0

I have a trigger on Opp, after each insert/update I need to recalculate a user's sales for the month. I have the user set and I can do an aggregate query, but how do I connect these in the map?

Here is what I have so far, any help would be appreciated!

    //get total current count of opps
oppList = [SELECT Id,Amount FROM Opportunity 
            WHERE CloseDate = THIS_MONTH 
            AND StageName = 'Closed–Won'];
//add opps to set to get comm splits
for(Opportunity o:oppList){
    oppSet.add(o.Id);
}
//get aggregate of all commission splits
csAgg = [SELECT SUM(Split_Amount__c),User__c 
        FROM Commission_Split__c 
        WHERE Opportunity__c IN :oppSet];
//get comm splits attached to this opp
csList = [SELECT Id,Split_Amount__c,User__c,Opportunity__c 
            FROM Commission_Split__c 
            WHERE Opportunity__c IN :oppSet];
//get users
for(Commission_Split__c c:csList){
    userSet.add(c.User__r.Id);
}
//now I have my users in a Set, and aggregate results above, how do I connect?
public Map<Id, AggregateResult> userSales = 
    new Map<Id,AggregateResult>([SELECT SUM(Split_Amount__c) 
                                FROM Commission_Split__c 
                                WHERE User__c IN :userSet)]);
  • You need a map of UserId to OpportunityId. There will be more than one userId to OppId, so you don't want to map in the other direction. Also, start building your code from where it would occur in the trigger using trigger variables for reference in your code. – crmprogdev Jun 2 '18 at 23:22
  • 1
    What's the use case? You could go this route, but I'm wondering whether there's a simpler way of handling it. – Shane Steinfeld Jun 3 '18 at 0:10
  • A roll-up summary with a filter on the owner id of the project totaling the amount seems like it would do the same thing with a lot less code. A code based alternative would be Declarative Rollups for Lookups! – battery.cord Jun 3 '18 at 3:45
  • @ShaneSteinfeld I am trying to update the total $ sales for each rep (user) every time an opp closes. This has many applications, mostly for gamification. Originally I had just wanted to show the total on the page, but now we want to send email alerts when the $ passes a certain amount, so I think saving the value to the user record is the best option. The $ sales are actually stored on a custom object called commission split, which is a child of Opportunity as we can have more than one user per opp. – Jean Grey Jun 3 '18 at 4:25
  • @crmprogdev so would that be a map of UserId and Set<Id>? – Jean Grey Jun 3 '18 at 4:25

Your Answer

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

Browse other questions tagged or ask your own question.