function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jean Grey 10Jean Grey 10 

Map of User Ids and Total Sales this Month

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)]);

 
Best Answer chosen by Jean Grey 10
Narender Singh(Nads)Narender Singh(Nads)
Hi Jean,

Your code will look something like this: 
User[] UserListToUpdate=new user[]{};

for(Id UserID: Usersales.keyset()){
      user u=new user();
      AggregateResult Agg= Usersales,get(userid);
      u.id=UserID;
      u.Splits_this_Month__c=agg.get('samt');
      UserListToUpdate.add(u);
}
Please note that code might vary depending upon your map structure.

Let me know if it helps.
Thanks!

All Answers

v varaprasadv varaprasad
Hi Jean,

Plase check once below sample code:
  
Map<Id,AggregateResult> results = new Map<id,AggregateResult>([SELECT User__c uid,SUM(Split_Amount__c) samt 
            FROM Commission_Split__c 
            WHERE Opportunity__c IN :oppSet]);

system.debug('==results=='+results);


Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.

Thanks
Varaprasad
@For Support: varaprasad4sfdc@gmail.com

 
Narender Singh(Nads)Narender Singh(Nads)
Hi Jean,

Use this code snippet: 
Map<Id,AggregateResult> UserSales= new Map<id,AggregateResult>([SELECT User__c UserId,SUM(Split_Amount__c) samt FROM Commission_Split__c WHERE User__c IN :userSet]);

system.debug('UserSales:  '+UserSales);

Let me know if it helps.

Regards,
Nads
Jean Grey 10Jean Grey 10
I have the map now but how do I update the User on each Commission Split with the aggregate sum? I have a custom field "Splits this Month" that should be updated everytime an opp closes.
Narender Singh(Nads)Narender Singh(Nads)
Hi Jean,

Your code will look something like this: 
User[] UserListToUpdate=new user[]{};

for(Id UserID: Usersales.keyset()){
      user u=new user();
      AggregateResult Agg= Usersales,get(userid);
      u.id=UserID;
      u.Splits_this_Month__c=agg.get('samt');
      UserListToUpdate.add(u);
}
Please note that code might vary depending upon your map structure.

Let me know if it helps.
Thanks!
This was selected as the best answer
Jean Grey 10Jean Grey 10
Thank you, that got me started. See below for actual code snippet after a few adjustments. This is working now.
  
AggregateResult[] sumSales = [SELECT User__r.Id,SUM(Split_Amount__c) 
                                             FROM Commission_Split__c WHERE User__c !=NULL AND 
     Split_Amount__c>0 AND User__c IN :userTriggerSet 
                                             AND Opportunity__r.StageName = 'Closed–Won'
                                             GROUP BY User__r.Id];
	Map<Id,AggregateResult> UserSalesAll = new Map<Id,AggregateResult>(sumSales);
        List<User> UsersToUpdate = new List<User>();
        for(AggregateResult ar: UserSalesAll.values()){
            for(Id userId: UserSalesAll.keyset()){
                User u = new User();
                AggregateResult Agg = UserSalesAll.get(userId);
                u.id = userId;
                object splits = Agg.get('expr0');
                u.All_Time_Sales__c = integer.valueOf(splits);
                UsersToUpdate.add(u);
            }}
            if(UsersToUpdate.size()>0){
                update UsersToUpdate;}