[SalesForce] Check Parent Is Available before accessing in Process Builder Formula

Process builder bombs if you try and access a value on a parent relationship record on a record without a value for the parent relationship. For criteria this is easy to avoid by first checking that the lookup is not equal to null (see Check if lookup field is not null in Process Builder)

But, this doesn't seem to work in formulas. I've got the following

ISPICKVAL([CampaignMember].Status, 'Responded') &&
NOT(ISBLANK([CampaignMember].LeadId)) &&
NOT(ISPICKVAL([CampaignMember].Lead.Status , 'New')) &&
OR(
 CONTAINS(LOWER([CampaignMember].Campaign.Name),'Name1'),
 CONTAINS(LOWER([CampaignMember].Campaign.Name),'Name2'),
 CONTAINS(LOWER([CampaignMember].Campaign.Name),'Name3')
)

If I update a campaign member that's related to a contact (and hence [CampaignMember].Lead is null) I get the following error

The flow failed to access the value for myVariable_current.Lead.Status
because it hasn't been set or assigned.

However, I thought this wouldn't happen since NOT(ISBLANK([CampaignMember].LeadId)) is evaluated before NOT(ISPICKVAL([CampaignMember].Lead.Status , 'New')).

I've tried using ISNULL as well with the same the same result. Now, I'm stuck, what's the proper way to access [CampaignMember].Lead.Status to avoid issues with null lead lookups?

Best Answer

Normally, Process Builder does do short-circuit evaluation on Boolean expressions, so this is an effective way to guard against null references.

I tried to reduce this to a minimal example, and the issue appears to be that the ISPICKVAL() function is evaluated before Boolean short-circuiting.

A Process Builder with this formula-based condition works correctly when inserting a Campaign Member with a blank Lead lookup:

NOT(ISBLANK([CampaignMember].LeadId)) && [CampaignMember].Lead.LastName = "Test"

while this one fails: 

NOT(ISBLANK([CampaignMember].LeadId)) && ISPICKVAL([CampaignMember].Lead.Status, "Test")

The same is true of using the TEXT() formula against Lead.Status; the formula function is evaluated first, resulting in the null exception.

One workaround is to create a Text field Status_Text__c on Lead, whose value is TEXT(Status). Your formula can then evaluate the field without calling a formula function in Process Builder:

NOT(ISBLANK([CampaignMember].LeadId)) && [CampaignMember].Lead.Status_Text__c = "Test"

(I confirmed that this works; this is a wrinkle to Process Builder evaluation I hadn't met before).

Related Topic