Andy in the Cloud

From BBC Basic to Force.com and beyond…


4 Comments

Account Hierarchy Rollups #ClicksNotCode

clicksnotcodeinthesunThe Declarative Lookup Rollup Summary Tool is holding up to quite an onslaught of requirements at the moment. When this one came in, i thought it may have met its match! However it has taken just less than a day in the Spanish sunshine to get my head round it and work with the poster to find a solution!

Use Case: Rollup a Count of Contacts to the related Account (which is pretty straight forward rollup for the tool), but then within the Account hierarchy rollup a Sum of Account Contacts at each level, so each Account shows the total Contacts for itself and all its child Accounts. The Number of Contacts (Inclusive) field shown below shows the Contact count rolled up at each level in the Account hierarchy. Burlington Textiles is the root account in this case.

accountrollups
accounthierarchy

Having realised it is a solution to the general requirement for Account Hierarchy Rollups (or in fact any Custom Object hierarchy type relationship) i thought i would share how it was eventually achieved in true “clicks not code” style! Note that while we are rolling up Contacts per Account here, this could effectively be any numeric field on Account.

First you will need to install the Declarative Lookup Rollup Summary tool from the latest package install link here. Then in the case of Account create the following Custom Field‘s.

accountfields

  • Number of Contacts,
    Results of a Count rollup of the number of Contacts associated with an Account.
  • Number of Child Contacts,
    Results of a Sum rollup of the Number of Contacts on each child Account to the parent Account.
  • Number of Contacts (inclusive),
    Formula field that adds the above two fields together and is actually used as the Field to Aggregate when calculating the Number of Child Contacts rollup.
    numberofcontactsformula

To populate these fields we are actually using two rollups, one to count the Contacts on each Account, then another to Sum the total Contacts on each child Account to each parent Account, any numeric fields on the Accounts will work the same way. Note that both these rollups are done Realtime and that the second rollup is using the Relationship Criteria Fields feature to ensure a change made by the first rollup triggers a recalculation of the second.

rollupaccountcontacts

rollupnumberofchildcontacts

Two rollups have been used here, only because the information we wanted to rollup in the Account hierarchy was not readily available on the Account record itself. So is a more complex setup than it needs to be if your just for example rolling up an existing Account field, such as Annual Revenue. However it does show nicely how two rollups can be used and have one trigger the recalculation of the other.

Scheduled Rollups: I have yet to try this with Scheduled mode, i suspect there maybe some dependency issues in the processing of two rollups in the correct order (something the tool can be taught about in the future if this is proven to be an issue), however a single independent rollup will work just fine in Scheduled or Realtime mode.