Author: Yuri Gee
5 minute read time
In this article, I will demonstrate specific auto update conditions for Sugar Logic formulas that may arise when using related functions (related, rollups, counts, etc.) across a hierarchy of multiple modules (more than two). One of these modules contains related formulas within 1:many (or many:many) relationships. I'll explain how to identify these conditions and provide strategies to mitigate any potential issues.
Scenario
To keep things simple, we'll use the stock modules: Opportunities, Accounts, and Cases. We'll create a scenario to count the number of related cases per Account for the Account and the related Opportunities records, using basic related and count formulas.
In the Accounts module, we'll add a field to the layout called num_cases_c with the formula: count($cases)
Opportunities related to an account will include a field also named num_cases_c with the following formula: related($accounts, "num_cases_c")
The num_cases_c field will display the number of support cases related to the account. This same number will also be displayed on each of the opportunities related to that account.
For example, if the initial count is 10, and you add a new case through the Cases subpanel on the account, then refresh the account and opportunities pages, it should automatically update to show 11 on both. This is contingent on your instance having the default setting enabled for automatic updates of related fields, with the configuration $sugar_config['disable_related_calc_fields'] = false;
Potential Formula Conditions and Possible Workaround
Next, create a new field called test_field in the Contacts module. By default, this module is related to the Cases module through a many-to-many relationship (with the important detail being that the Contacts side of the relationship is many). Then, add the following formula to the test_field: count($cases).
Now, try relating an additional case to the account and see if the field automatically updates for both the Opportunity and the Account.
If the change no longer propagates to the second-level module (Opportunity) after adding this new formula, it may indicate that the system is trying to detect and prevent a reference loop. In some cases, this detection might occur even when no actual loop or recurrence is happening.
If formulas of this kind are removed from Studio and a Repair and Rebuild is performed, the changes will propagate once again.
To mitigate such situations, consider implementing a BPM workflow triggered by a field change in the second and higher-level modules. This workflow, possibly with a wait or other synchronization mechanism, ensures that records at higher levels are saved, thereby updating the related fields. It's essential to ensure that the BPM process itself does not create loops.
Additional notes
This article discusses important considerations when designing Sugar Logic formulas that use related functions (related, rollups, counts). It aims to ensure that these formulas work as expected by recalculating the fields within a hierarchy of multiple (more than two) modules while avoiding loops and recurrences.
Implementing a BPM workflow can also be beneficial if the hierarchical modules contain cross-reference (direct inter-module) relationships and calculations that can bypass intermediate levels. In this context, BPM can ensure that the latest calculation in the sequence is applied by resaving the record on specific field changes.
The scenario presented can be applied to various stock and custom modules, as well as 1:many and many:many relationship types with Sugar Logic related formulas in Studio.