[SalesForce] Using Count function in SOQL and If condition

I am updating a value on related record based on the count of another record. The condition is say A is lookup to B record. So, if there is only one B record then certain Value of a field say C on B record will be populated to a new field in A record.

Best Answer

There are no conditional statements (such as IF()) in SOQL, and you can't put an aggregate function like COUNT() in the WHERE clause, so you'll need to do at least some of the processing in Apex.

I don't think the Declarative Lookup Rollup Summary Tool is a fit for you in this case, because I don't know if it allows you to restrict the 'rollup' based on a count.

That aside, there are a couple of queries that can accomplish what you want.

First, using aggregate functions:

[SELECT Object_A__c, Field_C__c, COUNT(Id) number_of_B_records FROM Object_B__c GROUP BY Object_A__c, Field_C__c]

Second, using Parent-Child subquery:

[SELECT Id, (SELECT Id, Field_C__c FROM Object_B__r) FROM Object_A__c]

In the end, there's not much of a difference between these two queries. The only appreciable difference is in the Apex to process each query.

For the first query (n.b. You'll need to manually cast the value from ar.get('Field_C__c')):

List<Object_A__c> recordsToUpdate = new List<Object_A__c>();
for(AggregateResult ar :[SELECT Object_A__c, Field_C__c, COUNT(Id) number_of_B_records FROM Object_B__c GROUP BY Object_A__c, Field_C__c]){
    if((Integer)ar.get('number_of_B_records') == 1){
        recordsToUpdate.add(new Object_A__c(
            Id = (Id)ar.get('Object_A__c'),
            Field_To_Update__c = ar.get('Field_C__c')
        );
    }
}

For the second query:

List<Object_A__c> recordsToUpdate = new List<Object_A__c>();
for(Object_A__c a_rec :[SELECT Id, (SELECT Id, Field_C__c FROM Object_B__r) FROM Object_A__c]){
    if(a_rec.Object_B__r.size() == 1){
        recordsToUpdate.add(new Object_A__c(
            Id = a_rec.Id,
            Field_To_Update__c = a_rec.Object_B__r[0].Field_C__c
        );
    }
}

Either one could be used in a trigger for either object, or in a separate Apex class.

If you do use this as a base for a trigger on Object_A__c, you'd want to have this snippet execute Before Update. You'd also want to modify the snippet to make use of Trigger.new or Trigger.newMap to avoid using a DML statement.

Related Topic