Formula Fields + Pardot: The Problem and How to Solve It

Share this article...

Salesforce formula fields enable you to display and leverage a great deal of new, untapped information that already exists within your records. Formula fields in Salesforce allow you to present data in the way you want.

Maybe you would like to calculate the number of days between two date fields? Or, use two number fields to calculate a percentage? Of course, you will want to leverage formula fields in Pardot (Marketing Cloud Account Engagement), but, there is a challenge that some marketers may not be clued up on.

This article will use an example to illustrate the problem, and how the use of Salesforce Flow can mean your formula fields can be used in Pardot.

READ MORE: How to Create a Salesforce Formula Field

The Problem

You may not realize that the formula field value that’s presented isn’t actually a value that is stored in the Salesforce database. In other words, it’s not a “hard” value so it can’t be used for triggering other activities, such as a Salesforce Flow, or – in a Pardot context – the connector to sync between Salesforce and Pardot.

Note: If you heard in the Summer ’22 release that you can use formulas as entry criteria for Flows, then I want to clear up any confusion. Yes, you can use formulas within the flow builder as an entry point into the Flow. However, what we’re addressing here is that changes to formula fields on records still won’t be able to trigger a Flow.

Introducing the example

One of my clients had fields for membership start and end dates. If today’s date was between those two dates, then the formula would be presented as an active membership (boolean: true).

The issue that they faced was that the membership field presented in Pardot was not accurate. Unless something on the Salesforce record changed thereby causing the connector sync to run, then prospects were still marked as having an active membership when in reality, it had expired.

Clearly, this was an important metric for them to use in their marketing efforts, so we needed to come up with a solution to have a real field value in their CRM and be able to trigger a sync so that the ‘Active Members’ Dynamic List could be reliable.

The Solution

We decided to create an ‘Active Member’ boolean field (i.e. checkbox) on the contact record and have a Flow use the same formula logic to update it to true or false, depending on whether today’s date meant their membership was valid. This field was then mapped to Pardot, with Salesforce being used as the master for sync behavior.

The update would be seen by Salesforce as a field change that would trigger an update, and therefore, a data sync with Pardot.

Here’s the logic for the Flow:

You will notice that on this occasion, I’ve gone with a schedule-triggered Flow.

In my specific use case, nothing needed to be actioned with the prospect on the day (e.g. send email communication) so we could afford to wait up to 24 hours or longer for their membership to show as “active”.

The reason for this decision was because a record triggered Flow would wait for the record to somehow get updated before the Flow was triggered. If this was the case we could end up with a similar situation as the issue where the record does not get updated. Therefore, the contact would still have an inaccurate active/inactive value.

By scheduling the Flow to run out of hours, we can be confident that when we start our day, the active members database will be accurate.

Flow resource: repurposing the original formula

This is where we make use of the formula that was being used in the formula field to replicate what was already in place:

  • On the left-hand side, click ‘New Resource’.
  • Select ‘Formula’ as the resource type.
  • The screen will expand with more options. For resources, follow a naming convention that explains what the resource is. In this case “formActiveMember” indicates it’s a formula.
  • Select ‘Boolean’ as Data Type (we want it to return a true or false value).
  • Insert the formula you’ve used previously into the formula text box.

The slight difference here is where you would have referred to the field in the formula previously, now you need to use the Record Global Variable before the field. Take a look at my formula below.

({!$Record.Membership_End_Date__c}>TODAY())&&({!$Record.Membership_Start_Date__c}<=TODAY())

Before the fields Membership_End_Date__c and Membership_Start_Date__c, we have now used the !$Record global variable. The formula above basically says “if the end date is greater than today AND the start date is less than or equal to today”.

Then, click ‘Done’.

Decision element

Now we are ready to use this formula in our decision element. This step is pretty simple now we’ve created the formula:

  • Create two outcomes, one for Active and one for Not Active.
  • For the Active path, simply have all conditions being met. Select the formActiveMember resource equal to the Global Constant, True.
  • Do the same for the Not Active path and make sure the formula resource is equal to the Global Constant, False.
  • Click ‘Done’.

Updating the contact record

The final step is to simply update the Active Member checkbox we created back on the contact record either as ticked (Active Path) or unchecked (Not Active Path).

For the Active Path:

  • Give the element a name like “Active Member = True”.
  • Use the first option of “Use the contact $Record global variable”. This ensures we are updating the record that started this Flow.
  • Select the ‘Active_Member__c’ field.
  • Pass the Global Constant, True into the field as a value.
  • Hit ‘Done’.

Repeat this step for the Not Active Path but select the Global Constant, False value instead.

Summary

What I’ve given you here is an example of how I repurposed a formula field and created a Flow to update the record so we can use this within Pardot. I hope this inspired you to think of ways you can use Flows instead of formula fields for these cases. The key things to note…

  • Schedule the Flow if the value could change but the contact record might not be updated.
  • The formula should be the same with the exception of the !$Record. string appended to the beginning of the field name.
  • Make sure that when you map the field to Pardot, Salesforce is the master of sync.

If you’re struggling with how to use formulas and need help in putting together a strategy that works with Pardot then give me a shout, I’d love to help you get the most from Flows.

Add Comment