0

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;
}
4
  • 1
    Most efficient way would be use an aggregate function to SUM room count and GROUP BY parent account id. Do this inside a trigger on account. Sorry for brevity -- am on mobile. Good luck! Commented Feb 15, 2016 at 22:13
  • 1
    Hi @samcool, welcome to SFSE! Your question is asking for other people to write code for you, without presenting a real question or issue. SFSE is a Q&A forum where we look to help each other by asking and answering questions that are real problems or errors. Posting requests to write code for you will get closed. If you need assistance learning the Salesforce technical environment, I recommend you look at Getting Started with Apex Triggers.
    – Adrian Larson
    Commented Feb 16, 2016 at 2:21
  • 1
    Also, take a look at declarative-lookup-rollup-summaries by Andrew Fawcett. With this package you can do what you want without writing any code.
    – Adrian Larson
    Commented Feb 16, 2016 at 2:22
  • 1
    @AdrianLarson: I am sorry, but i am new to stackexchange..My intention was not to ask for any code, but to get any tips about the algorithm.. From next time i will be more precise and put out the code in there and ask questions related to it..Thank you for the information
    – samcool
    Commented Feb 16, 2016 at 8:46

2 Answers 2

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.
    }
}
1
  • Please find the trigger that I have written below
    – samcool
    Commented Feb 16, 2016 at 19:04
1

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.

3
  • Code in the trigger body! And no error handling! /gasp Still, +1 but it might be worth mentioning this code may not be production-ready.
    – Adrian Larson
    Commented Feb 16, 2016 at 2:19
  • 1
    @AdrianLarson I know, it really felt weird, but I wasn't going to write an entire framework to answer a question, lol. That said, this code could be "production-okay" assuming there's no validation rules or other jazz. I'd be more worried about the admin two years later that then decides he wants to add a validation rule to the portfolio accounts...
    – sfdcfox
    Commented Feb 16, 2016 at 2:37
  • Yeah I always have the same dilemma when answering trigger questions.
    – Adrian Larson
    Commented Feb 16, 2016 at 2:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .