Trying to write a trigger to count records in a related list, then update a custom field with that count. Here's what I have so far:

trigger CountOpptys on Account (before insert, before update) {

for (Account a: Trigger.new) {
    List<Account> opptys = [Select id, (select id from LU_Relationship__r) from Account where id=: a.id];
    a.Custom_Field__c = LU_Relationship__r.size();
    // also tried
    //a.Custom_Field__c = a.LU_Relationship__r.size();
    //a.Custom_Field__c = opptys.LU_Relationship__r.size();

I did that query in the developer console (replacing a.id with the id of an Account that has some records in the related list I'm querying) and was able to see the data I wanted but I can't figure out how to get to it from my list/query.


Your code should be referencing opptys[0].LR_Relationship__r.size(). However, you shouldn't query in a loop, so you may want to instead query at the beginning:

trigger CountOpptys on Account (before update) {
    Map<Id, Account> accounts = new Map<Id, Account>(
       [Select id, (select id from LU_Relationship__r) from Account where id IN :Trigger.new]
    for (Account a: Trigger.new) {
        Account fromQuery = accounts.get(a.Id);
        a.Custom_Field__c = fromQuery.LU_Relationship__r.size();

However, this won't work before insert, because nothing could possibly be related to the records since they don't have ID values. Simply assign the value 0 if you want to use before insert.

However, even that's probably a bad idea. If all you need is the size, simply perform an aggregate query:

Map<Id, AggregateResult> relationships = new Map<Id, AggregateResult>(
    [SELECT Account__c Id, COUNT(Id) ctr FROM LU_Relationship__c
     WHERE Account__c IN :Trigger.new]

for(Account record: Trigger.new) {
    if(relationships.containsKey(record.Id)) {
        record.Custom_Field__c = (Decimal)relationships.get(record.Id).get('ctr');
    } else {
        record.Custom_Field__c = 0;

You are not referencing the Account. Use a.LU_Relationships__r.size() instead of just LU_Relationships__r.size(). You may also want to add a null check:

Integer relationships = a.LU_Relationships__r == null ? 0 : a.LU_Relationships__r.size();
  • 1
    The null check is not necessary. A subquery will never be null. – sfdcfox Oct 30 '15 at 21:06
  • That is not what I have observed. I am fairly certain I have had subquery lists come back as null instead of empty. – Adrian Larson Oct 30 '15 at 21:40
  • I've tested this extensively. I'd love to see a query that results in a null sub-query row; I've never gotten one. Also, try this sometime: Contact c = [SELECT Account.Name FROM Contact WHERE AccountId = NULL LIMIT 1]; System.debug(c.Account); System.debug(c.Account.Name); The special part here is that even though c.Account is null, debugging c.Account.Name won't generate a NullPointerException. Queries are kind of magical. The only thing you have to watch out for is c.Account.name.length() (etc, because it will still NPE. – sfdcfox Oct 30 '15 at 22:22

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.