Child record with multiple Parents

[Shaun Hutchinson]

Hi all!

This seems like it should be easy but for some reason I'm beating my head against the wall with this.

I have two tables. Site_records and Contact_records. Site_records is the parent record that holds all of the basic information for a site (address etc). Contact_records is the child record that holds (you guessed it) the contacts for the site. The two tables are joined by the site_records_id field. The Contact_records are viewed through a portal.

As it currently works (I originally built this with little knowledge of Filemaker), every time a contact is added to a site, a new record is created in the Contact_records table. This is all well and good, except that it's possible that a contact may belong to more than one site. So if John Smith works for 5 sites, I will have 5 records for John Smith in the Contact_records table.

That's problematic because if John Smith's phone number changes, we have to change it 5 times.

So...is there a way to create only one record in Contact_records and have it appear in multiple Site_records? I've managed to make it work with a repeating field, but this seems like a horrible solution because when I want to remove that contact from one site, I have to remove that repeating entry and it just seems messy and not good.

Thanks for the help!

Shaun

[efen]

You need a third -join - table between your present tables

[efen]

See

http://fmforums.com/forum/topic/50942-portal-grouping-problem/page__p__239210#entry239210

in which there is a demo file by comment

[Shaun Hutchinson]

Efen - Thank you for this. I appreciate it.