14 Replies Latest reply on Feb 16, 2017 2:12 PM by JackRodges

    Carrying a field value across tables

    bryanade

      I'm creating a research database for a healthcare setting. We identify patients by their ID numbers. These patients have measurements taken before surgery and then several times after surgery. We also record things about their surgery.

       

      Currently, I have a main table, which contains the patient's ID number, their year of birth and a few other things. Since they have measurements done several times, measurements are stored in a separate table (called Measurements), and their surgical things are stored in a third table called Surgery.

       

      When a patient's data is entered, the first piece of entered data is the ID number, followed by the rest of the fields in the main table. There are two things I would like to do:

       

      1) When Measurement data is entered, I would like the patient ID number to be automatically entered into the Measurement table to avoid having to re-type it. This is the primary linkage field. I have already established a one-to-many relationship between my main table and the Measurement table using the ID number as the linking field.

       

      2) When surgical information is entered, I would like the patient ID number to be automatically entered, but I would also like a different layout depending on the operation that was performed (since the information items are slightly different).

       

      I'm quite new at this, so apologies if these are really simple questions.

        • 1. Re: Carrying a field value across tables
          siplus

          Have a Add Measurement and a Add Surgery button on the patient layout.

           

          When hit, they show a popover full of globals that the user can fill in (some of them can be calculated before showing the popover).

           

          The popover has 2 buttons at the bottom, cancel and OK.

           

          If Cancel is hit, close popover and exit script

          If ok is hit, store PatID in $patID, close popover, freeze window, go to correct layout, create new record, set fk_PatID to $patID, fill in the fields, commit, come back to original layout.

           

          Refresh window flush if in fm 13 or refresh portal if in 15, in case you have a measurements portal and a surgeries portal on the patient layout, as you should have.

          1 of 1 people found this helpful
          • 2. Re: Carrying a field value across tables
            keywords

            Another approach could be to have a portal on the Patient layout for Measurement and another for Surgery. Provided the relationships these portals use are set to allow creation of related records, there will be a blank row in the portal into which you can enter data. The process of entering that data will automatically pass the patientID to the related record.

            • 3. Re: Carrying a field value across tables
              JackRodges

              There is one requirement to my suggestion: you don't switch patients and if you do, you start fresh.

               

              The Patient ID can be stored in a global variable such as $$PatientID. The text isn't important but the $$ is as this makes it a global.

               

              As a global variable it is available in every script (which is why you must not change it unless you are finished)..

               

              Now anytime you work with a record, you can set a field with this value:

               

              set field (whatever; $$PatientID )

               

              When finished you set variable($$PatientID;"") which removes it from memory.

               

              Be careful and try to not use the wrong $$PatientID...

              • 4. Re: Carrying a field value across tables
                philmodjunk

                It would be best not to use a patient ID that the user enters manually as your field for linking records. Better to use an internally generated ID that is auto-entered into each new patient record. If you must use patient ID from outside of FileMaker, use it as another label field in the patient table that you use for searches and sorts, but use the auto-entered ID to link your tables in relationships.

                • 5. Re: Carrying a field value across tables
                  JackRodges

                  Patient ID is sometimes called Get(UUID) 

                  • 6. Re: Carrying a field value across tables
                    philmodjunk

                    Yes but it should be generated by FileMaker, not an outside source.

                    • 7. Re: Carrying a field value across tables
                      keywords

                      gofmp wrote:

                       

                      Patient ID is sometimes called Get(UUID)

                      PatientID is a field name. Get ( UUID ) is a FileMaker function. You might use this function as an auto enter calculation—as recommended above by Phil—but it would be very unwise to name a field thus. If that is what you are suggesting it is bad advice.

                      • 8. Re: Carrying a field value across tables
                        JackRodges

                        Yes, my comment intended Patient ID or whatever the field is named to be an auto enter field with Get(UUID) as the calculation. So much for expecting everyone to be a mind-reader... 

                        • 9. Re: Carrying a field value across tables
                          philmodjunk

                          You also posted it as a reply to me, which makes it appear to be a comment on my post rather than a teply to the entire discussion.

                          • 10. Re: Carrying a field value across tables
                            bryanade

                            Can I ask why? In this situation, the PatientID is taken from the practice's electronic medical record. It is a unique 8 digit number assigned to the patient, with no recycling. The data-entry requires that the ID be entered twice to create a record and that the number, on record creation, does not exist already as a PatientID.

                             

                            I could see using the internally generated ID as a means of future-proofing the database in the event that the electronic medical record changes (thereby rendering the 8-digit number possibly non-unique); but I would appreciate any advice on this.

                            • 11. Re: Carrying a field value across tables
                              philmodjunk

                              You've answered your own question. Relying on a value external to the system means that you are relying on the other system to function correctly both now and in the future.

                              And even when requiring the user to enter the ID twice, there is at least some risk that it could be entered incorrectly.

                              The odds that either scenario may be very low, but why take any chance with a primary key when you don't have to?

                              1 of 1 people found this helpful
                              • 12. Re: Carrying a field value across tables
                                JackRodges

                                There's an option in the field editor for making the value unique, in other words no one can use that for a new record when manually entering it. However, this does not prevent duplicating the value when importing. You can create a Self-Join To and use that to warn you if it is duplicated using Count(field) or something similar.

                                • 13. Re: Carrying a field value across tables
                                  philmodjunk

                                  And the value can be unique, but wrong. That can set up a case where you have to correct the error in the future--with headaches resulting from the need to track down all the related records to update their foreign keys with the corrected value.

                                   

                                  It could even create a problem here a patient whose correct ID happens to be the same as the previously entered incorrect ID, and then you'll have an immediate problem where you can't log the new patient's data because the system keeps rejecting their correct, valid ID because it's the same as that previously entered incorrect ID....

                                  • 14. Re: Carrying a field value across tables
                                    JackRodges

                                    There are many ways to create problems in a database and I have personally investigated many of them over 30 years and I would be glad to add anecdotes for you to use in the future... 

                                     

                                    In the early days networks and software were so buggy as well as the developer that I added tons of fields and data to each record just to protect against that related file self-destructing or my clients deleting all the records in a file and so on. I became quite proficient in rebuilding and resurrecting data.