1 Reply Latest reply on Feb 12, 2015 3:06 PM by philmodjunk

    Allowing creation of records whilst stopping duplication with linked tables



      Allowing creation of records whilst stopping duplication with linked tables



      Any help appreciated. New to File Maker pro so bare with me.

      Managed to make myself a client database.

      Have a table with client info then about 7 other tables for the other people who interact with the client, for example GP's or Physio's.

      On the client page I have all the client details and then the names of all the other professionals involved with them. I have it set up so that I can type, for example, the Physio's name in and it will automatically generate their record on the corresponding Physio page/table. Also I have added a button which will send you to the record.

      The issue. When one Physio see's multiple clients. I.E. If I create a record for a new client and they have the same Physio as another client when I type in the Physio's name in on the client page it will create a new record on the Physio table, resulting in a duplication. What I need it to do is link to the existing record.

      How do I do that?



        • 1. Re: Allowing creation of records whilst stopping duplication with linked tables

          There are two ways to set this up. One is to link a patient to multiple records in the same table. A single table might list all Physiotherapists, GP's, surgeons, etc or you might have a different table for each.

          I think you are using a different table for each and if so, you need to set up this relationship:

          Patient::_fkPhysioID = Physio::__pkPhysioID

          And then you should select a Physio from a list of existing Physios and click a button to add a new record for a new Physio when they are not found in the existing list.

          The simplest list method is to set up a value list as a drop down list or pop up menu. So I suggest setting up that approach at least at first to confirm that everything is working. Later, you can investigate the two explortation files for which I will include download links if you want to explore ways to make a long list of values easier to work with in FileMaker.

          The script to add a new Physio record might look like this:

          Go to Layout ["Physio" (Phsyio) ]
          New Record/Request
          Set Variable [$PhysioID ; Physio::__pkPhysioID ]
          Go to Layout [origional layout ]
          Set field [Patient::_fkPhsyioID ; $PhysioID ]

          WIth the new record added you can then enter a name directly into a name field from the Physio table, or you can modify the above script to either use a custom dialog with an input field to ask for the name or have the script pause on the Physio layout in order for the user to enter that name into a field on that layout.

          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking