6 Replies Latest reply on Jan 9, 2017 11:45 AM by laguna92651

    Cascading Value List with Linked Data Sources

    laguna92651

      My Accounts layout has a portal that lists all products, Product_Lines table, that the Account is interested in.

      I have a second layout that lists all products that have been entered in all of my Accounts.

      The layout is based on the Product_Lines and the Account names are retrieved with a linked Account table.

      Product Search.png

      I am trying to set up cascading value list.

      The first value list is Products which is in the Product_Lines table, which is straight forward.

      The second cascading value list should show all Industries of the filtered product find, Industries is in the Accounts table.

       

      I can't figure out how to filter the Industries value list. There is no Industry data in the Product_Lines table and there is no Product data in the Accounts table. The Accounts and Product_Lines tables are essentially one blob of data linked with ID_Account.

       

      I can't link the T35zza_account_VALUE_LIST_INDUSTRIES TO to the T35_ACCOUNT_PRODUCT_LINES TO, to retrieve the filtered industries list.

      Product Search TO.png

        • 1. Re: Cascading Value List with Linked Data Sources
          bigtom

          You are obviously showing the industry name in your list. Saying there is NO industry data available does not seem to be true.

           

          Is this a list view or a portal?

          • 2. Re: Cascading Value List with Linked Data Sources
            laguna92651

            The records are in list view. I meant there is not industry data in the Product_Lines table, other than indirectly by linking with the Accounts table.

            • 3. Re: Cascading Value List with Linked Data Sources
              philmodjunk

              If I read this correctly, Once you have selected a product, you want to see a list of all accounts that have a link to that product? I'm not sure from your post, which TO is the basis for your layout and which data source table is specified for the "value lists" TO, so I am going to use my one names, but identify the data source tables in hopes that you can then replicate this design in your system:

               

              Your layout is based on a join table linking products and accounts if I understand correctly. I'll call it Product_Lines as you did originally. For the conditional value list that you describe, all you need are two occurrences of this table:

               

              Product_Lines::ProductID = Product_Lines|SameProduct::ProductID

               

              The left TO is the one specified for your layout. In value list set up, specify the right hand TO as your source of values from your value list and select Product_Lines|SameProduct::AccountID as your first field. If you select "Include only related values starting from Product_Lines, you'll get a list of AccountIDs for those accounts that are linked to the same product as you selected in your first value list. What you show in the second field will depend on what fields you have. You may need to link in another TO of accounts to Product_Lines|SaemProduct so that you can list a description or name field from this added TO of accounts to get a name listed with each account.

               

              For more on conditional value lists you might be interested in this free instructional file:

              Adventures in FileMaking #1 - Conditional Value Lists

               

              This file has multiple working examples of different ways to set up CVL's with detailed documentation on each.

              • 4. Re: Cascading Value List with Linked Data Sources
                laguna92651

                Thank you, that worked fine and thanks for the example file I will go through the file. Took me a little while to figure out how to lay a field over the Acct ID drop down. I didn't want to show the Acct ID and Industry after they were selected, only show the Industry.

                • 5. Re: Cascading Value List with Linked Data Sources
                  philmodjunk

                  I didn't want to show the Acct ID and Industry after they were selected, only show the Industry.

                   

                  Laying a "label" field with browse mode access blocked over the drop down is a commonly used work around. However, if you selected the pop up menu option, this trick is not necessary.

                  • 6. Re: Cascading Value List with Linked Data Sources
                    laguna92651

                    I saw the Pop up menu option solution last night as I was going through your example file, thanks again.