1 Reply Latest reply on Sep 10, 2009 8:54 PM by davidhead

    Three Key Fields, Drop-Down Pick List



      Three Key Fields, Drop-Down Pick List


      I have a table with three values which together make up a unique key - first name, last name, and course code.  The same person might have multiple entries so I can't pick a unique record with just first name and last name; it takes all three fields.


      I would like to set up a drop down pick list for people to pick the record they want to edit and then drop them in the first field of that record.


      I tried setting up a calculated field that concatenates the three fields with a comma between the first and last name and a : after the name so that the calculated field looks like this "lastname, firstname: coursecode".  That works and I can use a drop down list to pick the name I want, but I can't figure out the next step, which is to take that calculated field and somehow find the right record and drop the user into browse mode in the first field in that record.


      Any suggestions would be appreciated.  Thanks.



        • 1. Re: Three Key Fields, Drop-Down Pick List

          You need to set up a self relationship. Let's say your table is called ENROL and the calculated field you set up is called NameKey. You also need to set up a global text field that we will call NameKeySelect - this will be the field on the layout with the drop down pick list.


          You need two table occurrences of the ENROL table on your relationship graph - let's call them ENROL and ENROL_byNameKey. Create a relationship thus:


          ENROL::NameKeySelect = ENROL_byNameKey::NameKey


          So the user selects a name using the global field pick list and then the script that runs will be a simple Go to Related Record using the relationship above and going to whatever layout you choose. The script can either be triggered by clicking a button or OnObjectModify for the field (if you are using FileMaker Pro 10).


          See how that works?