8 Replies Latest reply on Oct 12, 2010 1:12 PM by WinstonChurchill

    Variable Value Lists



      Variable Value Lists


      Field A and B both use value lists for data entry, how do I get field B to use a value list dependant on the selection made in field A.

        • 1. Re: Variable Value Lists

          Use a conditional value list.


          If, after reading this article in the Knowledge base, you have further questions, don't hesitate to ask.

          • 2. Re: Variable Value Lists

            Thanks Phil, that was quick. I think I have the idea but will be a while before I know for sure, it seems that I need to create records for all possibilities initially, I have 5 fields I want to work with and some fields have up to 20 options, which might take some time.

            • 3. Re: Variable Value Lists

              It may be possible to so structure your values table that you don't have to enter the same value multiple times for each category of which it's a member.

              Say your values table has two fields: Category and Value.

              You have a value "Apple" that will be a member of the "Fruit" category and also a member of the "Tree Fruit" category. You could create two records with this value, one with Category = "Fruit" and one with category = "Tree Fruit" or you could format your category field as a check box field and click both the "Fruit" and "Tree Fruit" check boxes. "Apple" will then appear in the value list when either "Fruit" or "Tree Fruit" is specified in the controlling category field of the main record.

              • 4. Re: Variable Value Lists

                Struggling a little on the third level here.

                I can get everything working just fine if I set up a table and set of records for fields 1 & 2 and another table and set of records for fields 2 & 3 (and so on), but is it possible to just set up one table and one set of records for fields 1, 2 & 3 and so on.

                • 5. Re: Variable Value Lists

                  Certainly you can set this all up in one table, You can even define completely different value lists that use different relationships to control what subsets of all the data appears in the value list.

                  Whether that is a good idea or not depends on your value list data and how you need to manage it.

                  If that doesn't help you set this up fully, you may want to post a description of the fields, the values and how you need them to work. (Use actual names and values, not field 1, 2, 3 or values a, b c as they are harder to process.)

                  • 6. Re: Variable Value Lists

                    It might be a bit difficult to use the real life set up, some of the terms might mean nothing to you or be difficult to relate to each other, but a parallel example might be:

                    Layout for expenses, where the first field was for type of expense such as auto, recreation, groceries etc, the second field for general products such as fruit, veg, meat (from groceries) and the third field for specific products like apple, orange banana (from fruit).

                    If I create a table with records for all 3 fields which covers all combinations of the 3 fields and follow the example you linked me to, my first drop down menu lists auto, recreational, groceries etc, if I choose groceries, my second field drop down menu lists fruit, veg, meat, but if I choose say fruit my third drop down menu list all groceries (salmon, peas, bananas etc) and not simply all fruits.

                    I can solve this by creating two sets of records which relate (1) expense & general product and (2) general product & specific product and indeed this might be easier for now, but I was thinking further down the line when I might want to add something new, it might possibly mean adding a record to each of two layouts with two fields rather than one which contained all 3 fields.

                    Hope that makes sense.

                    • 7. Re: Variable Value Lists

                      Ok, that clears up the original question a lot.

                      Whether you use one data source table or two, you have to use two separate relationships for each value list that match to the correct subset of value records.

                      Here's the single table approach:

                      ParentTable::ExpenseCat = ValueTable::ExpenseCat

                      ParentTable::ProductType = ValueTable 2::ProductType // make ValueTable 2 a table occurrence of ValueTable

                      If you define ValueTable with three Fields: ExpenseCat, ProductType, Item

                      You can define 3 value lists:

                      Expense Categories : specify values from ValueTable::ExpenseCat

                      Product Type : specify values from ValueTable::ProductType, show only related values starting from ParentTable

                      Items : specify values from ValueTable 2::Item, show only related values starting from ParentTable

                      • 8. Re: Variable Value Lists

                        Got it thanks Phil. It was the second relationship I was missing.