After the great ideas and tips I got from my previous post, "Archiving Records", I think I have an archiving solution. The next part is deleting the records from the main solution once they have been successfully archived. Here is my solution, but it seems inelegant.
Using Anchor->Buoy terms, I have a couple of Anchor table occurrences (TOs) to which most of my data tables are related.
The first anchor is TO_Patients. It has a field called is_deleted that is set to “F” upon record creation. A number of relationships hang off of it, mostly to patient demographic data, insurance data, etc.
The second, and far more “hairy” (in the sense of having lots of sub TOs hanging off of it), is TO_Anesthetics. These two Anchors can be related as they both contain ID_Pt.
Here’s what I plan to do to delete patients from the solution. I will create a new TO and layout based upon the Patients table and call it TO_Delete. I will then tie it to all of my PATIENT sub TOs via ID_Pt. I have uploaded a screenshot of the relationships graph (Anesthesia EMR relationship graph.png) that I munged up to try to show it (it is in RCC format for TO names, would love any comments pro/con about that).
I will then create a layout for each of the related subtable TOs below TO_patients. A lot of layouts (about 50!), but they will be for one purpose only: to do a Find for TO_Patients::is_deleted = “T”, which will be the only related field put on the layout.
It gets tricky with the ANESTHETIC subtables, because while TO_Patients is related to TO_Anesthetics via ID_Pt, the anesthetic subtables are only related to TO_Anesthetics via ID_Anesthetic. My thought is that TO_Anesthetics then becomes a JOIN table, right?
So I will create a bunch of relationship like this:
TO_Delete (the patients table) ->(ID_Pt) TO_Anesthetics ->(ID_Anesthetic) TO_anesthesia_subtable
My master delete record script will start with the layouts for the child tables at the farthest right, (as suggested by andypieman), do the find for the records whose parent PATIENT record has been marked “T” in the is_deleted field, and then delete them.
Once all of these have been deleted, I then work up to the Anesthetics TO, delete those records, and then finally to the Patients TO.
Phew! What do you think? Is there a simpler way? Remember, this solution is working on a non-connected iPad. No server stuff allowed.