Many-to-Many relationship, subpanel displaying middle table local fields


Using Studio I created a Many-to-Many relationship from Project module to Product Module.
This created a middle table pointing to both modules and a local subpanel definition specific to each of those modules.

I need to have on each of the records belonging to this middle table 3 local fields (on built upon a drop-down list and the 2 remaining ones as integer types). Functionally wise this would work similarly to the contact role in the opportunity module. Each opportunity may have contacts with a role specific to the present opportunity. A contact involved into multiple opportunities may have a specific and different role for each of them.

I implemented with Mysqladmin the fields into the middle table and encoded those into the metadata file setting up the Many-to-Many relationship for this table.

Being new to SugarCRM encoding and programming structure I looked for examples on the web and found a few close ones.
However, after implementing different variants of Vardefs/Layoutdefs the SugarCRM log in Debug Mode, the database query related to the subpanels remains missing.

What I need is to understand how and where to declare the 3 fields of the middle table and get them queried to be reported into a subpanel.

Many thanks to point me to the right direction or an example to follow.

  1. I would’ve expected you might be able to do this through adding them in vardefs.
  2. I’m not sure what your doing is the best solution… what if instead…

You create a new module using module builder. The new module has two many-to-one relationships with projects… you then create your extra “meta data” as regular fields in the new “middle module”… call it “Project Relationships” or whatever makes sense to you.

I was looking at potentially doing this between accounts->projects or users->projects because like you, I want extra data about how the two relate.

My proposed method here would require 0 programming and should be 100% upgrade safe.

1 Like

Hi Sieberta, all

could you outline the above steps in more details?
I would like to have the same “relation field” editing possibility as between opportunity and contacts but to leads instead to contacts.
Means I would like to have fields (at least opportunity_role) which document the relationship between opportunities and leads.
I would prefer to do it directly as I could live with leads instead of contacts but even creating an new modul would be possible if needed.

I am using 7.9.14

Hello Kaeser49 - since sieberta didn’t reply here wondering if you figured out how to use a “middle module” as he suggested?
It seems like a solid approach, so I’m attempting the same right now and would also greatly appreciate more detailed instructions, especially for the relationships aspect.


What do you need to know? It has been a while since I’ve used module builder, but I will help if I can.

Let’s take the example of contacts for projects having a project role.

In module builder, you’d create a module (let’s call it Project Contacts).
The way I picture it right now, it would have a many to one relationship with projects (in other words many of these “Project Contacts” could be assigned to one “Project”.
Then it would have a many to one relationship with contacts (in other words, many of these “Project Contacts” could be assigned to one “Contact”.

Then add whatever other fields you want, like “Project Role”, which might be a dropdown with choices like “Project Manager”, “Project Accountant”, “Billing Contact”, whatever else makes sense in your world.

Then, after you deploy the module, you could go to that module and connect projects with contacts with the extra data.

I have never done this. I don’t know if my steps are good… and I don’t know if it would work as expected. I think all records have to have a “Name”, which you could auto-populate with logic hooks (but this would require some programming). The name could be " " for example.

If you then turn on the subpanel in projects, you could see all the roles at the project level. If you turned on the subpanel in contacts, you could see which projects those contacts have roles for.


1 Like