0

I have an object (call it "Details") with data that would look like this:

+------------+------------+-----------------------+
| Sequence # | % of total | Cumulative % of Total |
+------------+------------+-----------------------+
|          1 |         20 |                    20 |
|          2 |         25 |                    45 |
|          3 |         30 |                    75 |
|          4 |         15 |                    90 |
|          5 |         10 |                   100 |
+------------+------------+-----------------------+

The users can only edit the "% of Total" column. So for example if I they edit sequence #3 to have a % of total like 25 (denoted by the **below), the results should look like this:

+------------+------------+-----------------------+
| Sequence # | % of total | Cumulative % of Total |
+------------+------------+-----------------------+
|          1 |         20 |                    20 |
|          2 |         25 |                    45 |
|          3 |     **25** |                   *70 |
|          4 |        *15 |                   *85 |
|          5 |        *15 |                   *100|
+------------+------------+-----------------------+

I'm trying to perform the recalculation in an Apex trigger. In this example, the user updated the % of total for row 3 to 25%. The cumulative % of total for Seq. #3 needs to be updated, as do the % of total in rows 4 and 5, with the % of total for rows 4 and 5 being split evenly.

I can get the logic to work in a trigger. The way I have it now, I'm using two triggers: one before update, and one after.

  • The before trigger updates row 3's cumulative % of total.
  • When the after trigger runs, it updates the % of total on the next row, so in this case, row 4.
  • That fires the before update trigger for row 4,which sets its cumulative % of total.
  • The after trigger runs which updates row 5, which fires the before trigger.

The problem is that if there are too many rows, then I exceed the trigger depth. My first question: what's the best way to avoid this?

Or, alternatively, have I approached this completely wrong? I could probably do it all in an after trigger, but I'd probably end up with the same issue.

4
  • For easy handling of data updates, it may be better to handle all updates to records 3, 4, 5 in after-updates & avoid recursive execution of trigger logic using a static variable.
    – Cloud Man
    Sep 21, 2020 at 13:44
  • Do these records have master-detail or lookup relationship with a parent record?
    – arut
    Sep 21, 2020 at 14:32
  • @arut Yes, all the records are children of the same parent. In this case, it's a master-detail relationship.
    – Jeff
    Sep 21, 2020 at 14:59
  • 1
    On a different note, I had answered a similar question few days back. Although the requirements were not entirely similar, you would be able to relate to it (check this: salesforce.stackexchange.com/questions/319220/…). In answer to this question, I've provided the high level approach without getting into code level details. Hope it helps.
    – arut
    Sep 21, 2020 at 15:18

1 Answer 1

4

This approach should work better.

  1. Create a custom field in the parent sobject [let's call it Recalculate, type: Boolean, default value: false].
  2. In the child object's apex trigger, whenever the % of Total value changes (or a new child record is inserted), simply update the boolean field of the parent sobject. [No need to update the sibling records here. Lets handover that to parent sobject trigger]
  3. Create apex trigger on the parent sobject that checks if the boolean field has changed and is now equal to true. If the condition checks out to be true, then query the child records and update the % fields (Sequence # field can aid in identifying which child records are to be updated). Finally, reset the boolean field to false.
  4. Make use of static variable to avoid any recursion in the child and parent sobject triggers, as needed. [Thanks to @PhilW for pointing out the clarification needed here. DO NOT use boolean static variable because that can interfere when trigger executes multiple times for different chunks of data within a single transaction. Check out the link suggested in his comment for best practice.]

By taking this approach,

  • You will not exceed the trigger depth.
  • There will be a clear separation of responsibility between the child and parent sobject apex triggers.
  • In your current set-up, you are probably not considering the scenario of simultaneous insert as well as update. For example, lets say you already have 3 child records and 1 new child record is getting inserted. All of this occurs in single transaction. While update on one record tries to update the others, the new insert is not yet committed to the DB and hence, you cannot locate it efficiently (even if you end up with some workaround). You will run into incorrect distribution of % values or exceed trigger depth.

Update:

Sequence # field can aid in identifying which child records are to be updated.

This should serve two purposes, namely, avoiding any recalculation on records with sequence # that are lesser than the one updated by user and also, track which sequence # records are to be recalculated. This can be accomplished using any of the following (I would prefer the second option because it can be done in before trigger without a need of an additional DML):

  • Store the sequence #s in another additional field in the parent record (to be updated in insert/ update trigger of child sobject)
  • Have an boolean field in the child record (to be updated in insert/ update of child sobject trigger and toggled by the parent sobject trigger).

You should consider additional scenarios here. For example, you have 5 existing child records and the user updates the % of Total value on the records with sequence # 3 & 5. Now, the recalculation should happen on records with sequence # 3, 4 & 5. While the Cumulative % of Total should be updated on all 3 records (seq # 3, 4 & 5), the % of Total should be updated only on 1 record (seq # 4). Slightly different additional scenarios can also be worked out based on this example.

7
  • 1
    I would generally agree with this though the "static variable to avoid recursion" needs to be clarified. Use of a boolean is actually an anti-pattern since it can interfere with scenarios where a trigger is called multiple times because of splitting of a bulk update. There are various ways to address suppressing recursion though I recommend use of a static map of object IDs by operation, or something similar. Take a look at this answer for more detail.
    – Phil W
    Sep 21, 2020 at 15:23
  • Good point @PhilW. I do agree that usage of boolean to avoid recursion is not a best practice. I should have probably clarified that, but thanks for pointing it out. I've updated my answer.
    – arut
    Sep 21, 2020 at 15:33
  • I hadn't thought of the scenario with an insert and update at the same time in a bulk update and I suspect that would have been difficult to track down, so thanks for that. Using the method outlined in the linked answer, with the Map<TriggerOperation, Set<Id>>: in the case of bulk processing, what if record xyz has two entries in the update, the first one sets the value of % of total to 20, and the second updates it to 25. Would the second instance be blocked from running? I would assume so since its ID would be in the Map. In that case wouldn't I want the recursion?
    – Jeff
    Sep 22, 2020 at 15:06
  • At a specific point of time, if there were 2 updates to the same record, trigger context variables would have a single entry for it with the latest update (and not 2 entries) or execute trigger separately for each update. When such action occur from UI, I would guess apex trigger is designed to handle it without duplicate entries. Map & Set collection are based on unique keys and since, the trigger context variable wouldn't have duplicates, you wouldn't run into the problem of having two entries for the same record in the recursion (collection) variable.
    – arut
    Sep 22, 2020 at 17:13
  • 1
    I got it working using the ideas above. I had additional complexity, but worked through those. 1) If you update record 3's % of total, the remaining percentage should be distributed evenly across records 4 & 5, which means the % of total must be updated on those, too. 2) The last record. For example, if you have 5 records, and the cumulative percentage of 1-4 is 80%, record 5 must be at least 20%, so the total equals 100%. I added a field called minimum percentage that is zero on all except the last record and added a validation rule using that field. It's working, many thanks!
    – Jeff
    Sep 23, 2020 at 14:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .