Combo box - related field

[alvb]

Hello,

Not too sure where to start. I have learned database mostly with MS Access. I am not sure where Filemaker stands compared to MS Access. Here is my current problem. In Access, when you have a field that is a key to a parent table, you can use a combo box with an invisible first column. The key to the parent is usually a number so meaningless to the user, so it is hidden. The second column presents the meaningful name of the option. The saved value is the value in the first column.

I am lost, cannot find how to handle that in the user interface in Filemaker. I am OK with the portal for the addition of multiple record in the child table, but how should I deal with the selection of an option based on a related table.

Ex:

Employee Table

EmployeeID

FirstName

LastName

FunctionID

Function Table:

FunctionID

FunctionName

In the data entry form for the employee table, I would use in Access a combobox with the FunctionID hidden in the first column, and the functionName as the visible meaningful name of the option. Whatever option the user selects, the value of the FunctionID is then saved in the field FunctionID of the table Employee.

Anyone could help me.

[dkemme]

The important thing to remember is that FunctionID is the important piece of data. As FuntionName in Function Table could change, the goal is to get the correct FunctionID in Employee table. To allow the user to select from available FunctionNames to get the correct FunctionID into Employee table, create a new field in Employee Table "FunctionNameLookup", text. Then create a relationship Employee's FunctionNameLookup to Function's FunctionName. Go to Define Fields and configure AutoEnter for FunctionID using relationship you just created, selecting FunctionID. This will place the FunctionID into Employee Table's whenever FunctionNameLookup changes, if a match is found. On a layout based upon Employee, place the FunctionNameLookup field, Format Field as popup with a Value List based upon Function's FunctionName. This will give your users a list of available Functions to choose from. Import to not let them enter their own Functions, as no FunctionID will then be entered. You may even wish to have Employee's FunctionNameLookup Define Field to Validate that all entries are a member of this list you just created.

But when it comes to using the Functions of this employee, I would create a second relationship, Employees' FunctionID to Function's FunctionID. Then if the FunctionName every changes, you still have a valid relationship.

Whew, hope this helps.

[sbg2]

In the data entry form for the employee table, I would use in Access a combobox with the FunctionID hidden in the first column, and the functionName as the visible meaningful name of the option. Whatever option the user selects, the value of the FunctionID is then saved in the field FunctionID of the table Employee.

Terms like Visible or Enabled are not high on the priority list for Filemaker. My suggestion would be to get used to seeing two columns in your "Combo Box", namely FunctionID & FunctionName. Users are actually selecting the FunctionID but also see the FunctionName which makes the list easier to navigate.

[alvb]

OK. I got it to work with a listmenu showing both the ID and the functionName. It works, although I would prefer the ID to be invisible. Anyway. I moved on. Now The FunctionID is also related to a FunctionGroup through FunctionGroupID (manager, salesperson, etc). When I select in the listmenu the functionID, the correct functionName is shown in the related field FunctionName, but the next level relationship FunctionGroupName is not automatically completed. However, when I close the layout and open it again, all the information FunctionID, FunctionName, FunctionGroupName does show.

Is there any way to show the value in the second join (FunctionGroupName) during data entry?

Table Employee

EmployeeID

FunctionID

Table Function

FunctionID

FunctionName

FunctionGroupID

table FunctionGroup

FunctionGroupID

FunctionGroupName

Michel

OS X, Filemaker Pro 7

[thijorge]

I have a very similar problem and would like to know how you got it to work using the listmenu.

In my case I have the tables BOOK and INVENTORY and they are linked throught the field BOOK_ID.

When I want to create a new record, I want to choose a book from the BOOK table displaying the Book name and then I want to add to the INVENTORY table the BOOK_ID.

So far I am having a terrible time with that. Seems simple and easy to do using any kind of database and looks like FileMaker is getting simple things complicated!!

Thanks