Use a conditional value list.
If, after reading this article in the Knowledge base, you have further questions, don't hesitate to ask.
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.
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.
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.
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.)
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.
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
Got it thanks Phil. It was the second relationship I was missing.