1

How can I get the sum of three custom number field values from three different custom objects using a certain criteria?

Here are the custom objects with the corresponding fields:

ObjectA__c : Id, Name, chkbox__c, Total_ObjectB__c (Number field), Total_ObjectC__c (Number field), Total__c (Number field)
ObjectB__c : Id, Name, ObjectA__c, Num1__c
ObjectC__c : Id, Name, ObjectA__c, Num2__c

ObjectA__c serves as the parent for both ObjectB__c & ObjectC__c.

The requirement is when chkbox__c is set to false, Total__c will be equal to the sum of all Num1__c values from ObjectB__c + sum of all Num2__c values from ObjectC__c. I don't have any problems with this scenario.

But there's another requirement: when chkbox__c is set to true, Total__c will be equal to the current Total__c value + Num1__c value from new ObjectB__c record + Num2__c value from new ObjectC__c record.

But I am kind of confused on how to do this part when it comes to this scenario.

Can someone show me how to do this?

Meanwhile, here's the apex code I am working at:

public class NewController {

    public static void Sum(Set<Id> ids) {

        List<ObjectA__c> objAQry = new List<ObjectA__c>();
        List<ObjectA__c> objAList = new List<ObjectA__c>();

        objAQry = [SELECT Id, Name, chkbox__c, Total_ObjectB__c, Total_ObjectC__c, Total__c, (SELECT Id, Name, Num1__c FROM ObjectB1__r), (SELECT Id, Name, Num2__c FROM ObjectC1__r) FROM ObjectA__c WHERE ID IN: ids];

        for(ObjectA__c objA : objAQry) {

            Decimal s1 = 0;
            Decimal s2 = 0;
            Decimal s3 = 0;

            if(!objA.chkbox__c) {

                for(ObjectB__c objB : objA.ObjectB1__r) {

                    s1 += objB.Num1__c;

                } 

                for(ObjectC__c objC : objA.ObjectC1__r) {

                    s2 += objC.Num2__c;

                }

                objA.Total_ObjectB__c = s1;
                objA.Total_ObjectC__c = s2;
                s3 = s1 + s2;
                objA.Total__c = s3;
                objAList.add(objA);

            }

        }

        update objAList;
    }             
}   

In related to the apex class above, here are the two apex triggers:

Trigger1:

trigger updateTotal1 on ObjectB__c (after insert, after update, after delete) {

    Set<Id> itemIds = new Set<Id>();

    if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate)) {
        for(ObjectB__c b : Trigger.new) {
            itemIds.add(b.ObjectA__c);
        }
    }

    else if(Trigger.isAfter && Trigger.isDelete) {
        for(ObjectB__c b : Trigger.old) {
            itemIds.add(b.ObjectA__c);
        }
    }

    if(!itemIds.isEmpty()) {
        NewController.Sum(itemIds);
    }

}

Trigger2

trigger updateTotal2 on ObjectC__c (after insert, after update, after delete) {

    Set<Id> itemIds = new Set<Id>();

    if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate)) {
        for(ObjectC__c c : Trigger.new) {
            itemIds.add(c.ObjectA__c);
        }
    }

    else if(Trigger.isAfter && Trigger.isDelete) {
        for(ObjectC__c c : Trigger.old) {
            itemIds.add(c.ObjectA__c);
        }
    }

    if(!itemIds.isEmpty()) {
        NewController.Sum(itemIds);
    }

}

Updated NewController Code:

public class NewController {

    public static void Sum(Set<Id> ids) {

        List<ObjectA__c> objAQry = new List<ObjectA__c>();
        List<ObjectA__c> objAList = new List<ObjectA__c>();

        map<Id,ObjectA__c>idToObjectA = new map<Id,ObjectA__c>([SELECT Id, Name, chkbox__c, Total_ObjectB__c, Total_ObjectC__c, Total__c, (SELECT Id, Name, Num1__c FROM ObjectB1__r, createdDate), (SELECT Id, Name, Num2__c, createdDate FROM ObjectC1__r) FROM ObjectA__c WHERE ID IN: ids OrderBy createdDate]);

        List<ObjectA__c>aRecords = new List<ObjectA__c>();
        List<ObjectB__c>bRecords = new List<ObjectB__c>();
        List<ObjectC__c>cRecords = new List<ObjectC__c>();

        for(Id aId : idToObjectA.keyset()){
           ObjectA__c aRecord = new ObjectA__c();
           aRecord = idToObjectA.get(aId);
           aRecords.add(aRecord);

            Decimal s1 = 0;
            Decimal s2 = 0;
            Decimal s3 = 0;

            if(!objA.chkbox__c) {

               for(Objectb__c b:aRecord.ObjectB1__r){
                   s1 += b.Num1__c;
               }

               for(ObjectC__c c:aRecord.ObjectC1__r){
                    s2 += objC.Num2__c;
                }

                aRecord.Total__c = s1 + s2;
                aRecords.add(aRecord);

            }
            else if(objA.chkbox__c == true){

                List<objectB__c>obj_B_Records = new List<objectB__c>();
                obj_B_Records.addAll(aRecord.ObjectB1__r);
                ObjectBWrapper[] objBRecords = new List<ObjectBWrapper>(); 
                objBRecords.addAll(obj_B_Records);
                objBRecords.sort();
                if(!isNull(objBRecords[0].Num1__c){ 
                   s1 = objBRecords[0].Num1__c;
                }

                List<objectC__c>obj_C_Records = new List<objectC__c>();
                obj_C_Records.addAll(aRecord.ObjectC1__r);
                ObjectCWrapper[] objCRecords = new List<ObjectCWrapper>();

                objCRecords.addAll(obj_C_Records);

                objCRecords.sort();
                if(!isNull(objCRecords[0].Num2__c){ 
                   s2 = objCRecords[0].Num2__c;
                }

                s3 = s1 + s2;

                aRecord.Total__c += s3;
                aRecords.add(aRecord);

            }

        }

        update aRecords;

    }   

}
10
  • @crmprogdev I've just edited the query in my code, my apologies.
    – sfnewbie21
    Dec 23 '16 at 12:51
  • Yes, I am certain of the plural names. I want the plural names like that as opposed to what Salesforce has suggested by default.
    – sfnewbie21
    Dec 23 '16 at 13:23
  • As opposed to what Salesforce has suggested by default? Meaning that when you created the objects, you specified those names for the plurals?
    – crmprogdev
    Dec 23 '16 at 13:24
  • I am referring about the Child Relationship Names specifically, since these can be edited.
    – sfnewbie21
    Dec 23 '16 at 13:29
  • Is this used to process records from a trigger or is it to support a visualforce class? I'm trying to find out where the new Object_c record comes from & whether it's returned in the query.
    – crmprogdev
    Dec 23 '16 at 13:31
0

Since you're using this for multiple triggers, passing trigger.new from your triggers wouldn't work because of the trigger firing only on Object B as opposed to Object C or vice versa when you need data from both. That forces you into needing to find the most recent records for both of those objects.

It seems there might be several ways to solve this problem. One might would be to attempt and use a order by clause, but I'm not entirely confident that would sort by the created date on the subqueries. You'd need to test that with your data to test it first. I don't have a good dataset available right now to test it with. The last would be to use the comparable interface to sort if you returned the createdDate on your records which is what I'm showing as a solution in case the Order by clause doesn't work for you.

Here's your code:

public class NewController {

    public static void Sum(Set<Id> ids) {

        List<ObjectA__c> objAQry = new List<ObjectA__c>();
        List<ObjectA__c> objAList = new List<ObjectA__c>();

        map<Id,ObjectA__c>idToObjectA = new map([SELECT Id, Name, chkbox__c, Total_ObjectB__c, Total_ObjectC__c, Total__c, (SELECT Id, Name, Num1__c FROM ObjectB1__r, createdDate), (SELECT Id, Name, Num2__c, createdDate FROM ObjectC1__r) FROM ObjectA__c WHERE ID IN: ids OrderBy createdDate]);

        list<ObjectA__c>aRecords = new list<Object__c>();
        list<ObjectB__c>bRecords = new list<Object__c>();
        list<ObjectC__c>cRecords = new list<Object__c>();

        for(Id: aId : idToObjectA.keyset()){
           aRecord = idToObjectA.get(aId);
           aRecords.add(aRecord);

            Decimal s1 = 0;
            Decimal s2 = 0;
            Decimal s3 = 0;

            if(!objA.chkbox__c) {
                //sum of all Num1__c values from ObjectB__c + sum of all Num2__c values from ObjectC__c

               for(Objectb__c b:aRecord.ObjectB1__r){
                   s1 += b.Num1__c;

               }

               for(ObjectC__c c:aRecord.ObjectC1__r){
                    s2 += objC.Num2__c;

                }

                aRecord.Total__c = s1 + s2;
                aRecords.add(aRecord);

             }else if(objA.chkbox__c == true){
             // current Total__c value + Num1__c value from new ObjectB__c record + Num2__c value from new ObjectC__c record

                list<objB1__r>obj_B_Records = new list();
                obj_B_Records = aRecord.ObjectB1__r;

                ObjectBWrapper[] objBRecords = new List<ObjectBWrapper>();
                for(objB__c b:obj_B_Records){

                   objBRecords.add(new ObjectBWrapper(b));
                }
                objBRecords.sort();

                if(!isNull(objBRecords[0].objb.Num1__c){ 
                   // this check may not be necessary, but you could get a null pointer without it if Num1__c is null
                   s1 = objBRecords[0].objb.Num1__c;
                }

                //above assumes there can be more than 1 ObjectB__c per ObjectA__c record
                // uses comparable method to sort since the query may not sort the sub queries by created date 
                // could also have created a map, compared to first value added. 
                // Then, If newer value found, replaced existing key & value in map 




                list<objC1__r>obj_C_Records = new list();
                ObjectCWrapper[] objCRecords = new List<ObjectCWrapper>();
                obj_C_Records = aRecord.ObjectC1__r;

                for(objC__c c:obj_C_Records){

                   objCRecords.add(new ObjectCWrapper(c));
                }
                objCRecords.sort();


                if(!isNull(objCRecords[0].objc.Num2__c){ 
                   s2 = objCRecords[0].objc.Num2__c;
                }

                // Same assumptions & comments as for ObjectB 

                s3 = s1 + s2;

                aRecord.Total__c += s3;
                aRecords.add(aRecord);

            }

        }

        update aRecords;

    }   

}

Class used to sort lists of ObjectB Records:

global class ObjectBWrapper implements Comparable {

    public ObjectB__c objb;

    // Constructor
    public ObjectBWrapper(ObjectB__c ob) {
        objb = ob;
    }

    // Compare ObjectB's based on the ObjectB CreatedDate.
    global Integer compareTo(Object compareTo) {

        // Cast argument to ObjectBWrapper
        ObjectBWrapper compareToObjB = (ObjectBWrapper)compareTo;

        // The return value of 0 indicates that both elements are equal.
        Integer returnValue = 0;
        if (objb.CreatedDate > compareToObjB.objb.CreatedDate) {
            // Set return value to a positive value.
            returnValue = -1;
        } else if (objb.CreatedDate < compareToObjB.objb.CreatedDate) {
            // Set return value to a negative value.
            returnValue = 1;
        }
        // Note: > with -1 and < with +1 sorts desc Opposite sorts ascending

        return returnValue;       
    }
}    

Class used to sort lists of ObjectC Records:

global class ObjectCWrapper implements Comparable {

    public ObjectC__c objc;

    // Constructor
    public ObjectCWrapper(ObjectC__c oc) {
        objc = oc;
    }

    // Compare ObjectC's based on the ObjectC CreatedDate.
    global Integer compareTo(Object compareTo) {

        // Cast argument to ObjectCWrapper
        ObjectCWrapper compareToObjC = (ObjectCWrapper)compareTo;

        // The return value of 0 indicates that both elements are equal.
        Integer returnValue = 0;
        if (objc.CreatedDate > compareToObjC.objc.CreatedDate) {
            // Set return value to a positive value.
            returnValue = -1;
        } else if (objc.CreatedDate < compareToObjC.objc.CreatedDate) {
            // Set return value to a negative value.
            returnValue = 1;
        }
        // Note: > with -1 and < with +1 sorts desc Opposite sorts ascending 

        return returnValue;       
    }
}    

You can find more on the sort code and the comparable method above in the Apex documentation here including a test class for a similar one on Opportunity.

10
  • My apologies. That was an error on my part when adjusting the code in the example I referred you to, which I've now fixed for you. The code should compare two CreatedDates not the Amount to the CreatedDate. Try the updated code which should work for you now. Was a lot of code to put together on the fly.
    – crmprogdev
    Dec 26 '16 at 18:00
  • Thanks for that. Classes for both objects are now saving successfully. However, there's another error I am encountering right now from the NewController class, and it displays ` Incompatible argument type List<ObjectB__c> for All method on List<ObjectBWrapper>`
    – sfnewbie21
    Dec 26 '16 at 18:50
  • Same error displays when I tried it on ObjectC__c, and here's the error: Incompatible argument type List<ObjectC__c> for All method on List<ObjectCWrapper>
    – sfnewbie21
    Dec 26 '16 at 18:54
  • Have added lines to cast the plural lists of records to singular lists before calling wrapper. That hopefully will solve your issue. If it doesn't you'll need to create a for loop to cast them individually which is how I've usually done it.
    – crmprogdev
    Dec 26 '16 at 19:21
  • I've made some few modifications on the code, but still same error displays. I've included it below my original post. Can you check it?
    – sfnewbie21
    Dec 27 '16 at 18:08

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.