Orphans in Centralized data.mdb
An orphan in Centralized_data.mdb is a record with RecordStatus field = incomplete that was not sent with the latest batch from its location.
Orphans can be found by Reconnect_check_orphans query in Centralized_data_front_end.accdb.
Orphans can happen if a collector enters a patient, sends once while the patient is incomplete, and then deletes the patient or assigns a new location (since that is used to generate D_ID). Assigning a new location used to be very common, but no longer is after PatientFollow Project.
There should be no incomplete or complete records in CCMDB.accdb on a laptop that are not included in the most recent send into centralized database on Regional server.
All incomplete records for a given location, the sent date/time should be the same, and it should be the latest date/time sent to the Centralized data.mdb for that location.
- RecordStatus field is incomplete
- the record doesn't have the same SentDtTm field as the newest SentDtTm for that location.
In the very rare circumstance that something goes wrong during the later stages of sending, a record may not get changed from RecordStatus "complete" to "sent" at the end of sending. Such a record would not show up as an orphan because it its sent date is the same as the max send date. Usually this record would just be sent again next time (since collectors would not delete an unsent record), and if that send goes through OK it will be set to "sent" then and all is good.
However, if (1) the collector changes or deletes the record between the two sends, or (2) we messed with Service/Location names between the sends, then the originally sent record would not be updated further in the next send. This record would show up as a "complete" orphan.
Fixing these would always need to be on a case-by-case basis.
What to do with orphans
location used accidentally by another laptop
If another laptop (eg hsc a4) accidentally sends a patient with a different location (eg hsc h4) then the latest sentDtTm fir hsc h4 becomes that of hsc a4. If a4 sent after h4, then all of h4's most recently sent incomplete records will appear as orphans, because a later sentDtTm than theirs is found. So: if you find a lot of orphans for a site (eg >10, maybe fewer on low volume unit) then likely nothing is wrong with the orphans, but something is wrong with a different record. Fix:
- check the max_sentdttm listed for the group of false-positive orphans
- sort L_Log by SentDtTm field
- find the max sendDtTM; chances are it will refer to only one laptop's sent records
- review the location fields; one will be the same as the batch of false-positive orphans
- change the location to fix the issue in the short run. If the patient is incomplete also notify the collector to change the record for the next send, or the problem will happen again.
If there is a second record for the same patient (see eg names, first Service tmp entry ) with the same Pat_ID and different Service/Location, then the location was likely changed. Orphan can be deleted since the new record will take its place.
If there is a second record for the same patient (see eg names, first Service tmp entry) with the same Service/Location and different Pat_ID, then the Pat_ID was likely changed. Orphan can be deleted since the new record will take its place.
If there is not a second record for the same patient the patient was likely deleted. This might be correct and intended, or accidental. The data processor should follow up with the data collector to find out what happened. If the record was correctly deleted, delete the orphan. If the record should not have been deleted the processor could send the info in the record to the collector to facilitate re-entry on the laptop.
Partial data was resent
If the main office tried Re-sending data for partial dataset (e.g. only one patient) then that patient's sent_dttm will be the latest for that site. That means all records not re-sent will show up as orphans. This will be fixed automatically when the collector next sends.
None of the above
If none of the above seem to be the case, read the #Orphan definition closely. This is not intuitive, but it will usually help figure out what is going on.
how to properly delete an orphan in CFE
If there is an orphan in the PL orphan check, then
- in CFE, open Patient List
- find the orphan record
- click delete button on the very right
(this will delete from both L_Log table and all linked, and from L_PHI table)