0

I have an requirement to put a validation on the cumulative sum of a field on the child records associated with a parent account related by a lookup relationship.

Let's say: Parent Record: A Child Record : A1, A2, & A3 and have a custom field Order__c.

For, A1, Order__c = 1 A2, Order__c = 5 A3, Order__c = 5,

The cumulative sum of Order__c for A1, A2 & A3 for A should not exceed 10. While saving A3 with Order__c = 5, a validation error messages should be displayed.

Please suggest a solution for this.

2
  • Is this a master-detail relationship? – sfdcfox Mar 18 '18 at 7:49
  • @sfdcfox It is a lookup relationship. – logan Mar 18 '18 at 7:50
1

There's several possible ways to handle this, but unfortunately, no matter how you look at it, code will be involved. Probably the easiest way to go about this would be to install Declarative Lookup Rollup Summaries (DLRS), and then you can get the sum of the records on to the parent, and from there, you can write a validation rule to block the update to the parent.

If DLRS is too much for you, a simple trigger would suffice:

trigger validateLimit on Account (after insert, after update, after undelete) {
  Set<Id> parentIds = new Set<Id>(), invalidParentIds;
  // Aggregate all parent Id values
  for(Account record: Trigger.new) {
    parentIds.add(record.ParentId);
  }
  parentIds.remove(null);
  // Nothing to query?
  if(parentIds.isEmpty()) {
    return;
  }
  // Find parents that would have the limit exceeded
  invalidParentIds = new Map<Id, AggregateResult>(
    [SELECT ParentId Id, SUM(Order__c) sum
     FROM Account
     WHERE ParentId = :parentIds
     GROUP BY ParentId 
     HAVING SUM(Order__c) > 10]).keySet();
  // Display errors
  for(Account record: Trigger.new) {
    if(invalidParentIds.contains(record.ParentId)) {
      record.Order__c.addError('Parent account would exceed allowed Order limit.');
    }
  }
}

Note that this trigger presumes that the limit only applies to the immediate parent and not grandparent records or higher. For that, you'd definitely want DLRS or a custom rollup solution to handle the summation, and a validation rule to force errors.

3
  • getting an error while saving the trigger, Variable does not exist: ar – logan Mar 18 '18 at 8:35
  • @logan Yeah, that was a bit from an earlier version of the code. Updated the answer. – sfdcfox Mar 18 '18 at 8:37
  • thanks for updating the code, it is working. Cheers..!!! :) :) – logan Mar 18 '18 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.