Banyan
  • Home
  • Services
  • Apps
  • Contact
  • Blog

Roll to 1 - Being smart about Roll up Summary fields

10/24/2017

0 Comments

 
Do you have a lot of fields that needs to roll up to the parent object? Running out of roll up summary fields? Here's a simple trick you can use to minimize your consumption of roll up summary fields. 

In this example, I have Quotes and Quote Line Items. I have couple of fields at the Quote Line Item level that I want to roll up to the Quote level.

1. Total Price (which is the list price x quantity with discounts applied) of all line items
2. # of Line items where Type = One Time
3. # of Line items where Type = Recurring
4. Total # of Line items

Typically these are 4 roll up summary fields at the quote level.

In this solution you can see how you can get the same roll up information by using only 1 roll up summary field at the quote level and formula fields.

Step 1: Create a formula field on the Quote line item (Child object)

I'm using a formula number field with 2 decimal places. In that formula I'm constructing a 11 digit figure. 

1. The 1st digit is for count. So whatever the result of the formula is, the first digit should always be 1.
2. If the Type = One Time, the 3rd digit should be 1.
3. If the Type = Recurring, the 5th digit should be 1.
4. Last 6 digits is reserved for the Total price of the line item, that'll be added to this value.

So, if it's a One Time line item with a Total price of 40K, the formula value would be
10100040000.00

If there's a second line items which is of the type Recurring with a Total price of 20K, the formula value would be 10001020000.00
Picture

​Step 2: Create a Roll up summary field at the Quote (Parent)

Roll up (Sum) the formula field created in Step 1 to the parent level. If you roll up the values from the above example, you'll get 20101060000.00 where the,

1st digit = Count of Line Items (2)
2nd & 3rd digits = Count of Line items where Type is One Time (01)
4th & 5th digits = Count of Line items where Type is Recurring (01)
6th digit to end, including decimal places = Sum of Total price of all Line items (60,000)

Step 3: Create formula fields to reflect all the information

Create 4 formula fields, each looking at the value from Step 2 and just taking the information that's required. For the Total Price of all line items, create a formula to just get the last 6 digits from the roll up value.
Picture
You can create that as a currency formula field and use the above formula.
Picture
In this example, the Roll up of Quote line items field in the Roll up summary field from Step 2. 

The Total Price of all line items field is the formula field from Step 3.

Similarly, you can create formula fields for the remaining 3 fields.

Using this approach you can come up with a lot of permutations and combinations for your use cases.
0 Comments

    Author

    Bala Rajagopal - Salesforce enthusiast. Loves helping enterprises overcome complex challenges.

    Archives

    October 2017

    Categories

    All
    Admins
    Developers

    RSS Feed

Home

About

Contact

Copyright © 2022
  • Home
  • Services
  • Apps
  • Contact
  • Blog