HOME      HELP      CONTACT

It is common to have a “foreign key” in your table that points to a unique “primary key” in another table. For example, suppose your “Contacts” table contains a “State” field. Let’s also suppose that you have a “States” table where the primary key is the “Abbreviation” field. You might create a relationship in FileMaker® Pro to indicate this.

In this case, it may be easy to remember the state abbreviation and enter it directly into you contact record but there are times when the key is arbitrary; it might be a unique integer that serves as the ID for the record. Rather than enter the ID into your foreign key field, it would be more convenient to select from a list of records and have the ID automatically entered. In our example, let’s say that we want to select from a list of state names and have the abbreviation (key) entered for us. Follow these steps to accomplish this.

Step One

Create a layout in FileMaker® Pro for the States table (if it’s not already there) that contains, at a minimum, the primary key field (Abbreviation) and a descriptive field (Name) to be displayed in the “pick list”.

Create a TouchMyData request for the States layout. Be sure to identify the primary key. (Any request that has a primary key may serve as a “pick list”. If the request is used mainly as a  pick list, you may want to put it into the second or third segment to keep it out of the way of your main requests.)

Set the primary key here.

Step Two

Create a layout in FileMaker® Pro for the Contacts table (if it’s not already there) that contains, at a minimum, the foreign key field (State) and a descriptive field from the related table States::Name).

Create a TouchMyData request for the States layout. Touch Customize Fields and SELECT THE RELATED FIELD (States::Name). Since this is a related field, TouchMyData will present you with an opportunity to select a “pick list” request. For our example, we choose the “States” request that we created above. We must also identify the foreign key field in our main table (State) that will be updated when the user picks from the list.

Select the “pick list” request that you previously set up. (Remember, the request must identify the primary key of the related table or it won’t be available choose.)

Select the foreign key field. This is the field on the current layout that is connected to the primary key of the related table on the FileMaker® relationships diagram. This field will be updated when the user makes a selection from the pick list.

Also, you’ll probably want to override the label so that it’s friendlier than “States::Abbreviation”. Scroll up to the Edit Form section and enter “State” in the Label field.

Step Three (optional)

Since your foreign key field will now be updated automatically, you may want to hide it. To do that, back up to the list of fields, select the foreign key field (State), and switch Enable Field to OFF.

Hide the field with this switch.

There is no Step Four

That’s it! Now when you create or edit a Contacts record, you will be able to select from a list of State names and and have the State field (foreign key) updated correctly. The search bar on the “pick list” works too so you can narrow down the list if you have many records to choose from.