6 Replies Latest reply on Oct 14, 2016 2:42 PM by cortical

    Archiving/Deleting Records - part 2


      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.

        • 1. Re: Archiving/Deleting Records - part 2

          Without fully analysing your whole proposition, just a comment about one aspect. You say: "It has a field called is_deleted that is set to “F” upon record creation", and elsewhere indicate that this field may contain a T, suggesting some tagging process not mentioned. Since you only mention these two values I assume that is_deleted is on fact a Boolean field—these are in effect on/off, yes/no, true/false values. Now F and T may contain some special meaning for you, but they are meaningless to me. I follow the practice of always using 1 and 0 for Boolean values (although note that you can still set their appearance to F and T or anything else, if you want to) as this makes them much easier to manage and calculate with—for example, if you have a constant field that contains a 1, you can use the Boolean field directly in a relationship.

          • 2. Re: Archiving/Deleting Records - part 2

            find and delete, all those layouts; slow


            IF you do decide to use go layout then delete; structure the script as

            enter find

            go layout



            rather than

            go layout

            enter find


            that way only the required are downloaded



            think about creating a one record utility table

            global field for patient_id

            create the delete TOG based on the utility table as 'head of the squid'

            simple set global = patient_id


            then either go a layout based on etc utility table, and delete the record;

            or an off layout body portal and go field object, allow delete

            • 3. Re: Archiving/Deleting Records - part 2

              Thanks - I had thought about that in the past and just went with what I had used previously.  But your point drove home the importance, especially about being able to have the constant in a relationship. But what about instead of 1 and 0, using 1 and "" (null)?  Is there any advantage or disadvantage to just having "" for False and 1 for True?

              • 4. Re: Archiving/Deleting Records - part 2

                Great tips!  Thanks.  I'm going to try out your second idea, but will remember the "enter find" first idea.  This is aimed at a local file on the iPad so the speed issue is not as critical, but I have no doubt that if it works, people will be putting it on Server, I want to build that in where I can.

                • 5. Re: Archiving/Deleting Records - part 2

                  In a number calculation FM will treat 0 and null (empty) as the same. So you can have records created with a null value and then explicitly insert a 1 when needed.

                  • 6. Re: Archiving/Deleting Records - part 2

                    "" is an implicit 0

                    better practice as a general rule, to be explicit wherever possible

                    like the expression 'nature abhors a vacuum' , nulls are potentially false negatives.

                    will a REL 0 = null return the same results as 0=0?

                    will a count nulls be the same as count zeros? / can you count nulls?


                    Number fields will be faster than text fields (t/f)