8 Replies Latest reply on Apr 25, 2012 5:49 PM by BettyMenzies

    Synchronising a data field

    BettyMenzies

      Title

      Synchronising a data field

      Post

      I have two tables with ID relationships established.

      How do I synchronise the 2nd table to synchronise with the ID on the first table.

      Is this possible?

        • 1. Re: Synchronising a data field
          Jade

          I'm not sure what you mean by "synchronise".  Would you mind clarifying this for me?

          The basic premise behind establishing the relationship between two serial increment ID's in different tables is that they don't have to be synchronised to the same number or series of numbers.

          • 2. Re: Synchronising a data field
            BettyMenzies

            Thank you for your response!

            The reason I'd like to be able to synchronise the IDs (which I can successfully do using Access databases) is because I'm creating a database for Medical purposes.  When the pre-op details are presented on the screen for Patient with ID "55" for example, it would be convenient to click on to the operative details of OPID "55" and follow-up deatils FUID "55" which have separate tables linked and for the details of the same patient to be presented immediately without having to scroll throegh a list. 

            Regards

            Betty

            • 3. Re: Synchronising a data field
              Jade

              I see.  

              How did you estabish the ID relationship?  

              In FMP, the primary key on the Patient table occurrence (normally an auto incremented field) is linked to the foreign key (a number field) on the OP table occurrence using the Relationship Graph.  This sets up a one-to-many or parent-child relationship between the two tables.  When you add a new record to the OP table by using a Portal on the pre-op details Layout for a Patient, FMP will automatically put the primary key of the selected patient (e.g. "55") into the foreign key field on the new OP record for that patient.  This is how FMP "synchronises" the key values.  Note that the OP table should have its own primary key, an OPID auto incremented field.

              Since the relationships are handled automatically in this way, you will eventually realize that you really don't need to use the key values to find related information.  However, they can be used if you are more comfortable with that approach.

              • 4. Re: Synchronising a data field
                BettyMenzies

                Thank you for your help Jade.

                I understand the process better now.  I haven't used FileMaker (only in the free trial) - old habits die hard.
                I feel comfortable with the program now, just need more practise before purchasing.

                Betty

                • 5. Re: Synchronising a data field
                  Jade

                  Yes, it's difficult switching horses in mid-stream.Wink  Access and FMP use completely different approaches to creating databases.

                  I should have mentioned that there are several other ways to instantiate the foreign key on the OP table when you add a new record to it.

                  • If you don't like portals, you can use a layout based on OP and add the patient foreign key field to this layout.  Use either a pop-up or drop-down menu based on a value list of patient IDs.  The value list can display the patient IDs (e.g. "55"), or the patient names, or both.

                  • You can add a button to the patient layout that is scripted to add a new OP record with the foreign key field set to the patient ID.

                  • If you have FMP advanced you can alter the New Record command or use a trigger to instantiate the foreign key field for OP records. 

                  • 6. Re: Synchronising a data field
                    BettyMenzies

                    Jade

                    Now that you've pointed me in the right direction I really like using portals!

                    I have ONE more question re using CALCULATIONS.
                    I know how to calculate the age (today) from Birth dates.  However, I would like to calculate the age of a patient from his date of diagnsis, which is a fixed date within a field.

                    When I swap 'CurrentDate' in the calculation for a field named 'DateFirstVisit' I receive the following error message "This parameter is an invalid Get function parameter"

                    ie

                    Year (Get (DateFirstVisit)) - Year (Birthdate) - If (Get (DateFirstVisit) < Date (Month (Birthdate);Day (Birthdate);Year (Get (DateFirstVisit)));1;0)

                    Is it possible to use a Get function parameter using a fixed date?  I've looked up the "Get Function" topic and cannot see a solution.

                     

                    Thank you

                    Betty

                    • 7. Re: Synchronising a data field
                      philmodjunk

                      Remove the get function and just use DateFirstVist. This assumes that DateFirstVisst is a field of type date.

                      • 8. Re: Synchronising a data field
                        BettyMenzies

                        Excellent!

                        Thank you so much.