Assign main contact to organization through editable field

[sirmatter]

Hi FM Forum friends,

I am attempting to create a way that I can assign a "Main point of contact" to a record via a related table.

What I would like to do is have a field that I can place the Contact ID number into, this would then change the main point of contact fields (ex, firstname, lastname etc.) to match the contact in the related database in the organization table. There is a list of over 10,000 contacts, and I need a way to easily see who the main point of contact is for this organization and still have the ability to view additional contacts through a portal.

My relationship is: Org - Org_Contacts - Contacts

If you think of a better solution, or if I am thinking about this all wrong, please let me know. 

Thank you!

Edited June 2, 2015 by sirmatter

[Kris M]

if the relationship between Orgs and Contacts is such that 1 org has 1 and only 1 primary contact then you don't need the join table.

Org:;primary_contact_ID related to Contacts::contact_ID is sufficient.

Edited June 2, 2015 by Kris M

[sirmatter]

Each Org has unlimited contacts but only one main contact. This is the reason for the join table. 

Example: The org has a "President" who is the main contact for our contracts. However, there may be 20 people who are secondary contacts. The "President" may change over time and I need a way to reassign the "Main contact" based on the contact list.

 

[eos]

if the relationship between Orgs and Contacts is such that 1 org has 1 and only 1 primary contact then you don't need the join table.

Org:;primary_contact_ID related to Contacts::contact_ID is sufficient.

​Where maybe for a novice user it should be pointed out that this would need to be a new TO of Contacts (called, say, Contacts::primary_contact), not the one in the existing relationship chain.

[sirmatter]

EOS,

Are you saying that there should be a second occurrence of my contacts table to assign the main contact?

I just want to make sure I am understanding this correctly.

Also, If I do create a second occurrence of the contacts table, would this allow me to change it without corrupting the relationship?

 

Edited June 2, 2015 by sirmatter

[eos]

Each Org has unlimited contacts but only one main contact. This is the reason for the join table. ​

1. Use a join table to store a potentially unlimited number of contacts (and presumably their role and other data that describe an association).

2. Use a single field in Organizations to store the single primary contact (ID), and use an additional relationship to display/reference/access the name.

Edited June 2, 2015 by eos

[sirmatter]

Thanks EOS,

I think that I understand this but let me write it out for clarification.

Tables:

Organization  -  Organization_Contacts  -  Contacts  Which is a one to many relationship

AND Organization  -  Primary Contact (Second occurrence of Contacts) Which is a one to one relationship

I would then need a PrimaryContactID field in Organization table which relates to the ContactID in Primary Contact Table (Second occurrence of Contacts).

Does my Primary Contact Table need a separate ID or can I use the same ID that already exists in Contacts?

Thank you for the help!

 

[Kris M]

EOS yes i probably should have noted the seperate TO.

sirimatter:

Contacts table primary key should be related to the Orgs table foreign key primary contact ID

See my original post "Org:;primary_contact_ID related to Contacts::contact_ID is sufficient"

 

From this "Does my Primary Contact Table need a separate ID or can I use the same ID that already exists in Contacts?" i would suggest you dive into the differences between a table and a table occurrence

Edited June 2, 2015 by Kris M

[sirmatter]

For people to reference later. This post seemed to help me understand TO (table occurrence) a little better.

http://forums.filemaker.com/posts/3d8b364a6c

Thank you everyone for the help. I learned a ton!