I call this a Hierarchical conditional value list. WHere the value selected in field 1 controls what values are listed for field 2 and the selection in Field 2 then controls what values are listed in field 3, and so forth...
See the last link in this set of links. You can refer to the other links if you are unfamiliar with using a relationship to control a conditional value list.
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.
The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Thanks for the reply. I did manage to get a 3 level hierarchy working. used one of the examples you posted. thanks. However, I need the 4th level - which is kind of working, but it does not present the list sorted by the previous level.
I added the screenshot of the relationships - sorry it is in portuguese, as this is the company I am creating the db for.
This is how it goes:
level 1 - user selects a company. This company has a set of departments only pertinent to it
level 2 - user then select out of these departments which one he/she wants to add data. Each dept has a set of only pertinent to it.
level 3 - user then select if he or she wants income or expense.
Up to here everything is working fine using the attached relationships schema and value lists.
But then on level 4 (the actual expenses or income lines) I see the whole list, not only the ones for expenses or income.
Also, I think I am doing something wrong, as in my data table, where all this info is it seems to me that I could reduce the amount of data, but do not know how: let me explain:
From level 4 backwards: I have 1 line which is expense which is present in 3 or 4 departments for one or more companies. But sometimes, I have one expense line which is only present in one department for one company.
For example: Water is an expense that all companies will have to pay and it comes up 8 times in my table:
Cia A - depto J - Expense - Water
Cia B - Depto D - Expense - Water
How can I clear that.
How I wasn't too confusing
Here is the water case. 1st field: cia, 2nd field dpto, 3rd field transaction (expenses) and 4th field the item line.
I can't read the language, but the relationships used do not look like they are correctly set up. Using the same match field on for relationships on each side of a given table occurrence box does not look correct to me.
Also, if you look at the tutorial for hierarchical value lists, this "long chain" of table occurrences--which can make intuitive sense, is not what is described in that link.
Instead, you end up with table occurrences that directly link to the table occurrence on which your layout is based, each with one of the value list formatted fields specified as a match field in the relationship. A 4 level hierarchical value list might thus produce relationships that look like this:
LayoutTable::ValueLIstField1 = ValueTable1::MatchFIeld
LayoutTable::ValueListField2 = ValueTable2::matchFIeld
LayoutTable::ValueListField3 = ValueTable3::MatchField
LayoutTable::ValueLIstField4 = VlaueTable4::MatchField
ValueLIstField1 is usually a non-conditional value list--either custom values are all the values in a table.
ValueListField2 then lists values from valueTable1
valueLIst Field3 lists values from valueTable2
and so forth...
Once again thank you for your time. I got at the end. What I did I created two tables one for entering data e the other one for the items list
The item list contain the CIA, Dept, Transaction and Item. Also an ItemID. I also created 3 global fields (CIA, Dept and Transaction).
The Entry Data table contains an entryID, and itemID a data field (not used - it will be deleted or linked to another table).
Then I created the Value Lists for CIA, Dept, Transaction & Item.
Attached a representation of what i achieved. The Value List Selection is merely to make the visual easier. Now it is working like a charm.
Thanks for your support