This is called a conditional value list. Chaining together several conditional value lists so that each select filters the values for the next is something I've called a "hierarchical conditional value list". Check out these links, especially the last one which discusses the peculiarities of a hierarchical conditional value list:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Thanks for posting those resources. They're very helpful. It works great until I add a primary to foriegn key relationship. Then the conditional value list shows "no values defined".
Here is a screencast of exactly what happens: http://screencast.com/t/Rs5cg1AX
What is a good way to accomplish narrowing down a huge list of items in your line item table when creating invoices?
The links provided show a good way to narrow down your list. But they require that the relationship and value list be correctly set up in order to work.
From your screencast, there's no relationship in place that can serve to filter your choice of items from the inventory table.
Say you organize your inventory records into two categories: Power Tools and Hand Tools.
You add a field to Inventory named InvType and put one of these two values in the field for every inventory item. Add a field to LineItems named Category.
In Manage | Database | relationships, make a new table occurrence of Inventory by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as inventoryByCategory.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
LineItems::Category = InventoryByCategory::InvType
Define a Custom value list for Category using these same two values we've entered into InvType. Define a conditional value list with these settings:
- Use Values from a Field
- Select fields from InventoryByCategory, Not Inventory
- Select InventoryByCategory::Item as the primary field
- Select InventoryByCategory::Description as the secondary field
- Select "Include only related values, starting from Line Items".
Now put Category and LineItem::Item on your layout, each formatted with their value lists.
When you select a category in LineITems::Category, the value list set up on Item will list only those items from inventory from that category.