Welcome to FM.
You need two tables, Company and Ship.
Each needs an id field (call them _K_ID_Company, _K_ID_Ship) which you will use to relate the two. I'm assuming you know how to create auto enter id fields but if not just say so and we can start from there.
A company can have many ships so in the Ship table create a foreign id field called company_id which is a plain text field. in it you can enter the company_id so that you can identify which ship belongs to which company.
In the manage database (command/control shift D) under the relationships tab create a relationship between Company and Ship with
Company::_K_ID_Company = Ship::company_id.
To create a dynamic value list, once you're done with all that, go to FIle>manage>value lists.
1.Create a new value list.
2. select the option: 'use values from field' and click specify field
3. where it says 'Use values from first field' select the ship table and the ship name field(or whatever field you want to show up in the value list).
4. selecet the option 'include only related values startting from' and select the company table you created that is related to Ship thorugh the company_id relationship.
That should do it. Just make a field on the company layout and choose your value list as the dropdown. It will show a list of all the ships associated with that company.
Thank you so much for the above information.
I have run through all the above yet still seem to be geting something wrong.
Can you tell me if i have the relationship bit right?
And the other question is do I have to set value lists for both company and ship fields?
Sorry to be a pain but I really want to learn this.
Thank you again for you help
If you're still stuck...
Yes you have the relationships slightly confused.
In your example it should be _pkCompany_ID = _fkCompanyID
(fk meaning foreign key)
That way, when you are on a layout based on Company you can see all the ships that are related to that company (ie, have a foreign key equal to the company primary key)
Don't be discouraged if this doesn't make sense right away, its the most complicated concept to understand in databasing (but you can do it!)
If I were you, I would get rid of that large table for now and focus on the realtionship between company and ship.
try this and see how it goes. If you don't get it, upload the file and I can build you a small demo of how it should look.
How kind of you.
I have managed to sort it out now with the help of youtube lol
I am learning every day and like you say, the relationship bit baffals me loads.
I am doing some online help using Lynda.com which if helpful.
Thank you again for all you help