Relationship help needed please

[ssimons]

Hey all.

I'm not real familiar with relationships and I'm having an issue that I'm not sure how to fix.

First - I have a table for Country/Country Code so the end user can enter Countries (to populate drop down field in other tables) to their Country calling codes

Second - I have a table for Supplier Information. On the entry screen for Supplier Information the end user selects the Country from a drop down and the Country Code is auto filled

Third - I have a table for Contact Information. Idealy I would like the entry screen for the Contact Information to work the same way where a user selects the Country from a drop down and the Country Code is auto filled. This is where I'm having the problem.

I have the following relationships going on between these tables.

Country/Country Code::Country = Supplier Information::Country

Supplier Information::Supplier Key = Contact Information::Supplier Key

How do I make the connection from Country/Country Code::Country to Contact Information::Country?

Hope I've explained this correctly. Thank you in advance for any help you can offer.

Sarah

[comment]

The connection is already made. If you place a field from Country/Country Code on a layout of Contact Information, it will show data from the correct record (i.e. the country selected in the Supplier Information table. Similarly, if you refer to Country/Country Code::Code in a calculation evaluated from the point-of-view of Contact Information, it will evaluate as the calling code of the same country.

(Whew! Wouldn't it be simpler to name the tables simply: Countries, Suppliers, Contacts?)

[ssimons]

Thanks for the advise. Currently it is showing the Country selected on the Supplier record, but I don't want it to do that. I would like it to be blank and then the end user select it because there may be a situation where the contact isn't located in the same country (I know, that sounds strange). Is there a way you know of that I can make it work the same way, but not be linked to the Supplier record?

(I agree. Maybe I'll go ahead and make that change.)

[comment]

You can have the Country field in Contacts auto-enter or lookup the Country from the parent Supplier (or from Countries, it really doesn't matter - provided that a Contact has only one Supplier, and a Supplier has only one Country, it follows that a Contact has only one related record in Countries). Then you can change the Contact's country at will.

However, if the Contact record needs more information from the Countries table (such as the code), you will also need a separate TO of the Countries table, connected directly to Contacts. Then refer to this TO to get the code for the Contact's country.