Salesforce has just launched the Summer’17 release and once again we’ve collected our favorites from the new features and enhancements. And remember, these are Fluido’s favorites and not a comprehensive list so don’t forget to read the release notes for many many more!
Fiddling with numbers and reports is every administrator’s favorite pastime. Reporting takes time to master - you need to know more about what the data in the fields tell you rather than the functionalities of the report builder.
The opportunity process in Salesforce contains functions that you don’t necessarily run across until you report on them. Here are a couple of easy formulas for reporting on sales performance – right out of the box.
Calculating the win rate
Number of opportunities won per number of opportunities closed. These numbers come from the opportunity process where you determine which stages are marked as “Open”, “Closed” or Closed Won”. Note – this means that “Closed” includes BOTH won and lost opportunities.
Calculating Close rate
Number of closed opportunities per total number of opportunities.
CLOSED:SUM / RowCount
Value of opportunities won per value of opportunities closed
This one is a bit trickier because it requires knowledge on how the opportunity standard fields function. After that it becomes relatively simple.
This compares all closed opportunities based on Expected Revenue to Amount. The Expected revenue field is a standard opportunity field that shows what the expected amount on the opportunity is.
1) If an opportunity is “Closed Lost”, the Expected Revenue is 0 Euros. (0% of the amount)
2) It the opportunity is “Closed Won”, the Expected Revenue = Amount (100% of the amount)
3) Amount of course always shows the value of the opportunity regardless of the opportunity status
Written by Pauliina Löytty
Senior Business Consultant, Fluido
Managing account hierarchies, or other object hierarchies, in Apex is not a simple task. First of all you need to read all child accounts from the database starting from the top account down to lowest level of child accounts which requires multiple SOQL-queries. That’s usually fine. But what if you need to read the full account hierarchy for 5, 10 or even 50 top accounts in bulk? It would be nice to achieve this without an outer loop for the top accounts that would yield in 5 times the number of SOQL-queries, right?
The accounts are stored in the database in a hierarchy using parent ids as shown in the example below. Top accounts are A1 and B1. Children of first level are A2, A3 and B2, B3 for top accounts A1 and B1 respectively. Accounts A4, A5, A6, A7 and B4, B5 are all children on second level
First of all I needed to create a way to store hierarchies of accounts. Not only to store the hierarchy itself but also make it fast and efficient for traversing the tree. I came up with a combination of a custom class and a map. The key in the map is the account id for immediate retrieval of data and the value is the custom class containing each hierarchy node (account) and its immediate children (accounts).
The map is defined as follows:
And the custom class as follows. Note that the constructor will create an empty list of children by default:
Accounts in a hierarchy will be stored as nodes with list of children pointing “down”, this is in contradiction to the database where nodes’ parentid points “up”. The node tree will be built on the fly as accounts are read from database one level at a time.
Retrieval of accounts
To read the account hierarchy you would need to first have read all top accounts. Their immediate children can then be read by querying accounts for having its parentid equal its top account id using parentid = top_account.id. To read the full hierarchy we need to repeat this process until we reach the lowest level of child accounts. This could be accomplished by using a while loop surrounding the SOQL query.
But how do we read the full hierarchy of 5, 10 or 50 top accounts at the same time? The answer to that is by querying accounts for having their parentid in the set of their top account ids using parentid IN :top_account_ids.
The source code for the method retrieving the account hierarchies is defined as:
As you can see on line 1 above the method will return a map of all account hierarchy nodes and take a list of top accounts as parameter top_accts. The variable nodes on line 2 is the node tree that will be populated in this method and returned to the caller as the result of this method. The variable parent_acc_ids on line 3 is a set of parent ids that will used during the dive through each level of the hierarchy.
The top accounts are the first to be added in line 6 above as hierarchy nodes and their account ids on line 7 will serve as the first set of ids to use in first iteration on line 10 to get first level of children.
The query above on line 11 gets all children accounts on the level below all the accounts in parent_acc_ids and stores it in a map called subordinate_accounts.
Then the ids of the children are saved in line 18 above as parent_acc_ids to be used in next iteration to get their children accounts.
All retrieved child accounts will then be added as hierarchy nodes in the node tree in line 22 above. But they will also be added as children in their parents children list as stated in line 24 above.
And finally the populated hiearchy node tree is returned to the caller in line 27 above.
Traversing the account node tree
To traverse the node tree top down you start from the top account node and iterate through its children list, and for each child get its node and iterate through its children recursively down until lowest level found. On the way back from recursion all ids of the accounts will be collected into a set.
To find your way from lowest level child account (or from any other level) you start by getting the account node, get its parent id and get that parent account node and repeat that process until you reach the top account node (parentid is null).
Source code for getting and displaying the name of all children accounts of all top accounts is found below:
Senior Salesforce and Force.com Developer and Consultant
Originally published in Peter's blog
Having this in mind I started to think about separating data from the formula logic. After some exercises I came up with a solution where data is stored in custom settings and a formula that reads and evaluates that data. Instead of using CASE() I am using FIND(), MID() and VALUE() functions in the formula. Data is stored in a hierarchy custom setting with as many text fields as needed. In my example I am using two.
All numeric value/language pairs are stored in text fields (Language_Boost__c and Language_Boost_2__c) in the custom setting. The first field also contains the default value. After filling up the first text field in the custom setting record I just continued to put data in another text field and adjusted the formula (string concatenation) to bring in that data as well. So whenever you hit the field limit of 255 characters just add a new field. A numeric field was also added (Language_Boost_Digits__c) that contains the number of digits present in the text fields, in this case 3. If you need bigger numbers just set a bigger value in this field and change the number of digits for each value in the text fields.
The two custom setting fields look like this (I have removed data for readability). First value “000,” is default value, just as the last value in CASE(). Each value/language is made up of “nnn=xxxxx,” where nnn is the numeric value and xxxxx is the language. Default value and all languages must be appended with a “,” but you may use your own defined separator as well:
000,101=Brazilian Portuguese,102=Chinese (Hong Kong),105=English,
The formula looks like this:
For one of our customers I created a custom object to manage the language to score mapping. Then I wrote a trigger on that object that collects all records and creates the custom setting record field values automatically according to the format described above. That brings standard Salesforce GUI interface for managing the custom setting which is just great, and the customer knows for sure how to handle.
This example is just an eye opener for what formulas can deliver. By using concatenation of custom setting text fields the data limit used by formulas just increases a lot since data is not stored in the formula itself, only the reference. And there are many other use cases where you can store data in a smart and innovative format and create simple algorithms for using it.
Please let me know if you come up with something useful. Good luck!
Senior Salesforce and Force.com Developer and Consultant
Originally published in Peter's own blog 11.2.2015
Latest blog posts written by our professionals.
Salesforce Data Model Poster