6 Replies Latest reply on Aug 9, 2012 9:06 PM by sporobolus

    "Deactivating" records

    zolonman

      I am maintaining a database.

       

      Generally speaking, it is medical database tracking treatments for patients. There are current patients, and well.... non-current patients. Patients generally only get treated once but records are kept, but occasional come back so they can't just be archived and forgotten.

       

       

      My question is how in a database design sense, to "archive" a record into "non-current". I simple check box or calculation field seem to fragile. I would need to filter them out for most actions like bulk emailing and for any related portal etc. Seems like it could go wrong easy.

       

       

      I simple copy of the patients table seems like poor database design to me. Having to keep all the fields the same into the future, and manage duplication between them.

       

       

       

      In a general sense, is there a way to tackle this time of data management?

        • 1. Re: "Deactivating" records
          Malcolm

          how in a database design sense, to "archive" a record into "non-current". I simple check box or calculation field seem to fragile. I would need to filter them out for most actions like bulk emailing and for any related portal etc. Seems like it could go wrong easy.

           

          A checkbox is the starting point but you will also need to use privilege sets.

           

          You need:

          1. a number field, ie, "active", and have it be auto-enter the value 1. These are active records.

          2. Account Privilege sets. For most accounts you set the view access to the records in which "active = 1" . For at least one account you allow the user to see all the records so that they can edit the archived records.

           

          When you want to archive the record you set the field to empty. The user privilege set will then swing into action and they will not see the record anymore. you probably want to control data entry into this field using a script so that the user has some warning that the record will disappear.

           

          Privilege sets are very powerful. You can do many things with them. 

           

          Malcolm

          • 2. Re: "Deactivating" records
            Malcolm

            Of course, you should have a way to allow these records to be found as required. In ordinary use the privilege sets prevents the archived records from being found. You could create a "Search Archived Records" script which  runs under full access privileges so that it can "see" the entire record set.  You would then allow the user to make the records "active" and they will be visible to ordinary actions.

            • 3. Re: "Deactivating" records
              richardsrussell

              I use a variant of Malcolm's technique. I have a date field called "Obsolete", and a button to its left that says "Set" when the field is empty and "Clear" when it's not. As you would expect, "Set" inserts the current date, and "Clear" clears it out. On the right side of the "Obsolete" field is, just as Malcolm suggested, an "Active" checkbox, only in my case it's a calculation field that shows "1" (a checked box) if "Obsolete" is empty. It's the "Active" field that's used as the basis for searches and portal displays for current customers.

              1 of 1 people found this helpful
              • 4. Re: "Deactivating" records
                zolonman

                That is really clever.

                 

                This database is basiclly user priviledge free. So I am thinking of making a toggle switch. Have a show all and hide records, simple and doest require reworking of scripts and portals.

                 

                Thanks

                • 5. Re: "Deactivating" records
                  sporobolus

                  on 2012-08-09 17:56 zolonman wrote

                  This database is basiclly user priviledge free. So I am thinking of making a toggle switch. Have a show all and hide records, simple and doest require reworking of scripts and portals.

                   

                  you can use one privilege set for all users to do this, where in Data Access >

                  Records> Custom Privileges for each table you set the View privilege for the

                  patient table to Limited ... and enter a calculation like this for "Records can

                  be viewed when:"

                   

                  patient::is_active or patient::show_inactive_toggle
                  

                   

                  the second field is a global numeric which you'd set to 1 to allow access to

                  all records; set it to 0 and every operation will work only with active patients

                   

                  (be sure to retain one account with Full Access in case you mess something up)

                  • 6. Re: "Deactivating" records
                    sporobolus

                    scratch that — it turns off access to all the field values but still shows the records, so it won't serve the purpose