Find the sum of a custom field on the related accounts on Accounts

Question

I have a custom field called Total number of rooms on the Account Object.

Now, I have a custom lookup relationship from Account to Account called portfolio

A portfolio will have several accounts associated with it

On the Portfolio Account page, I want to show the sum of the total number of rooms for the related Accounts

Eg: If the portfolio has 2 Accounts, 1 account has 2 rooms and 2nd account has 3 rooms, then the portfolio should show 5 rooms in total.

It should also consider that , if one account is removed from the portfolio, the count should decrease

Below is the trigger that I have written to implement the below code

Sum of Total_Rooms__c works perfectly that is if related accounts are removed from the related list, the value of Total rooms get decreased and if related Accounts are added the value of rooms get incremented

If i remove the Portfolio association from the related account page, the value of Sum of rooms doesnt get updated.

What do I should in this case

trigger AccountRollup on Account (after delete, after insert, after update) {

  //Limit the size of list by using Sets which do not contain duplicate elements
  set<Id> AccountIds = new set<Id>();

  //When adding new Portfolio or updating existing Portfolio
  if(trigger.isInsert || trigger.isUpdate){
    for(Account p : trigger.new){
      AccountIds.add(p.Portfolio__c);
    }
  }

  //When deleting Portfolio
  if(trigger.isDelete){
    for(Account p : trigger.old){
      AccountIds.add(p.Portfolio__c);
    }
  }

  //Map will contain one Portfolio Id to one sum value
  map<Id,Double> AccountMap = new map <Id,Double>();

  //Produce a sum of Total Rooms on Portfolios and add them to the map
  //use group by to have a single Portfolio Id with a single sum value
  for(AggregateResult q : [select Portfolio__c,sum(Total_Rooms__c)
    from Account where Portfolio__c IN :AccountIds group by Portfolio__c]){
      AccountMap.put((Id)q.get('Portfolio__c'),(Double)q.get('expr0'));
  }

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

  //Run the for loop on Accounts using the non-duplicate set of Portfolio Ids
  //Get the sum value from the map and create a list of Accounts to update
  for(Account a : [Select Id, Total_Rooms__c from Account where Id IN :AccountIds]){
    Double RoomSum = AccountMap.get(a.Id);
    a.Total_Rooms__c = RoomSum;
    AccountsToUpdate.add(a);
  }

  update AccountsToUpdate;
}

Answer 1

Since your question was on how to do this in a trigger, here is a sample trigger which would accomplish the counting of rooms. Note that some of this is pseudo code:

public static void afterInsertAndAfterDelete(List<Account> accounts) {
    List<Id> portfolioIds = List<Id>();
    for (Account account : accounts) {
        //does this account have a portfolio?
        if (account.Portfolio__c != null && account.NumberOfRooms__c != null && account.NumberOfRooms__c != 0) {
            portfolioIds.add(account.Portfolio__c);
        }
    }
    if (portfolioIds != && portfolioIds.size() > 0) {
        updateNumberOfRooms(portfolioIds);
    }
}

public static void afterUpdate(List<Account> accounts, Map<Id, Account> oldAccounts) {
    //same as above, but the population of portfolioIds needs to check whether or not
    //the numberOfRooms changed OR the portfolio changed. If the latter, BOTH portfolios
    //should be added.
    Set<Id> portfolioIds = Set<Id>();
    for (Account account : accounts) {
        //does this account have a portfolio?
        if (account.Portfolio__c != oldAccounts.get(account.Id).Portfolio__c) {
            portfolioIds.add(account.Portfolio__c);
            portfolioIds.add(oldAccounts.get(account.Id).Portfolio__c);
        }
        else if (account.NumberOfRooms__c != oldAccounts.get(account.Id).NumberOfRooms__c) {
            portfolioIds.add(account.Portfolio__c);
        }
    }
    if (portfolioIds != && portfolioIds.size() > 0) {
        updateNumberOfRooms(new List<Id>(portfolioIds));
    }
}

private static void updateNumberOfRooms(List<Id> portfolioIds) {
    List<Account> portfolios = [Select Id, (Select NumberOfRooms__c From Portfolio__r) From Account Where Id IN: portfolioIds];
    for (Account portfolio : portfolios) {
        Integer numberOfRooms = 0;
        for (Account account : portfolio.Portfolio__r) {
            numberOfRooms += account.NumberOfRooms__c;
        }
    }

    try {
        update portfolios;
    }
    catch (DmlException ex) {
        //do something about this.
    }
}

Answer 2

At a basic level, you could just perform a general roll up algorithm. Here'd be my approach:

trigger rollUpToParent on Account(before update, after insert, after update, after delete, after undelete) {
    if(Trigger.isBefore) {
        for(Account record: records) {
            record.TotalRooms__c = 0;
        }
        for(AggregateResult result: [SELECT ParentId Id, Rooms__c Rooms FROM Account WHERE ParentId IN :Trigger.new GROUP BY ParentId]) {
            Trigger.newMap.get((Id)result.get('Id')).TotalRooms__c = (Decimal)result.get('Rooms');
        }
        return;
    }
    void getParents(Account[] records, Set<Id> parentIds) {
        if(records != null) {
            for(Account record: records) {
                parentIds.add(record.ParentId);
            }
        }
    }
    Set<Id> parentIds = new Set<Id>();
    getParents(Trigger.old, parentIds);
    getParents(Trigger.new, parentIds);
    parentIds.remove(null);
    Account[] updates = new Account[0];
    for(Id parentId: parentIds) {
        updates.add(new Account(Id=parentId));
    }
    update updates;
}

You'll need to adjust this code for fields, etc, but the basic idea is that you want to update all the portfolio accounts when a child is inserted, updated, deleted, or undeleted, perform a query by the parent to get the new values.