-1

I am trying to remove duplicate values in a list while adding up the other fields. e.g. enter image description here

my logic is → remove duplicates by (Name + role__c) and add the paid__c and hours__c values to the unique one. Expected result ↓

enter image description here

I have used a java snippet to achieve this logic but I am not getting correct values.

 list<account> accData = [select name, role__c, paid__c, hours__c from account where 
  role__c  != null];
  for (integer i = 0; i < accData.size(); i++) {
   account current = accData.get(i);

    for (integer j = i + 1; j < accData.size(); j++) {
    account compare = accData.get(j);
    system.debug('compare  ' + compare);
     if (current.name + current.role__c == compare.name + current.role__c ) {
     current.paid__c += current.paid__c + compare.paid__c;
      current.hours__c += current.hours__c + compare.hours__c;
     accData.remove(j);
   j--;
  }
}
}  system.debug('accData ' + accData);

any suggestions on this?

3 Answers 3

1

This is called an Aggregate Result query, and it looks like this:

AggregateResult[] results = [
  SELECT Name Name, Role__c Role, SUM(Paid__c) Paid, SUM(Hours__c) Hours
  FROM Account
  WHERE Role__c != NULL
  GROUP BY Name, Role
];

It's always faster to ask the database to do the summation for you than to write it in Apex yourself.

1
  • I like this approach, haven't thought about using AggregateResult! This shoud definelty be a good option as well.
    – Muffinzlol
    Oct 28, 2022 at 9:33
0

Without going into too much detail. You should check out Maps. https://developer.salesforce.com/docs/atlas.en-us.apexref.meta/apexref/apex_methods_system_map.htm

I would assume, this is what you are looking for.

You would probably need something like:

List<Account> accData = [SELECT Name, 
                                Role__c, 
                                Paid__c,
                                Hours__c 
                         FROM Account 
                         WHERE Role__c  != null];

  
Map<String, Integer> accountToPaidSumMap = new Map<String,Integer>();

for(Account account : accData) {
  String mapKeyString = account.name + account.role__c 

  if(accountToRoleSumMap.containsKey(mapKeyString)) {
    Integer currentPaidSum = accountToRoleSumMap.get(mapKeyString) + account.paid__c;
    accountToRoleSumMap.put(mapKeyString, currentPaidSum);
  } else {
    accountToRoleSumMap.put(mapKeyString, account.paid__c);
  }
...

}

You'll have to transform the Map into a List, to update the Data into a Database. As the Map currently has no ID / External ID. As the usecase is not fully clear youl'd have to figure this out by yourself.

3
  • mapKeyString can accidentally collide with other data. It'd be far better to use Map<Account, Integer> as the map data type, and then use a key of Account key = new Schema.Account(Name=Account.Name, Role__c=Account.Role);. Also, I would recommend using Decimal instead of Integer, because you're potentially truncating the fractional units off.
    – sfdcfox
    Oct 27, 2022 at 18:07
  • Makes sense to optimize it ;)
    – Muffinzlol
    Oct 28, 2022 at 9:32
  • Aggregate query helped with the requirement. i also used your version to sum them up, which worked fine.
    – kkJhn
    Oct 31, 2022 at 18:03
0

Here is the last version that I have for scenarios where you have formula fields and can't use Aggregate.

public class testTotal {
    public static void make(){
        list<Account> recs = [SELECT Name, Role__c, Paid__c, Hours__c  FROM Account WHERE Role__c != NULL];
        map<string, wrapper> myMap = new map<string, wrapper>();
        for(Account a : recs){
            string uniqueKey = a.Name + a.Role__c;
            if(myMap.containsKey(uniqueKey)){
                myMap.get(uniqueKey).hours += a.Hours__c;
                myMap.get(uniqueKey).paid += a.Paid__c;
            } else {
                myMap.put(uniqueKey, new wrapper(a.Hours__c, a.paid__c));
            }
        }
        system.debug('MYMAP ' + myMap);
    }

    public class wrapper {
        public decimal Paid;
        public decimal hours;
        wrapper(decimal hours, decimal paid){
            this.paid = paid;
            this.hours = hours;
        }
    }
}

You must log in to answer this question.

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