A value list need not draw values from the same table as the field formatted to use it. A "use values from field" value list should be able to list values from whatever table stores the chemical names of your possible ingredients.
If I understand, I think you are saying that in my recipe ingredients table portal I can use a value list to show the chemical names from the chemicals list table. I think this might be a different problem than the one I am trying to solve. Unless I am missing something.
Recipe ingredients table has these fields: ingredient code, amount of ingredient, lot number, etc.
Chemicals list table has these fields: Ingredient code, name of ingredient, supplier, etc.
The recipe ingredients tables is displayed through a portal, to allow users to enter their recipe. I would like them to be able to use a drop down list of chemical names.
My problem is that the recipe ingredients table does not store the chemical names, just the chemical codes, which don't mean anything to the user. Does this make sense? Sorry if this is unclear.
From what I see, what I am suggesting is exactly what you need for what you are asking.
Open Manage | Value Lists and create a new "use values from field" value list.
For field 1 of this value list, select the Ingredient code of your chemicals list table. For field 2, specify the ingredient name of the same table.
Now format your ingredient code field in your portal row (use field from recipe ingredients table for this value list), with the above value list and you can select an ingredient by name, but the value list enters the ingredient code.
What I am describing is the "beginner level" approach. It's a good starting point, but is best suited for relatively small value lists. There are a number of approaches that can be set up once you have the "beginner version" working, that are more user friendly.
Conditional value lists can shorten the value list by having the user select a category and then the value list of ingredients limits itself to only those ingredients that are a member of that category.
And there are ways to set up a name based value list that "auto completes" (type in a few letters and the list filters down to just value starting with those letters), but which still links records by ID numbers.
And there are other, more sophisticated options than that you can set up.
You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.
Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)
Thanks a lot for your help. I am having some problems with the SQL data source so sometimes it is hard to tell why something doesn't work. I went back and reestablished my tables and I got this working.
My next question is is it possible to display only the chemical names this way? My field is chemical ID, and I have a drop down list that allows you to select from the chemical names. When you select a chemical name it enters the appropriate chemical ID # in the field, but the chemical name is not displayed anywhere. I would like the user to not see the chemical ID # at all.
Thanks again for your help!
I suggest that you download the Adventure files that I recommended.
If you use the pop up menu format instead of drop down list, the pop up menu format will still show the "visible field" specified for the value list even when you exit the field.
You can also add the name field from your Ingredients table as a separate field if you have defined a relationship linking the Ingredient ID fields of the two tables. When you select an ID in the drop down list field, this field updates to show the name.
And if you refer to some of the "side topic" examples in the two files that I recommended, you'll see some discussion of ways to hide the drop down list field back behind the "name" field from the related table, but also ways you can use to make this list an auto-enter value list based on the name.