2

At my org, we have FICO scores for each contact in an account. Some accounts have multiple contacts, while others have one. I am trying to pull the highest contact FICO Credit Score into a custom account field called Account FICO Status. I am brand new to salesforce, so my coding techniques are border line miserable, but I have developed the following:

trigger accountFico on Contact (after update, after insert) {

        Map<Id, Account> parentAccs = new Map<Id, Account>();
        List<Id> listIds = new List<Id>();

        for(Contact childObj : Trigger.new) {
            listIds.add(childObj.accountId);
        }

        parentAccs = new Map<Id, Account> ([Select Id, account_Fico_Status__c, (Select Id, fico_Credit_Score__c from Contact) from Account where Id in :listIds]);

        for(Contact cont : Trigger.new) {    
            Account myParentAcc = parentAccs.get(cont.Account);
            myParentAcc.accountFicoStatus = Max(cont.fico_Credit_Score__c);
        }

        update parentAccs.values();


}
4

Ishwar Mete's answer is close, but continues to have some issues.

I do agree that using SOQL Aggregate Functions is the way to go here. Aggregate Functions:

  • Are provided by Salesforce, so their behavior is well tested and documented
  • Handle null values without issues, save for one edge case (unlike apex)
  • Help make your code more DRY

The following code should work as the body of your trigger:

// My preference for aggregating related record Ids is to use a Set<Id>
//    instead of a List<Id>. This will make it easier to remove nulls.
Set<Id> accountIds = new Set<Id>();
List<Account> accountsToUpdate = new List<Account>();

for(Contact con :Trigger.new){
    accountIds.add(con.AccountId);
}

// It will cause issues later (when we update) if we have a null in this set. 
// This ensures we will not have a null value in the set.
// This can be accomplished in other ways (such as using an `if` block in the loop above), 
//    but I believe this to be the fastest.
accountIds.remove(null);

for(AggregateResult ar :[SELECT AccountId, MAX(fico_Credit_Score__c) maxScore
            FROM Contact
            WHERE AccountId IN :accountIds
            GROUP BY AccountId]){

    // We can set the Id of an sObject if we pass it into that sObject's constructor.
    // This is a common pattern used for updating records of related sObjects,
    //    and saves us from needing to write another query
    accountsToUpdate.add(new Account(
        Id = (Id)ar.get('AccountId'),
        // When fetching an aggregated field, it comes back as an instance of Object.
        // Thus, it's required to cast the result. Number field type = Decimal
        accountFicoStatus = (Decimal)ar.get('maxScore')
    ));
}

update accountsToUpdate;

The difference between our two answers is as follows:

  • In my solution, we largely take the first portion of the code you provided in your question. We gather all of the AccountId in a set, and use that set as the sole condition in the WHERE clause of the query.
    • We can't use Trigger.new in the WHERE clause because that would cause us to only calculate the max FICO score of the contacts being inserted or updated instead of the max FICO score of all contacts under a given Account. Something like getting a local maximum of a mathematical function instead of the absolute maximum.
  • I alias the aggregate function result. Not necessary, but gives more context compared to expr0. Helpful if your query is long or if you use multiple aggregate functions
  • I cast the result of the aggregate function as a Decimal (which corresponds to the Number field type). This should resolve the compilation issue you were having with the corresponding line in Ishwar Mete's answer.

One quirk to watch out for when using SOQL Aggregate Functions is that, the last time I tested this (4 months ago), if none of the records being aggregated have a value in the field that you're trying to aggregate, the result of the aggregation will be null. In other words, the SOQL Aggregate Functions are not completely coalescing nulls.

If your FICO score field on Account has a default value, explicitly setting that field to null will overwrite that default value. If you don't want that to happen (or need to do more math with the aggregated result), you can use the ternary condition operator to manually coalesce a null result.

The following line

accountFicoStatus = (Decimal)ar.get('maxScore')

would become

// syntax is <boolean test> ? <value if test passes> : <value if test fails>
accountFicoStatus = ar.get('maxScore') == null ? 0 : (Decimal)ar.get('maxScore') 
|improve this answer|||||
  • This should resolve the compilation issue you were having with Looks like you left us hanging on your final bullet. – Adrian Larson Jul 11 '16 at 19:11
  • 1
    I suppose that's what I get for trying to divide my attention amongst 5 different – Derek F Jul 11 '16 at 20:04
  • Thank you so much. The local maximum is a perfect description. I was tinkering with it all weekend trying to get an absolute, but I'm not familiar enough with apex yet. I appreciate your time and the help. @DerekF – Conner Jul 11 '16 at 20:30
1

Use of Aggregate function in SOQL will be the best solution for this case,

trigger accountFico on Contact (after update, after insert) {

        List<AggregateResult> contactAggrList = [SELECT AccountId, max(fico_Credit_Score__c) 
                                                 FROM Contact 
                                                 WHERE Id IN :Trigger.New
                                                 AND AccountId <> null
                                                 Group By AccountId];

        List<Account> accountsToUPdate = new List<Account>();

        for(AggregateResult contactAggr : contactAggrList) {
            accountsToUPdate.add(new Account(Id = (Id)contactAggr.get('AccountId'),
                                             accountFicoStatus = (Integer)contactAggr.get('expr0'))); //Assuming fico_Credit_Score__c is Integer if not typecast to that type

        }

        update accountsToUPdate;
}

I have added this code directly into trigger but best practice is to add it into the handler class.

|improve this answer|||||
  • .get() needs a string. You should use .get('AccountId'). – Sebastian Kessel Jul 7 '16 at 18:03
  • This solution may not always return the correct result as is. You'll need to iterate over Trigger.new and build a list or a map of Account Ids, and use that in the WHERE clause instead of the Ids from Trigger.new to guarantee that the query returns the absolute max for all contacts related to a given account. – Derek F Jul 7 '16 at 18:09
  • Thanks so much for the post. I tested that one, but got an unexpected token error on 'where', but I will try the trigger.new out now. Thanks again for the help y'all. – Conner Jul 7 '16 at 18:17
  • @Connner Check it now, I have updated the code and its working – Ishwar Mete Jul 7 '16 at 18:21
  • @IshwarMete Thanks again for all of the help. I am receiving a message saying that the variable "contactList" at line 10 column 43 does not exist. Would I need to define the variable first? – Conner Jul 7 '16 at 18:34

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.