Value List help needed for loooong time Access developer

[ClayH]

Hi, I'm learning Filemaker for a new project. I've been an expert Access user for the past 10+ years, so the subtle differences leave me scratching my head.

Here's the issue... I have a table called Domain and a table called Category. Each have an * ID field, with the respective name, sequentially numbered, etc. The Category table has the Domain ID in it as a foreign key -- all is well and good. Each Category falls into a Domain.

In Access, I would store the foreign key (Domain ID) in the field, but display the text value of interest (Domain Name) via the lookup to the other table. The interface would know there were 2 columns and that the field was bound to the first column, but I would set that to 0" width so that it wouldn't display to the user. This is a common way to handle combo boxes on Forms in Access.

In Filemaker, I set up a Value list to those parameters, with two columns.. The first is Domain ID and the second is Domain Name. I asked the list to only display the Domain Name -- it does that.

Now, via the Category layout, if I choose to display the Domain ID as a pop-up list, it does exactly what I would expect -- shows me the Domain Names and obscures the Domain ID number. After I make a selection, it shows me the Domain Name. If I choose to display the field as a drop-down list, after I make a selections, it always shows the Domain ID number. If I ask it to display the data from the Domain Name field in the Domain table, it will not allow me to use the control to set values on Category records while on the Category layout.

Am I just thinking about this the wrong way? Is the pop-up list the Filemaker equivalent of the combo box in Access? If not, is there a way to add a field to a layout that would have a drop down list that shows the related field of interest (Domain Name), displays that in the field in form view, but actually is storing the Domain ID underneath (the way the pop up list handles it)?

Edited December 18, 2009 by Guest
fixed typos

[bcooney]

Welcome Clay.

Yes, popup menus are the way to have the user select an ID for a foreign key, but show them a text value (value list is created using two fields, show only second field).

Drop down lists always show the first value (ID) after selection. Ugh.

Some options come to mind:

1. Layer the fields. Bottom layer, drop down list of Domain IDs Names with allow entry in Browse mode on. Top layer, related field, "Domain Name", with allow entry in Browse mode off and white fill. Possible issue, user can type anything in a drop down list. You can limit what they can type by using a field validation option = to value list.

2. Switch to a popup window to select a domain.

3. Create new Categories from a Domain form. This script sets the foreign Domain ID in the category record. Do not allow a Category's Domain ID to be edited on a Category form.

[ClayH]

Thanks for the quick response and for the suggestions. I'm so used to Access that it's going to take me a while to figure out these little differences. B)