NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
{{TOC right}}{{Note | All ID are important to be filled in. '''Field reportForm ID''is important for the DEF'<pre>SELECTfieldreports Reconcilation tab and I recommend that to update that one as the last one.fieldreport_localid,}}fieldreport.dateofreport AS `Date of Information`,fieldreport.reportreceiveddate AS `Data Entry Date`,==Missing Local ID ==__NOEDITSECTION__ fieldreportStart with checking the items and the infoversions.reportcompleteddate AS `Submitted Date`,fieldreportIf they are missing a local ID then I strongly recommend to set one which will be very helpful if you need to populate ''Form ID''.reportverifieddate AS `Approved Date`,fieldreport.dataenterer AS `Data enterer`,fieldreport.verifiedby AS `Approved by`FROMfieldreportWHERE=== Accident ===__NOEDITSECTION__ fieldreportIf you get output here you need to take action.fieldreport_localid From the result set you can figure out a solution depending on if it is null the item, the infoversion or length(trim(fieldreport.fieldreport_localid)) = 0</pre>'''Accidentboth'''that are missing the Local ID.
<pre>
SELECT
accident.accident_localid ASC
</pre>
=== Hazard ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''Hazardboth'''that are missing the Local ID.
<pre>
SELECT
hazard.hazard_localid ASC
</pre>
=== Hazard Reduction ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''Hazard Reductionboth'''that are missing the Local ID.
<pre>
SELECT
hazreduc.hazreduc_localid ASC
</pre>
=== Location ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''Locationboth'''that are missing the Local ID.
<pre>
SELECT
location.location_localid ASC
</pre>
=== MRE ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''MREboth'''that are missing the Local ID.
<pre>
SELECT
mre.mre_localid ASC
</pre>
=== QM ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''QMboth'''that are missing the Local ID.
<pre>
SELECT
qa.qa_localid ASC
</pre>
=== Victim ===__NOEDITSECTION__ If you get output here you need to take action. From the result set you can figure out a solution depending on if it is the item, the infoversion or '''Victimboth'''that are missing the Local ID.
<pre>
SELECT
</pre>
=== Example update query ===__NOEDITSECTION__
== fieldreport ==__NOEDITSECTION__
If you get output here you '''NEED''' to take action. The easiest solution to populate the ''Form ID'' for DEF is to use the local ID from the infoversion which you have now populated with updating the ones that were missing with the queries above.
<pre>
SELECT
fieldreport.fieldreport_localid,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`
FROM
fieldreport
WHERE
fieldreport.fieldreport_localid is null or length(trim(fieldreport.fieldreport_localid)) = 0
</pre>
{{NavBox Upgrade}}
[[Category:NoPublic]]
[[Category:SQL Queries]]