Better too much detail (not the case here) than not enough. Then I have to dig out my crystal ball and the darn thing just isn't reliable...
What you describe is called a conditional value list. I have a lot of links on the subject, what you describe is fully covered in the first 3 links--once of which is a demo file.
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 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The next to 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 and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.
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
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
I'm out of the office for a few days but will try this out as soon as I'm back.
Really appreciate the fast response.
Been reading the threads and feel the light shining through, but a question before I actually try anything…
I should have mentioned that I have a “test” database – a copy of the production dB – so I can make as many mistakes as I like in a safe environment.
Just to recap on current situation:
Table1 – which I populate with a new record from…
Table2 – which has the unique codes and descriptions.
One relationship Table1:code to Table2:code
Before this post I had created a third table – Table3 for the parent code. Relationship with Table2 only.
My question is, reading the examples, do I actually need this third table, or am I better to add new fields in Table1 and Table2?
If I were doing this in SQL I would create the new table as described, but I’m not sure which is the best method in FileMaker?
I don't actually need the parent code in Table1, but from the examples I don't think that would work. That is, from a form looking up a code from Table3, with no relationship to Table1, that once selected offers a choice of codes from Table2 that, when selected, populates the field on Table1
Being new to FM not just looking for the solution but also "best practice"
Since my last post, I've released the first in what I hope will be a series of Freeware instructional database files. The first is on conditional value lists and covers all of the above plus additional variations--with working examples of each all in a single file and with very complete descriptions of how each is set up and how they work. You are welcome to visit my FaceBook page (see link below) where you can download a free copy.
With regards to you last question, in an SQL based DB or FileMaker, there is no simple rule to answer your question as you have to look at the data you would put in that added table and consider all the possible ramifications that may ensue if you do or do not use that table. (And FIleMaker's design is easily enough changed that if you set up your design one way and then realize that your other option is better, you can modify your design accordingly.)
Apologies for not coming back sooner. Several projects on the go at the moment.
Thanks for all of the help. My Hierarchical Conditional Value lists are now working. :)