Conventional wisdom is each table has a unique, numeric, auto-entered, not modifiable, ID field.
Using names for the relationship is a bad idea.
Users can have many Jobs?
Companies can have many Users?
Users can have one Company?
The White Paper for Filemaker Novices is worth a read.
How do you create a conditional value list?
Answer ID: 5833Last Updated: Sep 29, 2011
1st Link from the Google "conditiional value list filemaker"
Here are a few more links on conditional value lists that you may find helpful.
Forum Tutorial: Custom Value List?
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 help guys but this now creates another problem.
Reading from the lists is fine and gives the desired results. If I create a table called Company and add a name in the Company field then when I create the table Users with fields called Company and User Company displays the company name from the Company table. That works a treat. When I create a new table called Job Details and have a Company and Users field I get the company listing which then gives the correct users listing as well.
Where it doesn't seem to work is the reverse where I create a new record from the Job Details field and enter the details in there. It doesn't add the information into the Company or Users tables. Is this by design or am I not understanding how these value lists are supposed to work?
I'm moving up from Bento so I'm used to the significant ease of use of Bento and I'm just trying to get my head around how FileMaker does things.
I've created a work around actually which I think is better and safer.
Instead of creating the data from the Job Details table I've created a couple of buttons so that if the User or Company doesn't exist it can be created by clicking the appropriate button.