I've working with a travel company, and they've added a custom module for reservations. In turn, they've added a relationship between contacts and reservation to keep track of which customers are on which reservations. On this relationship, custom fields were added, like their requested bed configuration and the date they submitted their paperwork. These custom fields are displayed on the reservation's contacts subpanel as well as on the contact's reservations subpanel. Setting these up took some tricky work with the metadata files but ultimately didn't require any custom code.
I'm adding another custom module, Emergency Contacts, and a many-to-many relationship between Emergency Contacts and Contacts. They also want to keep track of which Emergency Contact each contact nominates for each reservation, and to accomplish this I've added a relate field to the contact-reservation relationship. And this information should be displayed on the same subpanels as above.
I've set this up, and it *almost* works. The database field gets added. Some custom code I wrote that saves the data via the bean relationship works successfully. And the new field is being displayed with data in the subpanels.
Unfortunately, the data in the subpanels is wrong -- it turns out that the SQL that backs the subpanel joins the emergency contacts table but doesn't constrain it by the foreign key. After the SQL is executed, some built-in PHP code removes the duplicates. Thus the subpanel displays an arbitrary emergency contact for each record, rather that the emergency contact that is actually referenced.
I've been looking through the SugarCRM code, and it is very close to actually working. Inside One2MBeanRelationship::buildJoinSugarQuery, there's a call to $sugar_query->joinTable->on()->equalsField() that configures the desired foreign key constraint. However, when the SQL is compiled, the constraint is ignored because the field for the foreign key has been marked as IsNonDb().
I think I can make this work by overriding the REST API and adding code to manually tweak the SQL. But before that, I am curious if there's a way to do this properly. Maybe a tweak to the field definitions would fix this. Unfortunately, I haven't been able to find comprehensive information on how the source, rname, rname_link, etc exactly work. I've spent some hours tracing the code and trying different combinations, and while I haven't found anything that works, I don't yet understand it enough to be sure it can't work.
This is SugarCRM 7.7, by the way.