Help with Filemaker 9 database design

I’m not sure if I fully understand what you are wanting to do, but it sounds like you want the user to choose an option from a pop-up menu field, and then have FileMaker auto-fill other text fields with appropriate values associated with that choice.

To achieve this you could simply use a set of Calculation fields that looks at the pop-up menu data and returns the appropriate value, but this is cumbersome.

A much better way is to make use of FileMaker’s relational abilities and use a separate table containing the choices and associated values, and a relationship link between the two tables.

Firstly you need to create a new table with the appropriate text fields.

Choices table fields
Text Fields: ChoiceOptions, Text1, Text2, Text3, etc. ​

In this table you need to create records for each possible menu choice value and its associated text data values. For example, you might have records with data:

Animated Shows, Yogi Bear, The Smurfs, Spongebob Squarepants
TV Shows, Stargate SG-1, Chuck, Lost
Movies, Ghostbusters, Harry Potter, Willy Wonka​

Back in the main table you will need to define a Value List for the pop-up menu options. You could manually type in the values, but a better method will be to let FileMaker retrieve the values already entered into the Choices table.

MenuChoiceList value list
Use values from a field Choices table ChoiceOptions
All values​

Then you can create the field that will be used as the pop-up menu.

Main table fields
Text Field: MenuChoice ​

Put this on a layout formatted as a pop-up menu using the MenuChoiceList value list

Next you need to define a Relationship to link the two tables by matching the data in the main table’s MenuChoice field with data in the Choices table’s ChoiceOptions field.

ChoicesLink relationship
Match records in Main table with records in Choices table
when MenuChoice = Choices table ChoiceOptions​

Now you can create the additional fields in the main table which will copy the associated data from the Choices table. These will need to be Calculation fields rather than Text so that they will also change if the user changes their pop-up menu choice.

Main table fields
Calculation fields
Data1 = ChoicesLink::Text1
Data2 = ChoicesLink::Text2
Data3 = ChoicesLink::Text3
etc.​

Put these new fields onto the layout and you’re all done.


Now when the user picks a option from the pop-up menu MenuChoice field, the associated data for that option will be copied into the fields Data1, Data2, Data3, etc. Each record in the Main table will have it’s own set of Data field values associated with it’s MenuChoice field value.

Using the above example record data, if the user chooses “TV Shows” from the pop-up menu, Data1 will be set to “Stargate SG-1”, Data2 will be set to “Chuck”, and Data3 will be set to “Lost”.

If the user then changes the pop-up menu choice to “Movies”, the three Data fields will change to the new associated values of “Ghostbusters”, “Harry Potter”, and “Willy Wonka”.


You can add new records to the Choices table at any time and the new options will automatically appear in the Main table’s MenuChoice field pop-up menu.

You also can change the data in any of the Choices table’s Text fields and the corresponding Main table’s Data fields will automatically change ... but the same is not true of the Choices table’s ChoiceOptions field – if you change the data here, you’ll have to re-choose the value in each record of the Main table’s MenuChoice field that used the old value.


Two good places to get FileMaker help are

• FMForums.com message board (although I find this to be a bit slow and graphic heavy via my dial-up connection)

• comp.databases.filemaker Usenet Newsgroup, which can be accessed via this Google Groups link or via a proper newsreader application and a Usenet newsgroup server