SugarCRM has a great feature that allows referencing fields in related modules to auto-populate fields in the primary module. However, I haven't been able to definitively determine why I can make it work in one module, but not another. I have a suspicion, but if I'm right, I have to find another way to accomplish the same thing.
This is for SugarCRM Professional, 6.4.2, hosted on our own servers.
What Works
Using two default modules (Contacts, Opportunities): I am able to make Opportunities.name a calculated field that references two fields from Contacts, using the following formula:
concat(related($contacts, "last_name"), ", ",related($contacts, "first_name")," - ", getDropdownValue("timeline_options", $timeline_interest_c))
The relationship between these two modules is "many-to-many". Opportunities is the primary module, Contacts is the secondary, and the relationship name is "opportunities_contacts". Any one Contact can be related to multiple Opportunities, and any one Opportunity can be related to multiple Contacts.
What Doesn't Work
Using one custom module (Applications, created in and deployed from Module Builder) and one default module (Contacts): I am not able to make Applications.name correctly reference the same two fields from Contacts, using the following formula:
concat(related($contacts, "last_name"), ", ",related($contacts, "first_name")," - ", getDropdownValue("timeline_options", $timeline_choice))
The relationship between these two modules is "one-to-many". Contacts is the primary module, Applications is the secondary, and the relationship name is "contacts_applications". One Contact can have any number of Applications, but every Application can only be related to exactly one Contact.
When I try to save the formula, I get this in a popup message: "Unknown field: contacts"
What Am I Missing?
Obviously, the thing that is different in these two scenarios is the type of relationship. What I don't understand is the technical implications of that different relationship. In other words, why does the Opportunities module know about $contacts when the Applications module doesn't?
Is it...
- the nature of how "many-to-many" vs. "one-to-many" relationships are stored? or,
- the fact that in the first instance, the calculated field resides in the "primary module" of the relationship, while in the second instance, the calculated field resides in the "secondary module" of the relationship? or,
- something else entirely?
The answer to this question may suggest what I might try differently in order to accomplish what I want.
Update... What Still Doesn't Work
Since originally submitting this question, I've tried a couple more things.
- I recreated the one-to-many relationship so that Applications is the primary module and Contacts is the secondary module, to no avail. The same error occurs. This tells me that #2 from above is not the problem.
- I attempted a different formula, accessing a field from Opportunities, which has a one-to-one relationship with Applications. The same error occurs, this time telling me "Unknown field: opportunities". This tells me that it isn't necessarily the "one-to-many" relationship that's causing problems as in #1 above, but perhaps the fact that it isn't a "many-to-many" relationship.
Desired Answer Has Two Parts
I want to understand the reason for the different behaviour, and I also want to know the best way to access fields in a related module (on the "one" side of "one-to-many") in order to auto-populate a calculated field in my new module (on the "many" side of "one-to-many").
My thanks, in advance!
