I have a question about setting a foreign key via a Drop-down control.

I’m new to FileMaker (using FMP 9 Advanced), though have considerable experience building apps with PHP and MySQL.

I am working on an application for tracking computers and other assets in schools. The hierarchical tables are: States, School Districts, Schools, Departments, Rooms, Computers.

The tables have one-to-many relationships. Each table has an explicit key field called Record_ID (Number, Auto-enter Serial) which is related to a foreign key field (Number).

In the layout where the user will enter a new child record, I want to supply a Drop-down menu for selecting the proper parent record. Also, the parent table will populate this Drop-down via a lookup list.

Consider the two highest tables: States and School Districts (relationship: one state has many districts). When adding a new District, the layout offers a Drop-down for selecting the State the District belongs to. The Drop-down value list is set on the State_ID (foreign key) field of the Districts table; it connects to two fields in the States table, the Record_ID and the State_Name. This records the value of the state Record_ID while letting the user view the friendly State_Name.

Unfortunately, after the user has made her selection – for example, Arizona – she will see only the Record_ID value, 4, in the field. The user cannot visually confirm that she has made the right choice. The state names are visible while clicking on the Drop-down…

Does FileMaker have a way (that I haven’t discovered) to make those names persist for the user?

Thanks!

Use a Pop Up Menu instead of a Drop Down List.

Whowa! This works great! Thank you!

But it leads to another question.

I implemented the Pop Up, then noticed the following. When the data is examined by View / Table, the field State_ID appears to be populated with the state name, whereas I wanted to store the unique ID integer. BUT…apparently the information that is visible in the table (state name) is not the information that is stored by FileMaker. When I exported this table and examined it in Excel, the correct ID integers were present.

Does this sound right? It surprised me. I had assumed that FileMaker’s table view revealed the actual data that is stored in the database…apparently not so.

Does FileMaker offer any means of viewing the contents of a table which insures the developer that she is seeing the actual, primitive data that has been entered and stored?

Does FileMaker offer any means of viewing the contents of a table which insures the developer that she is seeing the actual, primitive data that has been entered and stored?

Yes. Don't use Pop Up Menus in your developer layouts.

It sounds like you need a developer layout, not a user layout viewed in Table View, but a dedicated layout for you to use. I always have one for every table, and I add and remove fields and portals to that layout as needed.

Edited March 18, 200817 yr by Guest

...got it.

Thanks!