2 Replies Latest reply on May 31, 2017 3:03 AM by keywords

    How copy and paste a set of Found records into a new table?

    hareeshred

      A few lines about me and Filemaker: I am a doc and a unusual one (as my colleagues would describe me) with an interest in Relational Database. I learnt the basics from the help section of Lotus Approach Database software and one fine moment got to know about the Filemaker and since then there is no looking back.

       

      The Problem:

      I am building database for my wife (Diabetes Specialist) The general Structure is Like:

       

      Patient Table: it contains the details of the patients like ID, Address etc

      Visit Table: (linked from Patient table by one to many- as each patient has multiple visits). Visit Table has details of the visits like weight, Glucose values for that particular visits and also the treatment given for that visit.

      Treatment Table: (Linked to visit Table ) contains a drug and its dose and how to take it.

       

      The scenario is: 

      since these are diabetic patients most of the treatment will be same for each visit except some minor changes. So What I want to do is: If a patient comes for the second visit If I click a button all the treatmentof the previous visit of that particular patient should be created new under the 2nd visit ( create new records in Treatment Table but linked with  2nd visit)

       

      Can you kindly guide me how to do it.

      My attempts so far were

      1. Find the records of the first visit and duplicate all the found records but the problem is how to link to the second visit.

      Your help is highly appreciated.

      Thanks and Regards

        • 1. Re: How copy and paste a set of Found records into a new table?
          Jason Wood

          I'll assume you can get to the point where the new VISIT has been created and you have the ID in a variable ( $id_newVisit ), and you have a found set of TREATMENTS from the previous Visit. Further, I assume that you have error checked to ensure there are in fact 1 or more records showing.

           

          This script will duplicate the records in the found set and link the newly created records to the current Visit:

           

          Go to record, first

          Loop

            Duplicate record

            Set Field id_Visit to $id_newVisit

            Omit record

            Go to record, first

            Omit record

            Exit loop if [ Get ( FoundCount ) = 0 ]

          End Loop

          • 2. Re: How copy and paste a set of Found records into a new table?
            keywords

            It seems to me you need at least one additional table. To my way of thinking the Treatment Table contains records for the various drugs or whatever that the practice has available to prescribe, including recommended standard dosages. However you would not link a patient visit directly to these but rather deploy a prescription of one or more treatments at each visit. So a Prescription Table would act as a join table between visit and treatment, enabling you to vary from the standard at any given visit/prescription without altering the standard stored in the Treatment Table.

            To prepare a new Visit record based on the previous visit would simply be a matter of going to the existing Visit record, grabbing all the Prescription IDs from the Prescription records as variables, creating a duplicate visit record, grabbing the new visit ID as a variable, finding the Prescription records from the set of variables, duplicating each and applying the new Visit ID to the duplicates.