5 Replies Latest reply on Jan 8, 2010 10:02 AM by philmodjunk

    Populate foreign key



      Populate foreign key


      I have a Clients database, which has tables such as "Clients", "Sessions", "Insurance Policy", "Payments" etc.


      Currently, when I create a new session record from the session layout I click the foreign key field and choose the clients name in order to relate the two records.  (typical drop down list)


      Problem is, if there are two clients with the same name it appears that only one of them is displayed (probably the first one created) 


      I noticed that if I sort by the first field in the "Specify fields for value list" than all my clients show up, because I am sorting by a unique value.  This however is not ideal, nor intuitive to the end user. 


      Ideas?  I thought about using some seperate find window to populate foreign key fields, however this might be beyond my skill set.


      OS 10.6.2

      FMP 10.0v3 

      I have been working with Filemaker for about a year.

        • 1. Re: Populate foreign key
             In the specify field window, have you tried checking the "show values only from second field" box?
          • 2. Re: Populate foreign key
               Yes, and that is when it shows only one record from the client table.  If there are two John Doe's in the client table only one shows up in the drop down list on the Session Layout.
            • 3. Re: Populate foreign key

              There must be a distinction between your two John Doe's...whether it be Company, Address, ... , something.


              What about:

              create a field in your client table that combines the name and the other distinguishing characteristic

              Calc = FirstName & " " & LastName & " " & Company & " " & Location (or other distinguishing fields)


              ...then use this field as your second field in the value list.


              In this way it would still sort by name but you'd be able to discern between your two John Doe's since field2 would be different between them.


              Would this work for you?

              • 4. Re: Populate foreign key
                Sorry, I misunderstood your original post. I like Ninja's idea - even if you have all of the "John Does" displayed in the dropdown, you will need additional information in order to select the correct client.

                If you'd rather try the separate window to populate the foreign key fields, here is the basic idea:

                Create a new layout "clientsearch" with whatever fields are appropriate (name, address, company, etc.) 

                create a script "client id search" with these steps:

                New Window []
                Go to Layout ["clientsearch"]

                create a script "add client id" with these steps:

                Set variable [$clientid; Value:client::clientid]
                Close Window [Current Window}
                Set Field [Session::clientid; $clientid]

                add a button to the session layout that performs the "client id search" script
                add a button to the clientsearch layout that performs the "add client id" script
                • 5. Re: Populate foreign key

                  Another option (takes a bit of extra work, but can be very nice for the user):


                  Add a global field and format it to list all your names only (no ID field) and enable the auto-complete feature so that the user can "prune" a long list of names down to just a few by entering the first few letters. Set up a script trigger to perform a script when the user exits the field.

                  1. Write the script so that it freezes the window, switches layouts to layout listing the same records and performs a find for all records with the selected name.
                  2. Use get (foundcount) to count the number of records that match the name. If there's just one, use set variable and set field to copy the ID number of this record into the record on your original layout.
                  3. If there's more than one found, pop up a list of the matching records in a small window where the user can see a little more info (such as some address fields) to distinguish between entries with the same name.
                  4. The user clicks the name they want from the list and the script then puts the selected record's ID number into your form and closes the pop-up window.