Many-To-Many Relationships

A client called me with a problem today. He has a custom object called "Projects" in which he keeps track of the construction projects his company is working on. He also keeps his contractors in the system as Accounts and Contacts.

My client wanted to be able to create a link on the Account record to the Project Record to show which Projects a given contractor is working on. The problem he ran into appears when a contractor works on more than one project. Many contractors can link to the project record, but one contractor can't link to many project records.

The problem is based on the one way link. Since the contractor (an account record) has only one field pointing to the project record, only one project can be assigned per contractor. We have what is called a "One-to-Many" relationship and we need a "Many-to-Many" relationship.

While the functionality is not "built-in" to, there is a way to solve this problem. The solution is to create another object that serves as a link between the contractor and the project. We called this object "Project Contractor". Each "Project Contractor" record will have two fields, a lookup relationship field associated with the Project Object and another lookup relationship field associated with Accounts. Each record will, in essence, be a relationship between a particular project and a contractor. We name each record according to the project and the name of the contractor separated by a hyphen, for example, "Downtown Building-Jones Construction".

Using this third object allows there to be a related list on both the Account Detail Page and the Project Detail page called "Project Contractor". Each related list can show multiple records. Therefore, a given contractor record (an Account record) can show on its related list all of the Projects that the contractor is working on. Then on a given Project record, there will be a related list that shows all of the contractors that are working on that project.

You can move between the two records (contractor and project) via the Project Contractor record contained in the Project Contractor Object that will be found on the related lists of the Account and Project detail pages.

In this way we have a many-to-many relationship link between the Account record and the Project record. Very, very cool.


Fraser Steen said...

Just trying to solve this exact problem, thanks for the clear explanation

Anonymous said...

this is exactly what I was looking for ... thank you.

Narasimha said...

This is cool. I tried this and understood how M:M relationship works in Salesforce...Thanks for the clear explanation.

Anonymous said...

Thanks Mike. I have a similar problem but the relationship that we need is on the Account object and we need a custom object called "Business Relationships". This business relationships custom object will manage the many-to-many relationships between Investor and Advisor OR Investor and Partner, all of which are Accounts. Now we can use record types to designate between the various types of accounts but what if one Account is an investor but also a partner for a different company and also serves as advisor for a third company. This is a real example. How do you manage this?
Thanks in advance.

Deepabali said...

Was just trying to do this.Google search revealed this site.Thanks.Will try to execute now.

Amitabh said...

We are running into following problem related to many-to-many relationship, which is following -

We want to create many-to-many relationship between Leads(Standard object) & Universities(Custom object). We created a 3rd object named LeadsUniversities but when we are trying to create Master-Detail relationship and selecting the Related-to drop-down, there is no object named Leads shown. Other Standard objects like Account are shown. Can't we establish Master-Detail relationship with Leads? We would appreciate any ideas why this is happening.

Anonymous said...

This is all good. But there's a problem to this.

You create a Project first. Then in the related list ( Project-Contractor), you click New.. you go the page which has 2 lookup fields. One: Project, auto-populated. Two: Contractor Name. I cannot use lookup for contractor name as the Contractor is not yet created in Salesforce yet at this point.

So, I have to cancel this. Create a contract now and then go to 'Project-Contractor' related list and create the relation between two.

Everytime, I have to create a Project and then Contractor (or vice-versa) and then create the relationship between the 2 records which is an extra step for the users.

Let me know if my understanding is correct! Any alternatives for this? Please let me know.