Missing Local ID

All ID are important to be filled in. Form ID is important for the DEF's Reconcilation tab and I recommend that to update that one as the last one.

Missing Local ID items

How it should NOT look like

Start with checking the items and the infoversions. If 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.

Accident

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 both that are missing the Local ID.

SELECT
accident.accident_localid AS Acc_ID,
accidentinfoversion.accident_localid
FROM
accident
Inner Join accidentinfoversion ON accident.accident_guid = accidentinfoversion.accident_guid
WHERE
(accident.accident_localid IS NULL  OR
accidentinfoversion.accident_localid IS NULL OR 
length(trim(accident.accident_localid)) = 0  OR
length(trim(accidentinfoversion.accident_localid)) = 0)  AND
accidentinfoversion.link_only =  '0'
ORDER BY
accident.accident_localid ASC

Hazard

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 both that are missing the Local ID.

SELECT
hazard.hazard_localid AS Haz_ID,
hazardinfoversion.hazard_localid
FROM
hazardinfoversion
Inner Join hazard ON hazardinfoversion.hazard_guid = hazard.hazard_guid
WHERE
hazardinfoversion.link_only =  '0' AND
(hazardinfoversion.hazard_localid IS NULL  OR
hazard.hazard_localid IS NULL OR
length(trim(hazardinfoversion.hazard_localid))= 0  OR
length(trim(hazard.hazard_localid)) = 0 )
ORDER BY
hazard.hazard_localid ASC

Hazard Reduction

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 both that are missing the Local ID.

SELECT
hazreduc.hazreduc_localid AS HR_ID,
hazreducinfoversion.hazreduc_localid
FROM
hazreducinfoversion
Inner Join hazreduc ON hazreducinfoversion.hazreduc_guid = hazreduc.hazreduc_guid
WHERE
(hazreducinfoversion.hazreduc_localid IS NULL  OR
hazreduc.hazreduc_localid IS NULL OR
length(trim(hazreducinfoversion.hazreduc_localid)) = 0  OR
length(trim(hazreduc.hazreduc_localid)) = 0 )  AND
hazreducinfoversion.link_only =  '0'
ORDER BY
hazreduc.hazreduc_localid ASC

Location

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 both that are missing the Local ID.

SELECT
location.location_localid AS Loc_ID,
locationinfoversion.location_localid
FROM
locationinfoversion
Inner Join location ON locationinfoversion.location_guid = location.location_guid
WHERE
(locationinfoversion.location_localid IS NULL  OR
location.location_localid IS NULL OR 
length(trim(locationinfoversion.location_localid)) = 0  OR
length(trim(location.location_localid)) = 0)  AND
locationinfoversion.link_only =  '0'
ORDER BY
location.location_localid ASC

MRE

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 both that are missing the Local ID.

SELECT
mre.mre_localid AS MRE_ID,
mreinfoversion.mre_localid
FROM
mreinfoversion
Inner Join mre ON mreinfoversion.mre_guid = mre.mre_guid
WHERE
(mreinfoversion.mre_localid IS NULL  OR
mre.mre_localid IS NULL OR 
length(trim(mreinfoversion.mre_localid)) = 0  OR
length(trim(mre.mre_localid)) = 0)  AND
mreinfoversion.link_only =  '0'
ORDER BY
mre.mre_localid ASC

QM

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 both that are missing the Local ID.

SELECT
qa.qa_localid AS QM_ID,
qainfoversion.qa_localid
FROM
qainfoversion
Inner Join qa ON qainfoversion.qa_guid = qa.qa_guid
WHERE
(qainfoversion.qa_localid IS NULL  OR
qa.qa_localid IS NULL OR
length(trim(qainfoversion.qa_localid)) = 0  OR
length(trim(qa.qa_localid))= 0)  AND
qainfoversion.link_only =  '0'
ORDER BY
qa.qa_localid ASC

Victim

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 both that are missing the Local ID.

SELECT
victim.victim_localid AS Vic_ID,
victiminfoversion.victim_localid
FROM
victiminfoversion
Inner Join victim ON victiminfoversion.victim_guid = victim.victim_guid
WHERE
(victiminfoversion.victim_localid IS NULL  OR
victim.victim_localid IS NULL OR
length(trim(victiminfoversion.victim_localid))= 0  OR
length(trim(victim.victim_localid))= 0)  AND
victiminfoversion.link_only =  '0'
ORDER BY
victim.victim_localid ASC

Missing Local ID fieldreport

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.

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

If you have any output here you need to take action. The queries below help you to identify for which item(s) the rows in the table fieldreport need to be updated.

Missing Local ID Auxiliary data

Example update query

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:missing local id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:missing local id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:missing local id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:missing local id

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}