3 Replies Latest reply on Aug 22, 2012 6:08 AM by philmodjunk

    How to narrow selection on one field based on selection of another.


      How to narrow selection on one field based on selection of another.


      I'm a complete rookie so please excuse this very basic question.

      I'm using FMP12.  I'm trying to create an invoiceing system.  I have the basic concept of using a line item table, an inventory table and an invoice table with unique serial numbers and relating them via primary and foriegn keys.

      I would like to have drop down and pop up menus when creating an invoice that narrows down your selections as you fill out the form.

      Ex: Field#1= Product (Violin,bow,rosin,etc)  

      Field#2=Brand limited to first selection (if you selected rosin in the 1st field, only the 10 brands of rosin would appear in the pop-up menu of the 2nd field to choose from)

      Field#3=Item Details (color, size, etc. based on available choices of the 2nd field.)

      After making these selections, they will result in a new line item on the invoice.

      Each selection is an attribute of an inventory item, so I would like to avoid creating new tables for different attributes.

      What would be the best way to approach this goal?

        • 1. Re: How to narrow selection on one field based on selection of another.

          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

          Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          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.

          • 2. Re: How to narrow selection on one field based on selection of another.

            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?

            Thanks again.

            • 3. Re: How to narrow selection on one field based on selection of another.

              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:

              1. Use Values from a Field
              2. Select fields from InventoryByCategory, Not Inventory
              3. Select InventoryByCategory::Item as the primary field
              4. Select InventoryByCategory::Description as the secondary field
              5. 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.