10 Replies Latest reply on Dec 17, 2015 10:54 AM by laurahildebrand

    Conditional Value List Question

    eldoonmad

      Title

      Conditional Value List Question

      Post

      Hello,

         I am very new to FileMaker Pro, still figuring things out. Anyways here is my situation. I am creating a database for asset management and inventory purposes. I have one table for my products as they come in and another that is specifically for assets, taking the product table's fields and adding additional information to it such as location, serial number, etc. Part of the asset form layout that I have allmost succesfully completed uses, what I guess are called, conditional value lists.

      Basically it goes like this, on my asset layout I have a field titled category which has a relationship with the category field on the products table. It is set as a pop up window. When I select it, it shows me a list of all categories on my products table.

      Then the next feild in the asset layout is titled manufacturer. This will only bring up manufacturers from the product table that are related to the category I selected in the previous field.

      That works well on my database, so then I continue, I then have a product line field on my asset layout that will show only a list of product lines from the products table that are related to the previously selected manufacturer.

      OK, that works just fine as well. Then the next feild in the asset layout is titled model. This is where I am confused, it shows a complete list of models from the product table relating to the selected manufacturer, not a list of models relating to the product line. 

       

      Here is a quick breakdown

      Tables : 

      Products

      Assets

       

      Relationships:

      Category feild in both Products and Assets table

       

      Assets Layout fields:

      Category (list of all items in the category feild from the product table) WORKS

      Manufacturer (list of manufacturers from the product table based on selection in category)WORKS

      Product Line (list of product lines from the product table based on selection in manufacturer)WORKS

      Model (list of models from the product table based on selection in product line) Gives list of all models related to manufacturer

      Screen_Shot_2012-03-08_at_8.44.06_AM.png

        • 1. Re: Conditional Value List Question
          eldoonmad
          /files/17a2a7c79c/Screen_Shot_2012-03-08_at_8.44.29_AM.png 705x614
          • 2. Re: Conditional Value List Question
            eldoonmad
            /files/eb636838f6/Screen_Shot_2012-03-08_at_8.44.47_AM.png 705x614
            • 3. Re: Conditional Value List Question
              eldoonmad
              /files/7eabacd00a/Screen_Shot_2012-03-08_at_8.44.59_AM.png 705x614
              • 4. Re: Conditional Value List Question
                eldoonmad
                /files/8567c778b4/Screen_Shot_2012-03-08_at_8.47.30_AM.png 1280x800
                • 5. Re: Conditional Value List Question
                  eldoonmad
                  /files/3276b96067/Screen_Shot_2012-03-08_at_9.03.52_AM.png 691x529
                  • 6. Re: Conditional Value List Question
                    philmodjunk

                    What you don't document is the relationship between products and Assets. To get a multi-tiered set of conditional value lists to work requires more than a single relationship between products and assets.

                    You appear to have this relationship:

                    Products::Category = Assets::Category

                    I then have a product line field on my asset layout that will show only a list of product lines from the products table that are related to the previously selected manufacturer.

                    It may look like that is what you have, but if my analysis of your relationship is correct, you actually get a list of all Products for the specified CATEGORY--not the manufacturer you selected. If you have only one manufacturer in a given category for the values you used in testing, then this will not be a visible difference.

                    Then the next feild in the asset layout is titled model. This is where I am confused, it shows a complete list of models from the product table relating to the selected manufacturer, not a list of models relating to the product line.

                    Actually, you have a list of all models from the specified category as once again, it is the relationship based on category that is actually limiting what items appear in the value list.

                    • 7. Re: Conditional Value List Question
                      philmodjunk

                      To fix this, use multple occurrences of your Products table--each with a different relationship:

                      Assets::Category = Products::Category  (You have this one)

                      Assets::Manufacturer = ProductsByManufacturer::Manufacturer

                      Assets::ProductLine = ProductsByProductLine

                      In Manage | Database | relationships, make a new table occurrence of Products 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 ProductsBymanufacturer.

                      We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                      Repeat this process to bet ProductsByProductLine

                      Now use Manage | Value Lists to specify that your values from ProductLine Selection be shown from ProductsByManufacturer instead of Products and set up the Model value list to list values from ProductsByProductLine.

                      • 8. Re: Conditional Value List Question
                        eldoonmad

                        Worked like a charm!!! Thank you.

                        • 9. Re: Conditional Value List Question
                          GauravRampal

                               Would the source be available for the multi-TO solution?

                               I am trying to understand why multiple TOs are needed. I think this is doable with one table occurence.

                          • 10. Re: Conditional Value List Question
                            laurahildebrand

                            Somewhat lower-level question here - I'm trying to set up a hierarchy much like the one that you were working on, and would love to see the screenshots that you posted.  I'm only seeing partial URLs with some dimensions, though:

                             

                            /files/17a2a7c79c/Screen_Shot_2012-03-08_at_8.44.29_AM.png 705x614

                            /files/eb636838f6/Screen_Shot_2012-03-08_at_8.44.47_AM.png 705x614

                            /files/7eabacd00a/Screen_Shot_2012-03-08_at_8.44.59_AM.png 705x614

                            /files/8567c778b4/Screen_Shot_2012-03-08_at_8.47.30_AM.png 1280x800

                            /files/3276b96067/Screen_Shot_2012-03-08_at_9.03.52_AM.png 691x529


                            Do these images still exist somewhere, or is there some step I'm missing?  Any help would be appreciated.