Company > Contacts: multiple relationships

[JenniSTM]

Hi all,

We run a business that has been going for years and often deals with freelancers.
I'd like to update our database so that we have separate layouts for contacts and for companies (currently with have multiple company entries, each for a different contact).
The issue is that, for example, when a freelancer works for multiple companies, they end up with multiple contacts;
OR when a supplier's rep changes job & works for another supplier, they again have multiple entries (if we delete the old contact we lose the contact info off the old orders).
Also, just to keep things interesting, sometimes our suppliers can also be our customers.

So, I'd like to be able to have relationships between companies & contacts that can go both ways, so I can list the companies on the contact's layout and the contacts on the company's layout.
I think this would really tidy up our database and make it easier for newcomers to understand our relationships with customers and suppliers.

Is this possible?

I'm still learning about relationships and scripting, so I'm really hoping someone out there can help me with this.

With hope,
Jenni.

[comment]

5 hours ago, JenniSTM said:

So, I'd like to be able to have relationships between companies & contacts that can go both ways, so I can list the companies on the contact's layout and the contacts on the company's layout.

If a contact can represent multiple companies and a company can be represented by multiple persons then there is a many-to-many relationship between the two and you should add a join table between the two. Then you can have a portal of contacts on the Companies layout and vice versa. Here's a (very old) demo file showing the concept.

OldJoinDemo.fmp12

And, any time you have a new dealing (order?) with a company, you can select the company first and then select a contact from the list of contacts that are related to it (you may want to add a Deactivated field to the join table to prevent past contacts from showing in the list).

 

Edited October 27, 2023 by comment

[JenniSTM]

Thanks!