How to Update exisitng Child record field value based on other Child Records field value

Question

I am new in SFDC.

I have a requirement where on Parent Object Account there is a Child custom object Account Member which has lookup with Account.

Requirement :

Use Case 1: For Each Account, User should not create more than 4 Account Member Records. On Account Member Object there is a Percentage Field "Calculate %". If User create first Account Member record, Calculate % should be 100. If User create 2nd record then Calculate % should be 50 for 2nd Record and it should update first record to 50%, for 3 rd record it is 33.33 first 2 records should be calculated correctly and for the 4th Record it is 25%, previous 3 records also get calculated. Aim is if Based on record count, Calculate % should be calculated on new as well as on existing records.

Use Case 2: On Existing Account Record, if there are 4 Account Member records whose Calculate % field is 25 on each record, if I change 1st record Calculate % to 50% then other 3 records should be calculated based on remaining 50%.

so for first record, Calculate % is 50, for other 3 records, Calculate % field value should be 50/3. so based on User input on existing records, it should calculate % on existing records also.

Answer 1

If the only purpose of the Calculate field is to limit the quantity, you don't need it. All you really need is a custom number field in Account to hold the quantity value, and use a tool like Rollup Helper to rollup the count of Account Members. You can then use a validation rule on Account Member to prevent save when the quantity field is greater than 4. Check Salesforce Trailhead for tutorials on using validation rules.

Answer 2

This can be done with few roll-up summary fields, some indicator fields and apex triggers. The basic idea is to have

• separate fields for calculated and user provided percentage values (so, we can work out the correct percentage using some formula and code).
• roll up summary fields on the parent object to determine the split on the child records and boolean flag to indicate that child records need to be processed. The responsibility of reevaluating the child records should be with the parent record or its trigger.
• supporting fields to close the loop and solve this problem.

Consider the parent sobject api name as ParentObj1__c & child sobject api name as ChildObject1__c.

Child sobject should have the following 4 fields:

1. RCount__c: Read-only number field (Number(1, 0)) with default value as '1' and should not have any other value or exposed to end-user. Idea is to use this field in the parent roll-up summary to determine the number of child records.
2. OverridePercentage__c: Editable number field (Number(5, 2)) with default value as '-1' and exposed to end user. '-1' value indicates that user doesn't want to override the calculated percentage and any other positive value indicates that user wants to override the percentage value. If you need to then you can indicate a larger negative value as the default instead of -1.
3. BasePercentage__c: Required number field (Number(5, 2)) with default value as '100' and not exposed to end user. This field value is split equally between all the child records (or between the ones that aren't overridden by end user). For example, if the user were to override one child record's OverridePercentage__c value to 15, then the value of this field on the other child records will be 85.
4. CalculatedPercentage__c: Formula field [Formula (Percent) with 2 decimal places] with the following formula: IF( OverridePercentage__c >= 0, OverridePercentage__c/100 , ((BasePercentage__c/ ParentObj1__r.FilteredChildCount__c )/100 )). This field will indicate the final calculated percentage for any child record and same as 'Calculate %' from your question.

Parent sobject should have the following 3 fields (these fields need not be exposed to end user):

1. ChildCount__c: Unfiltered Roll-Up Summary (SUM ChildObject1) field defined as SUM(ChildObject1: RCount). This will indicate the total number of child records.
2. FilteredChildCount__c: Filtered Roll-Up Summary (SUM ChildObject1) field defined as SUM(ChildObject1: RCount) and filter criteria as ChildObject1: OverridePercentage LESS THAN 0. This will indicate the total number of child records on which end user has not specified overridden percentage value. In other words, the number of child records on which the remaining percentage has to be equally split.
3. RecalculateChildRecords__c: Checkbox field with default value 'Unchecked' (or false). Any update on the child records would toggle this field and that should trigger recalculation of base percentage and calculated percentage on the child records.

Screenshot ref for these fields as shown below:

You can write triggers on both the parent and child sobjects to complete the required functionality.

Before you jump into the code samples, note the following:

• This is only a sample code and will definitely require tweaking (in order to NOT break any of the existing functionality in your org). But this works for your requirements specified and will pass some of the high level test cases.
• My focus while working out this approach was only to provide a working solution for your problem, so I've not considered the best coding practices (bulkification, recursion control, meaningful names for fields/ variables etc. to name a few) and you will have to incorporate them. But, I've tried to incorporate some best practices to provide a working solution.
• This code sample is by no means production ready and will require much more thorough testing (esp., check that trigger recursion is not causing any critical bugs).
• If you find any flaw in this approach (other than anything related to the above mentioned points), feel free to let me know. I'll check and let you know if that can be mitigated.
• This is just one approach and there could be other or apps/ tools which could get your work done without much effort.

Parent object apex trigger code sample (refer to the comments for explanation):

trigger ptrigger on ParentObj1__c (after update) {
    if(Trigger.isAfter && Trigger.isUpdate){
        // Control recursion using static flag
        if(!TriggerRecursionCtrl.pObjRecursiveProcessing){
            
            // Fetch set of parent record IDs which have been marked for recalculation of child records
            Set<Id> parentIds = new Set<Id>();
            for(ParentObj1__c pObj: Trigger.new){
                if(pObj.RecalculateChildRecords__c != Trigger.oldMap.get(pObj.Id).RecalculateChildRecords__c 
                   && pObj.RecalculateChildRecords__c == true)
                {
                    parentIds.add(pObj.Id);
                }                
            }
            
            if(parentIds.size() > 0){
                // Create a map of parent records. This will be used for reset of RecalculateChildRecords__c field later
                Map<Id, ParentObj1__c> parentObjMap = 
                    new Map<Id, ParentObj1__c>([Select Id, RecalculateChildRecords__c FROM ParentObj1__c WHERE Id IN : parentIds]);
                
                // Fetch the list of child records on which user has not overridden the percentage value 
                List<ChildObject1__c> childObjs = 
                    [SELECT Id, BasePercentage__c, OverridePercentage__c, ParentObj1__c 
                     FROM ChildObject1__c 
                     WHERE ParentObj1__c IN :parentIds AND OverridePercentage__c < 0];
                
                // Using aggregate SOQL, determine the final overridden percentage value for each parent record
                AggregateResult[] baseOffsetResults = 
                    [SELECT ParentObj1__c, SUM(OverridePercentage__c) BaseOffset 
                     FROM ChildObject1__c 
                     WHERE ParentObj1__c IN :parentIds
                        AND OverridePercentage__c > -1 
                     GROUP BY ParentObj1__c];
                
                // Convert the aggregate query result (so that it can be used later)
                Map<String, Decimal> baseOffsetMap = new Map<String, Decimal>();
                for(AggregateResult aggrRow: baseOffsetResults){
                    baseOffsetMap.put((String)aggrRow.get('ParentObj1__c'), (Decimal)aggrRow.get('BaseOffset'));
                }
                
                // Update the base percentage on the child records (so that formula field will take care of calculcating the actual percentage).
                // Note that this considers only the child records on which user hasn't specified the overrridden value.
                for(ChildObject1__c cObj: childObjs){
                    cObj.BasePercentage__c = 100 - (baseOffsetMap.get(cObj.ParentObj1__c) == null? 0: baseOffsetMap.get(cObj.ParentObj1__c));
                    parentObjMap.get(cObj.ParentObj1__c).RecalculateChildRecords__c = false;
                }
                
                // Toggle the static variable to control recursion
                TriggerRecursionCtrl.pObjRecursiveProcessing = true;
                
                update childObjs;        
                update parentObjMap.values(); // DML to reset RecalculateChildRecords__c            
            }
        }
    }
}

Child object apex trigger code sample (refer to the comments for explanation):

trigger ctrigger on ChildObject1__c (before insert, before update, after insert, after update) {
    // On after insert or update, fetch set of all parent record IDs and use it to query & update the RecalculateChildRecords__c flag on parent object.
    // This should trigger the parent object apex trigger to process all its child records.
    if(Trigger.isAfter){
        if(Trigger.isInsert){
            Set<Id> parentIds = new Set<Id>();
            for(ChildObject1__c cObj: Trigger.new){
                parentIds.add(cObj.ParentObj1__c);
            }
            
            List<ParentObj1__c> parentObjs = [SELECT Id, RecalculateChildRecords__c FROM ParentObj1__c WHERE Id IN :parentIds];
            for(ParentObj1__c pObj: parentObjs){
                pObj.RecalculateChildRecords__c = true;
            }
            update parentObjs;
        }
        // On after update, fetch set of all parent record IDs, only if OverridePercentage__c values has been edited by the user
        if(Trigger.isUpdate){
            Set<Id> parentIds = new Set<Id>();
            for(ChildObject1__c cObj: Trigger.new){
                if(cObj.OverridePercentage__c != Trigger.oldMap.get(cObj.Id).OverridePercentage__c)
                {
                    parentIds.add(cObj.ParentObj1__c);
                }                
            }
            
            List<ParentObj1__c> parentObjs = [SELECT Id, RecalculateChildRecords__c FROM ParentObj1__c WHERE Id IN :parentIds];
            for(ParentObj1__c pObj: parentObjs){
                pObj.RecalculateChildRecords__c = true;
            }
            update parentObjs;          
        }
    }
    
    // On before insert or update, if the user has specified overridden percentage value, then update the same to base percentage value.
    // This will immediately reflect in the calculated percentage formula field value.
    if(Trigger.isBefore){        
        if(Trigger.isInsert){
            for(ChildObject1__c cObj: Trigger.new){
                if(cObj.OverridePercentage__c > -1)
                {
                    cObj.BasePercentage__c = cObj.OverridePercentage__c; 
                }                
            }
        }
        // On after update, fetch set of all parent record IDs, only if OverridePercentage__c values has been edited by the user
        if(Trigger.isUpdate){
            for(ChildObject1__c cObj: Trigger.new){
                if(cObj.OverridePercentage__c != Trigger.oldMap.get(cObj.Id).OverridePercentage__c 
                   && cObj.OverridePercentage__c > -1)
                {
                    cObj.BasePercentage__c = cObj.OverridePercentage__c;
                }                
            }
        }
    }
}

Apex class for trigger recursion control static variables: [Note: Using boolean for recursion control is not really a good practice. Check out the best practice answered by @PhilW here]

public class TriggerRecursionCtrl {
    public static Boolean pObjRecursiveProcessing = false;
}