One of the things I love about Process Builder is that you don’t need to know code to configure a solution that once required Apex. Because Process Builder is built with Flow as the chassis, you can do a lot of cool stuff with it and not know how to write a single line of code.
In the past, I’ve had requirements as an Admin, and just this past week from a client that would have required a trigger. But, thanks to the magic of Process Builder, a developer no longer needs to assist. Let’s review the requirement and see how to solve for it.
Here’s the requirement. For auditing and compliance reasons, and for ease of reporting, the client wanted to be able to track the previous values of a date field (let’s say Close Date) on the Opportunity and easily report on it. Standard field history tracking and reporting didn’t meet the requirement because the standard reporting wasn’t as flexible as the client would like.
To solve for this, I could have used the custom field history tracking which I wrote about a few weeks ago, but that was too complex for the clients needs. So, we went simple.
Simplicity is always important. Never overcomplicate things. The solution was easy. Create a Text Area field that would capture the previous value and keep a list of the previous values in descending order (most recent date on top). This text field could then be reported on for reference.
This used to require a trigger, but Process Builder does this with ease.
Custom Text Area Field
I chose to use a text area field instead of a long text field because the date doesn’t change often and we’re working with dates (10 characters per date) so 225 characters are plenty. However, this solution could also work with a long text field.
Since we’re wanting to track the changes in Close Date, I’ll create the field on the Opportunity and call it Close Date History Hidden to indicate that this is a hidden field (not on the page layout). There’s an additional reason we’ll add the word “Hidden” to the field which we’ll get to later.
Create a Process
Now that the field is created, we can create the Process in Process Builder. This one is really easy. We’ll go into Process Builder, and provide a name and description.
Next, we’ll create the Process criteria. To do so, we start by selecting the object that will be evaluated in this process which, in this example, will be the Opportunity. Be sure to have the Process evaluated when the record is created or edited.
Now that the object is selected, we’ll describe the entry criteria. Under what circumstances should the actions associated with the process be triggered? Since the requirement is to capture the previous date from the Close Date field and add it to the Close Date History Hidden field, our criteria will look to see if Close Date has been changed.
Great! Now, when the Close Date is changed, the process will be triggered, and the associated actions will be executed. Time to setup the action! The action is tricky. Remember, we don’t want to override the value of the long area text field – instead, we want to append. This means retaining the existing values of the text field and adding to it. Kind of tricky.
To accomplish this, we’ll create a formula. The formula will consist of two elements – the current value of the Close Date, and the Prior Value of the Close Date History Hidden field. Here’s what it looks like.
Here’s the formula.
TEXT( [Opportunity].CloseDate ) & BR() &
PRIORVALUE( [Opportunity].Close_Date_History_Hidden__c )
Let’s break it down so we know what we’re looking at (because formulas can be tricky)!
[Opportunity].CloseDate – We want to have the most recent value of the Close Date field on the top of the list when viewing the previous dates. I’ve selected the field using the Insert section of the formula builder to ensure I have the correct field. Because the Close Date History Hidden field is a text field, we need to convert the Close Date into a text value which is why the field is wrapped in the TEXT() function.
[Opportunity].Close_Date_History_Hidden__c – The requirement to append means that we need to know what the previous value of the field we’re appending was. So, I’ve used the Insert function in the formula builder to select the newly created text area field. By wrapping it in the PRIORVALUE() function, we’re able to capture the value of this field and populate it as part of the update to the Opportunity record.
& BR() & – The client wanted to see the date fields in a list with the most recent previous date captured at the top of the list. The && signs allow for the two fields to be concatenated (Close Date and Close Date History Hidden), but the BR() operator inserts a line break in the text field. This fairly new function is inserted between the ampersands so that the return happens between the date fields.
Cool! Now, it’s time to test. Once the Process is active, here’s what the results look like.
Well, that doesn’t look quite right! Something is wrong. (Remember when I mentioned we are going to call this text field “hidden!”) Let’s see why.
The Known Issue & Workaround
Not everything works as planned. I thought that after creating the above formula and activating the Process, it would work perfectly! But, alas, this is another “known issue” with Process Builder and flow. Thankfully, there is a workaround.
First, before showing the workaround, be sure that you check out Salesforce’s Know Issues page. Doing a simple Google search may also result in the answer you’re looking for (as it did with this use case). If the issue is impacting you or your customers, be sure to indicate that and you’ll be alerted when there are updates to the issue.
Click here to view the known issue for this specific use case.
Okay, now for the workaround!
This is actually pretty simple, but it requires creating yet another custom field. However, it is an easy field to create. We’ll create another field, and this one will be called Close Date History. Now you see why we called the first field we created Close Date History Hidden!
However, instead of creating another text area field, Close Date History will be a formula field with an output of Text. The formula is super easy – it’s simply the API name of the Close Date History Hidden field! By referencing the field in a formula, the BR_ENCODED error message disappears and the link breaks are formatted correctly.
One thing to note is that this workaround does not work in Lightning Experience (hence the screenshot from Salesforce Classic above).
Now, we’ll go back and hide the Close Date History Hidden field from view using field level security so that only Salesforce Admins can view the field which will limit confusion.
That’s it! What used to require a developer and some Apex to complete now requires a few minutes and a formula!