Admin Tools vs Coding: Take your Formulas to the Max
In our last post we talked about how we can take rollup summaries to the next level without the need for development. It’s now the time for formulas to show how far we can go.
A formula field calculates a value from other fields, expressions, or values. As useful as they can be, a lot of Admins usually don’t use a lot of the features associated with formulas. In this post we’ll take a look at some common scenarios where we can use extended features of formulas to provide quite a bit of functionality.
Formulas: The Advanced Admin Way
Scenario 1: Formulas with Criteria for Filtering
An Opportunity is considered high priority when it has Record Type “Donation” or “Contribution”, the Close Date is within 10 days, the Primary Contact is in the Regions A, B or C.
Solution
This criteria may not be complex, but it may need to be repeated in different places. For example: you need to have the list of these records in a report, in a list view in the Opportunity tab, and in a process builder or flow that performs certain actions on them. So instead of creating the criteria repeatedly in several places for different purposes, why not have a formula of type checkbox, which automatically indicates if the record meets all these criteria?
This formula not only avoids repetition, it also more importantly avoids inconsistencies. Many times somebody will forget or not set correctly one of the criteria items somewhere and that’s when you get inconsistencies across reports, list views or records that are processed, which later translates into bigger issues with processes not triggering on time or records left behind because nobody noticed them as they didn’t appear on some of the lists.
Scenario 2: Adding Content with Formulas
An email is sent out when an Opportunity’s stage is Proposal and has not been modified in the last 5 days with the subject “Attention: it’s been 5 days since you last followed up” and another email when an Opportunity’s stage is Proposal and has not been modified in the last 10 days with the subject “Final Notice: it’s been 10 days since you last followed up”. The content of the email is exactly the same, only the subject is different.
Solution
You could create 2 email templates, with their corresponding email alerts and add them to the corresponding process builder, or workflow rules. OR you could actually use formulas to reduce the repetition of email templates and email alerts over minimum content differences.
Create a formula in the Opportunity that based on the Last Modified Date calculates how many days have passed and returns the Subject text you want. We called this field Email Warning Subject (EmailWarningSubject__c) and it has the following definition:
IF(TODAY() - LastModifiedDate = 5, ‘Attention: it’s been 5 days since you last followed up’, ‘Final Notice: it’s been 10 days since you last followed up’)
Then use that field as the Subject in your email template:
This is a great way to customize Email Templates without having to create multiple of them. Formulas can be used as well to customize the content of the email itself. The text does not even need to be in the formula itself. Since formulas can reference Custom Labels, you can create labels (which can have a value with length of up to 1000 characters) and use them in the formulas to customize your content. For the example above the formula for the subject would be reduced to:
IF(TODAY() - LastModifiedDate = 5, $Label.Opp5DaySubject, $Label.Opp10DaySubject)
This can also be used to provide helptexts and notifications on screen in a record’s Page Layouts. In addition, Lightning Record Pages allow you to display a section based on certain values, so you can create very flexible dynamic content that is displayed in particular conditions within them as well. These formulas can also be used to customize the message of Chatter posts sent out by process builders with less decision branches in the process itself to decide what the content of the notification will be, since the decision on the content will be within the formula.
Scenario 3: Formulas with Settings Values
A field indicates if an Opportunity record is within the current Fiscal Year based on its Close Date, but the Fiscal Year is not set in the standard Fiscal Year in Company Settings. The Client does not want to use the Fiscal Year in the Company Settings as they don’t want this to apply or affect their other processes. The Fiscal Year starts on June 1st.
Solution
We could hardcode the Fiscal Year start date and month in a field in the Opportunity itself, but if this is eventually applied to other objects, we may want to store it somewhere where it’s centralized. This way, if the Fiscal Year setting changes, we can change it once in the centralized location and it would propagate to all its references.
The best way to store any centralized data is in either Custom Settings or Custom Metadata Types (CMDT). These are used a lot in development to store settings for a particular application, such as credentials, unique settings for a feature, etc. Since we want to use this in a formula to be able to calculate automatically, Custom Settings have the restriction that they have to be of Hierarchy type in order to be accessed in formula fields. CMDTs do not have that restriction, so we will use CMDT for this implementation.
To set up when the Fiscal Year begins we created a Custom Metadata Type called “Fiscal Year” (Fiscal_Year__mdt) which can be accessed from any formula:
Then we create a record of this CMDT with the Start Date of the Fiscal Year as June 1st:
Now we can proceed with the definition of the formula that based on the Close Date and the Fiscal Year will define whether the record is within the current fiscal year. For this we need to consider 2 cases depending on which time of the year we are currently:
Case 1: If today is within the first half of the fiscal year (from 06/01 thru 12/31), then the Close Date of the Opportunity has to be between 06/01 of this year and 05/31 of next year) for the Opportunity to be within the current Fiscal Year:
(
TODAY() >= DATE(YEAR(TODAY()), $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
&&
DATEVALUE(CloseDate) >= DATE(YEAR(TODAY()), $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
&&
DATEVALUE(CloseDate) < DATE(YEAR(TODAY()) + 1, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
)
Case 2: If today is within the second half of the fiscal year (from 01/01 thru 06/01), then the Close Date of the Opportunity has to be between 06/01 of last year and 05/31 of this year) for the Opportunity to be within the current Fiscal Year:
(
TODAY() < DATE(YEAR(TODAY()), $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
&&
DATEVALUE(CloseDate) < DATE(YEAR(TODAY()), $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
&&
DATEVALUE(CloseDate) >= DATE(YEAR(TODAY()) - 1, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Month__c, $CustomMetadata.Fiscal_Year__mdt.Start_of_Fiscal_Year.Day__c)
)
If you concatenate Cases 1 and 2 with an OR, then you have a formula that calculates whether a record is within the current Fiscal Year based on a custom Fiscal Year established only for this particular feature.
Custom Settings, CMDTs, and Custom Labels can all be used to centralize values that can be reused across your org in formulas, validation rules, workflow rules, process builders, flows, email templates, and much more.
Scenario 4: Filter Criteria with Formulas
Rollup to the Opportunity the last date when a Payment was made within the current Fiscal Year. The current Fiscal Year is as explained for the previous scenario.
Useful for filters in list views, reports, lookup filters, roll-up summary fields, workflow rules, approval processes, process builders, flows, coding.
Solution
Now that we know how to do a formula to know if any record is within the current Fiscal Year, that part is resolved (we created the checkbox formula field “Within Current Fiscal Year formula” - Within_Current_Fiscal_Year_formula__c - in the Payment). Since Payments have a master-detail relationship with Opportunity, then the last Date of a Payment made is a Rollup Summary-type field in the Opportunity.
The issue is that formula fields cannot be used as filter criteria in rollup summaries. So how do we include the “within the current Fiscal Year” request in the rollup summary of the Payments if we cannot use the formula field we created?
We can create an additional checkbox field called “Within Current Fiscal Year” (Within_Current_Fiscal_Year__c) in the Payment object. By default the value of that field is true.
Then create a scheduled flow that runs everyday that if the Within_Current_Fiscal_Year__c field is true, but the Within_Current_Fiscal_Year_formula__c formula field is false, then turns the Within_Current_Fiscal_Year__c field to false. This way the records keep their status within the current fiscal year up-to-date.
Then the rollup summaries can be defined to take the last Date from Payments where the Within_Current_Fiscal_Year__c (the checkbox, not the formula) is true.
So even with this small limitation we were still able to provide the functionality declaratively and reusing the previous solution for the Fiscal Year.
The Takeaway
The ability to access Custom Settings, CMDTs, and Custom Labels via formulas provides a whole set of possibilities to the functionality you can create declaratively. It takes a bit of imagination to find the scenarios in which you can apply these workarounds, but they can save you more time later than the creation and maintenance of more complex solutions to the same problem.
Resources
Here are some resources to learn more:
- Custom Labels
- Create Custom Settings
- Custom Metadata Types
- Custom Metadata Types and Advanced Formula Fields
- Custom Metadata Types and Validation Rule Formulas
Ideas to vote for:
What do you think of the possibilities with formulas? Which one(s) have worked out best for you? Any other workarounds you know of? Tell me all about it in the comments below, in the Salesforce Trailblazer Community, or tweet directly at me @mdigenioarkus. Subscribe to the Arkus newsletter here to get the top posts of the Arkus blog directly to your inbox.
