3 Replies Latest reply on Sep 27, 2010 11:44 AM by philmodjunk

    should i duplicate records



      should i duplicate records


      Hi everyone,

      I was wondering what the best solution for my scenario  would be, on a DB with 3 tables one for Patients, second is medical visit and third is diagnosis

      relation is as follows:

      Patients          MedicalVisit          Diagnosis

      PT_ID              PT_ID

                             MV_ID                  MV_ID

      There can be many visits per patient and many diagnosis per visit. everything is working just fine only that I discovered some patients have chronic diagnosis and swing by for a visit several times per month, in order to try and save time i have been asked to have those diagnosis that are chronic copied to every visit.

      My idea was to add a field to the diagnosis table named chronic and have it displayed as a checkbox then create a script that finds these duplicates them and adds the current visit ID.

      Not sure where to start or if thats even the best solution, any input would be appreciated.


        • 1. Re: should i duplicate records

          I think that's likely the best approach here. I toyed with the idea of constructing a relationship that would link such "chronic" diagnosis records to every visit, but since such diagnoses could change over time and I believe you wouldn't want such changes to modify past visit records, I ruled out that approach.

          You might consider a "chronic diagnosis" table that lists the Diagnosis ID and PT_ID of every such Diagnosis. This "join" table could be regularly updated as a patient's condition (or the doctor's understanding of it) changes. Each new visit script could check this table for diagnosis records that should be replicated in the Diagnosis table and linked via MV_ID to the most recent MedicalVisit record.

          • 2. Re: should i duplicate records

            ok, ive been going at it for a bit and seem to be in a brain lock here,

            I created a new table named it ChronicDiagnosis with fields PT_ID, Chronic_ID, and Diagnosis.

            I figure I can add a Button on the layout to open a new small window to a layout pertaining to this table and have the doctor create a list and then adjust the script for the new medical visit to copy these diagnosis and add them to the current visit upon creation of the visit, when the doctor get to the part of the layout with the portal to add any new diagnosis he can simply delete those diagnosis that dont pertain to the visit and at any time go back and adjust the list.

            How would i copy the diagnosis over from that table to the current "Diagnosis" table?

            Or did I misunderstand what you were suggesting?

            • 3. Re: should i duplicate records

              I deliberately went light on the details until I got a response from you to make sure this idea made sense to you.

              You'd have to loop through these records and for each such record, either perform a find on the diagnosis table or use Go To Related Records to find the diagnosis record, then use duplicate record to make a copy of the record and update the MV_ID field with the ID of the patient's latest visit. Final step would be to update the record in the chronic diagnosis table with the ID of the newly created duplicate entry so that if the doctor updates any of the data in the diagnosis record, the updated data will appear for the patient's future visits.