4 Replies Latest reply on Dec 29, 2011 5:18 PM by XavierOrtega

    Dropdown with values from current found set



      Dropdown with values from current found set


      I have a layout that uses a found set of records (Account Names that have a balance). I put a dropdown list in it that uses a value list based on the Parent table's field (Account Name). The dropdown displays all the Account Names not just the ones in the found set. How do I go about getting that suset list of names to appear in the dropdown? Thanks. (FMP 11 Advanced)

        • 1. Re: Dropdown with values from current found set

          There's no simple way to filter your value list by the current found set. If you can set up a relationship or stored, indexed calculation field based on your find criteria, then you can do this. Since your balance based criteria could well be based on a summary field or an unstored calculation that uses Sum() this could be hard to do here.

          Otherwise, you'd need to use a script to set up the list of values after performing the find.

          Assuming you have a field named AccountID that uniquely identifies each account.

          Duplicate the layout on which your perform your find, but then remove all fields except AccountID from this layout. Call the layout AccountIDs.
          Define a global field in same table as that used in the layout where you need your conditional value list.
          Add a relationship between the layout's table occurrence to your account table matching this global field to the AccountID field:

          LayoutTableOccurrence::gAccountIDList = SelectedAccounts::AccountID   (SelectedAccounts is a new table occurrence of your Accounts table.)

          Define your value list to list values from fields in SelectedAccounts, then select the "Include only related values starting from LayoutTableOccurrence" option.

          After performing your find these script steps can build a list of Account ID's that can be used in a relationship for your conditional value list:

          Freeze Window
          Go To Layout [AccountIDs]
          Copy All Records
          Go To Layout [Any layout that has the gAccountIDList field]
          Paste [LayoutTableOccurrence::gAccountIDList]
          Go To Layout [The layout where you have your conditional value list of accounts]

          If "table occurrence" is an unfamiliar term, you may want to read this thread:  Tutorial: What are Table Occurrences?

          If you have never worked with conditional value lists before, you may find these links helpful:

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          • 2. Re: Dropdown with values from current found set

            Thank you PhilModJunk!

            • 3. Re: Dropdown with values from current found set

              I have a similar situation.  I'm looking to create a value list from a found set of active customers.  

              I have created a layout with the full names of the customers being the only field.  This layout shows records from "Main", which is the same table as the script will be running and all fields (not sure if this is the problem).

              Here's my current script:

              Freeze Window
              Go to Layout [ “Email selected” (Main) ]
              Perform Find [ Specified Find Requests: Find Records; Criteria: Main::Active: “=Yes” ] 
              [ Restore ]
              Copy All Records/Requests
              Go to Layout [ original layout ]
              Paste [ Select, Main::Email_cust ]


              I created a value list to use the Email_cust field. When I test the Main::Email_cust (displayed as an edit box) it correctly displays the customer names.  When I display it as a checkbox, it displays lots of other data related to each record.

              • 4. Re: Dropdown with values from current found set

                I have a similar problem, and haven't been able to get it working correctly. I tried the solution provided by PhilModJunk, but still missing a few steps or so. Hopefully he (or someone else!) can help me out. I'm curreently using FMPro 11v3.

                I have two(2) tables in 2 separate files, converted from FMPro 6 to 11. The table in the first file is Shipment Doc Generator, and the table in the second file is Rolodex. In the Rolodex file/table, I have 3 layouts: Detail (shows company name & address info, with "Forwarder-Supplier" field as the unique ID), Detailed Found Set (displays only selected values), and Freight Carrier ID (duplicate of Detailed Found Set, with all fields removed except "Fowarder-Supplier").

                I have a value list titled "Forwarder List" in the Shipment Doc Generator table. I want to use the values in the "Forwarder-Supplier" field from the "Freight Carrier ID" layout in the Rolodex table as the basis of the "Forwarder List" value list in the Shipment Doc Generator table.

                I created a global field titled "Freight Company List" in the Rolodex table, but not sure it should be placed there or the Shipment Doc Generator table??

                I'm not sure how the relationship mapping between the 2 tables in the separate files should work?

                And in what table should the script be placed?


                thanks in advance,