I'm working on a small trigger that will update the Contract object with the sum of the total price on a custom Subscription object is not a child record of the contract object. Contract:Subscription is one:many.

I've created a Contract custom currency field, MRR, (Monthly Recurring Revenue). My objective is to loop through all related Subscription records, obtain the total price for each subscription, aggregate them together and insert this value onto the Contract.

I've chosen to create the trigger on the Subscription object, though I suspect it may be simpler to do so on the Contract object. Here is what I have so far:

I'm trying to match the Subscription.Contract.Id to the Contract Id. Once I have a list of Subscriptions, I will update the Contract record.

What is the simplest approach to accomplish this?

Thank you,

  • Can you clarify a couple of items: Is the Subscription object a child of the Contract object? Are their date fields involved (MRR makes me think that the subscription has a term on it.....)? Do the changes need to happen immediately or is a schedule sufficient (once/twice a day). – techbusinessman Jul 24 '15 at 16:26
  • The Subscription is not a child of the Contract object. Dates are autocalculated, not a part of this trigger. The changes should happen right away. – COBETCKNN17 Jul 31 '15 at 19:33
  • So how are the Contract and Subscription object related to each other? – techbusinessman Jul 31 '15 at 20:46
  • My previous comment was misleading. There is no built-in master-detail relationship between the two. There is a lookup relationship. Each Subscription record has a lookup to the Contract. Many Subscriptions to One Contract. – COBETCKNN17 Aug 1 '15 at 20:36

I would do something similar to the following:

Set<Id> contractIdSet = new Set<Id>();
for(Subscription sub : trigger.new)
List<AggregateResult> contractMRRTotal = [SELECT SUM(TotalPrice), ContractId FROM Subscription WHERE ContractId IN :contractIdSet GROUP BY ContractId];
List<Contract> contractsForUpdate = new List<Contract>();

for(AggregateResult ar : contractMRRTotal)
    contractsForUpdate.add(new Contract(Id=(Id)ar.get('ContractId'),TotalPrice=(Decimal)ar.get('expr0')));
    update contractsForUpdate;
catch(Exception ex)
    //error handling
  • Hi techbusinessman, Thank you for the design. I came up with similar designs, but I'm not able to save the trigger due to the following error: Compile Error: Invalid foreign key relationship: SBQQ__Subscription__c.Contract at line 4 column 30 --- SBQQ__Subscription__c is a managed object, while Contract is the Salesforce standard object. There is a custom lookup on the SBQQ__Subscription__c object looking at the Contract, SBQQ__Contract__c. It seems I cannot code in the relationship successfully. Any ideas on how to solve this? – COBETCKNN17 Aug 6 '15 at 17:57
  • Hi techbusinessman, Thank you for your design. I tried something similar, and I cannot save the trigger. The problem seems to be in identifying the relationship between the Subscription (SBQQ__Subscription__c) and the Contract (Contract). I'm getting this error when I look up to the Contract Id like so: subscription.Contract.id – COBETCKNN17 Aug 6 '15 at 18:01
  • Try sbqq_subscription__r.Contract – techbusinessman Aug 6 '15 at 19:07
  • Also an update to the question with your new code and where the error is would help considerably – techbusinessman Aug 6 '15 at 19:07

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.