6

I am writing a query that calculate and NPS Score on the Account record. There is an NPS custom object related to the Account object through a master-detail relationship. How do I link the account record to the NPS custom object such that I can do a count etc of those objects which are related to a certain account? Right now this is the base of my trigger, how would I go about accessing the child records? I've tried some SOQL queries but it doesn't seem to work. I'm not sure if there is something specific i have to do for parent-child relationships

trigger CalculateNPS on Account (before update) {    
          for(Account a : trigger.new){
             List<NPS> nps= new List<NPS>();
             nps = [SELECT Id FROM NPS__c WHERE =: a.Id];
              a.NPS_Score_Trigger__c = 2;         
          }
}
  • 5
    Have you looked at roll up summary fields? Are you just trying to count the NPS(custom objects) related to the Account object, if so roll up summaries is the perfect option for you in this situation than going for a trigger. – Rao Jan 29 '14 at 22:53
  • my solution requires a trigger because there are other things I have to do. This is only part of my requirements. But that's where I'm stuck right now. Thanks – user988 Jan 29 '14 at 22:59
  • mind stating the relationship name ? (or the relationship plural label) – Samuel De Rycke Jan 29 '14 at 23:01
  • Customer_Account__c <- This is the Master-Detail field that's linking the NPS Custom object to the Account record – user988 Jan 29 '14 at 23:06
  • "NPS" is the plural name of the object – user988 Jan 29 '14 at 23:12
9

You can use SOQL Aggregate Queries in your Account trigger.

for (AggregateResult ar : [
     Select Count(Id) numRecs, Account__c acctId
     From NPS__c
     Where Account__c In :Trigger.New
     Group By Account__c
]) {
    Id acctId = (Id) ar.get('acctId');
    Account acct = Trigger.newMap.get(acctId);
    acct.NPS_Score_Trigger__c = (Decimal) ar.get('numRecs');
}

This assumes that the API name for Account is Account__c.

  • @peterknolle I'm just using the standard account object so I'd assume this would just be "Account" – user988 Jan 29 '14 at 23:44
  • @user988 - It would be Account__c. All custom fields have the __c suffix. It actually doesn't matter that it is a relationship to the standard Account object. There's more on relationship names here: salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/…. – Peter Knolle Jan 29 '14 at 23:51
  • This worked like a charm. Thanks ! :-) – user988 Jan 31 '14 at 18:20
5

Never have a soql inside a for loop :

trigger CalculateNPS on Account (before update) {    
          for(Account a : trigger.new){
             //List<NPS> nps= new List<NPS>();
             //you do not have to query account related fields inside the account trigger. Use soql only when you have to access related object fields from account.
             //nps = [SELECT Id FROM NPS__c WHERE =: a.Id];
              a.NPS_Score_Trigger__c = 2;         
          }
}

IF THE NPS_SCORE_TRIGGER__C is a text/number field the output when you edit and save the record should be 2 ( you are assigning a defined number to the field).

If you need the actual count of the children NPS associate with the account then

UPDATED:

I did this purely to satisfy my ego. Having maps and query will be bulkier than what peter has answered below. I would recommend using his solution, this is one other way of tackling this issue :)

 trigger CalculateNPS on Account (before update) {     
       map<string,decimal> account_map = new map<string,decimal>();        
    for(Account a :[SELECT Id,(select id from NPS__r) FROM Account WHERE Id IN: trigger.new])
         {
          account_map.put(a.id,a.NPS__r.size());       
         }
for(account a: trigger.new){
if(account_map.containsKey(a.Id){
a.NPS_Score_trigger__c = account_map.get(a.Id);
}
}
      }
  • Thanks. For some reason it's saying "Didn't understand the NPS_child__r part of the query call" etc – user988 Jan 29 '14 at 23:22
  • Since i had no idea of what the plural name of the object was I used nps_child__r . After you added a comment that NPS is the plural name, I updated it as NPS__r( U can access the child from parent using pluralname__r) – Rao Jan 29 '14 at 23:29
  • ooh ok. It's not erroring out anymore but just not populating the output field "NPS_Score_Trigger__c" with anything. I have a feeling I'm really close – user988 Jan 29 '14 at 23:35
  • Are u sure that 1) you are editing and saving an account/ not creating an account 2) you are looking at an account that has a NPS record associated with it? – Rao Jan 29 '14 at 23:40
  • yep i'm editing and the account has 3 NPS records on it right now – user988 Jan 29 '14 at 23:42
0

I think the best ay will be use those Master-Detail relation. Instead of code use Roll-up fields.

If you must have it in code it is better calculate from child to parent. For example when you want just set number of child just put methode in child trigger (after insert, before delete).

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.