5 Replies Latest reply on Apr 15, 2014 2:20 PM by NsaA

    Linking multiple fields to auto enter based on one field

    NsaA

      Title

      Linking multiple fields to auto enter based on one field

      Post

      I am designing a medical database. Every record in the database reflects a patient's visit to the clinic. Every patient included in my database has a unique Database ID. So patient with PatientID 1 may have 5 records in the database as he has made 5 visits to the clinic.

            
           Every time a patient comes in, some fields in the same table "TableA" have to be filled repeatedly, like
           1) DatabaseID
           2) DateofBirth
           3) Ethnicity
           4) Sex
           5) AttendingPhysician
            
           Please help me write a calculation for the following:
           Everytime a Database ID is filled in, the other three fields should fill automatically based on past entries for the same Database ID.
           The user should be able to over-ride, that is, should the Attending Physician change, the user should be able to change it for that particular visit in the database.
           Also, the fields of Sex, Ethnicity and Attending Physician are chosen from a drop down list. I hope that does not matter.

        • 1. Re: Linking multiple fields to auto enter based on one field
          philmodjunk

               Looks like you need two tables instead of the one that you describe. One table would record all the data specific to that patient such as the DOB, Ethnicity, gender and attending physician. A second table, linked to the first by PatientID, would record the details of each office visit.

               With that relationship, no such calculations would be needed. You'd add the fields from the first table to the layout set up for the table of office visits and no data need be copied into each new record created for each new office visit by that patient.

          • 2. Re: Linking multiple fields to auto enter based on one field
            NsaA

                 That does make sense. Unfortunately, I absolutely have to work with one table only.

                 (This is a previously created database and I'm modifying parts of it)

                 Is there any way to do this in a single table?

            • 3. Re: Linking multiple fields to auto enter based on one field
              MarcMcCall

                   My guess here is that you have some sort of script or something that creates a new visit for the patient.

                    

                   You could create a self join of visits to visits and sort the link in descending order and then do some auto enter calculations for your fields and the calculated value would come from the new self join table accurance.  This would populate your fields with the same data from the last visit.

              • 4. Re: Linking multiple fields to auto enter based on one field
                philmodjunk
                     

                          I absolutely have to work with one table only.

                     If find that "absolutely" very difficult to believe. If you are modifying parts of it, one modification that you can easily make is to add that needed second table and use scripts and/or import records to set it up with the correct data.

                • 5. Re: Linking multiple fields to auto enter based on one field
                  NsaA

                       PhilModJunk: My problem was that data has been already entered in this database and I was following a fixed set of instructions. also, I'm relatively new to FileMaker Pro. In any case, your solution was very neat and simple. I did exactly as you said and created a separate table eventually. Just finished with it, and it worked out beautifully! Thanks for your advice and help! You gave me great suggestions on a previous post too. This forum has never failed me :)

                       MarcMcCall: Thanks for your time Marc, but I did not need to try out your solution eventually.