Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
FinneyFinney 

Help Please - Trigger to pull the Invoice Value of all contracts of a User to a field on the Custom object

Hi,

I need help with pulling a field value on all contracts of a user to a custom Object.

The 2 objects here are
1) Contract - Standard object
2) Target - Custom Object.

Contract and Target object are not related to each other i.e. there is no lookup field for the Target object on the Contract Object.

There are 2 fields on the Contract object
a) Invoice Value - Currency
b) Account Manager - Lookup field to the user.

There are 2 fields on the Target object
a) Target Account Manager - Lookup field to the user.
b) Total Invoice Value - Should add up Invoice value of all Contracts under the Target Account Manager's name.

Any help would be greatly appreciated.

Thanks in advance.
Best Answer chosen by Finney
HARSHIL U PARIKHHARSHIL U PARIKH
Hello Finney,

I see the requirements. I have wrote trigger on Contract object. See below,
I am starting it from Contract Object and then going up to User object and then to the Target__c object.
This trigger would most likely work in all conditions such as if you add particulat contract to the User OR delete one OR change/edit one OR UnDelete one.
  
Trigger SummingUpInvoiceValue On Contract(After Insert, After Update, After Delete, After UnDelete){
    
    List<Id> accountManagerIds = New List<Id>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
         For(Contract contr : Trigger.New){
             If(contr.Account_Manager__c != null){
                 accountManagerIds.add(contr.Account_Manager__c);
             }
         }   
    }
    
    If(Trigger.IsDelete){
        For(Contract contr : Trigger.Old){
           If(contr.Account_Manager__c != null){
                 accountManagerIds.add(contr.Account_Manager__c);
             }
        }
    }
    
    Map<ID, Double > UserWithTotalInvoicevalue = New Map<ID, Double>();
         
    AggregateResult[] aggRes =  [Select Account_Manager__c,
                                        Sum(Invoice_Value__c) sumac 
                                        FROM Contract where Account_Manager__c = :accountManagerIds 
                                            Group by Account_Manager__c];
     
     for(AggregateResult EveryAR : aggRes )
     {
         //UserWithTotalInvoicevalue.put(aggRes.Account_Manager__c, aggRes.sumac);
         Id accountManagerId         = (Id)EveryAR.get('Account_Manager__c');
         Decimal TotalInvoiceValue   = (decimal)EveryAR.get('sumac');
         UserWithTotalInvoicevalue.put(accountManagerId ,TotalInvoiceValue);
     }    
     
     List<Target__c> targetFinalListToUpdate = New List<Target__c>();
     
     For(Target__c targ : [Select Id, Target_Account_Manager__c, Total_Invoice_Value__c
                                     FROM Target__c WHERE Target_Account_Manager__c =: accountManagerIds ])
     {
         targ.Total_Invoice_Value__c = UserWithTotalInvoicevalue.get(targ.Target_Account_Manager__c);
         targetFinalListToUpdate.add(targ);
     }
     
     try{
         If(!targetFinalListToUpdate.IsEmpty()){
             update targetFinalListToUpdate;
         }
     }
     catch(Exception e){
         System.Debug('Thrown Exception for SummingUpInvoiceValue Trigger Is:: ' + e.getMessage());
     }                               
                                
}

Hope this helps!

If it solves the query then please mark is as Best Answer!

All Answers

Maharajan CMaharajan C
Hi Finney,

I built the trigger for you and please create this trigger in Target Object just adjust the API name's of Object and fields:

trigger UpdateTargets on Targets__c (before Insert,before update)
{
set<id> AMids=new set<id>();
for(Targets__c tg:trigger.new)
{
AMids.add(tg.Target_Account_Manager__c);
}
Map<Id,decimal> AMTarget=new Map<Id,decimal>();
aggregateResult[] aggRes=[Select Account_Manager__c,Sum(Invoice_Value__c) sumac from Contract where Account_Manager__c = : AMids Group by Account_Manager__c];
for(aggregateResult ar:aggRes)
{
Id AM=(Id)ar.get('Account_Manager__c');
Decimal IV=(decimal)ar.get('sumac');
AMTarget.put(AM,IV);
}
for(Targets__c tg:trigger.new)
{
tg.Total_Invoice_Value__c=AMTarget.get(tg.Target_Account_Manager__c);
}
}

Can you please Let me know if it works or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
​Raj
HARSHIL U PARIKHHARSHIL U PARIKH
Hello Finney,

I see the requirements. I have wrote trigger on Contract object. See below,
I am starting it from Contract Object and then going up to User object and then to the Target__c object.
This trigger would most likely work in all conditions such as if you add particulat contract to the User OR delete one OR change/edit one OR UnDelete one.
  
Trigger SummingUpInvoiceValue On Contract(After Insert, After Update, After Delete, After UnDelete){
    
    List<Id> accountManagerIds = New List<Id>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
         For(Contract contr : Trigger.New){
             If(contr.Account_Manager__c != null){
                 accountManagerIds.add(contr.Account_Manager__c);
             }
         }   
    }
    
    If(Trigger.IsDelete){
        For(Contract contr : Trigger.Old){
           If(contr.Account_Manager__c != null){
                 accountManagerIds.add(contr.Account_Manager__c);
             }
        }
    }
    
    Map<ID, Double > UserWithTotalInvoicevalue = New Map<ID, Double>();
         
    AggregateResult[] aggRes =  [Select Account_Manager__c,
                                        Sum(Invoice_Value__c) sumac 
                                        FROM Contract where Account_Manager__c = :accountManagerIds 
                                            Group by Account_Manager__c];
     
     for(AggregateResult EveryAR : aggRes )
     {
         //UserWithTotalInvoicevalue.put(aggRes.Account_Manager__c, aggRes.sumac);
         Id accountManagerId         = (Id)EveryAR.get('Account_Manager__c');
         Decimal TotalInvoiceValue   = (decimal)EveryAR.get('sumac');
         UserWithTotalInvoicevalue.put(accountManagerId ,TotalInvoiceValue);
     }    
     
     List<Target__c> targetFinalListToUpdate = New List<Target__c>();
     
     For(Target__c targ : [Select Id, Target_Account_Manager__c, Total_Invoice_Value__c
                                     FROM Target__c WHERE Target_Account_Manager__c =: accountManagerIds ])
     {
         targ.Total_Invoice_Value__c = UserWithTotalInvoicevalue.get(targ.Target_Account_Manager__c);
         targetFinalListToUpdate.add(targ);
     }
     
     try{
         If(!targetFinalListToUpdate.IsEmpty()){
             update targetFinalListToUpdate;
         }
     }
     catch(Exception e){
         System.Debug('Thrown Exception for SummingUpInvoiceValue Trigger Is:: ' + e.getMessage());
     }                               
                                
}

Hope this helps!

If it solves the query then please mark is as Best Answer!
This was selected as the best answer
FinneyFinney
Thanks a lot, Maharaja and Govind. Both triggers work, but I'll use the trigger on the contract as its the contract that is inserted and updated.

Appreciate the time taken by you guys to help me!
HARSHIL U PARIKHHARSHIL U PARIKH
Hi Finney,
We are glad we can help you out! Would you mind marking this question solved by hitting Best Answer since it would help other people with similar issus as well?
Thank You Finney, Enjoy!