Saturday, May 16, 2015

"The Power of One" in Salesforce Formula Field


Most administrators think formula field is a way to do calculation in the pages such as finding out the final price after deduction, calculate date operation etc. But in Salesforce, formula field can be utilised to do more powerful stuffs. In today's post, I will explain the "The Power of One" in Salesforce Formula Field.

Business Case -
Company "Universal Bank" is using Salesforce's out of the box contact and activity standard object to track the each contact's activities. Now a contact can have multiple activities. So the requirement is to run a report which will tell the total number of distinct contacts having some activities. 

Implementation -
Now if you run a report, you will get something like this below -
Now if you see the report is showing grand total as 12 as we have many contacts having more than one activities. But our requirement is to show total number of distinct contacts having some activities.

And here comes "The Power of One". This will help us to achieve our requirement. 
First we need to create a formula field in Contact object as shown below -

Point to be noted - the value of the formula is field is 1 and that is why the name is "The Power of One". Cool.
Now add this field in the report and do Summarize by this field using Sum option. Picture is shown below.
One done, save and run the same report. Below is what you will get -
Now you can see the report is showing 5 as the right bottom of the report which is showing the number of distinct contacts having some activities.

Awesome. So this trick is always useful when you are dealing with multiple objects in the report and you want a count of some object other than the one the report is originally based on i.e. activities.

4 comments:

  1. Nice write-up, thanks for sharing!

    ReplyDelete
    Replies
    1. Thanks Robert. I am happy that you liked it.

      Delete
  2. Great Article. its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    SalesForce online training

    ReplyDelete