Contact Checkbox Formula - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Paul BlomerthPaul Blomerth 

Contact Checkbox Formula

Hi,

I'm reworking a formula to trigger a checkbox to true based on 3 criteria, and I was wondering if someone can sanity check my logic and let me know if it is going to do what I need it to. Here's the formula:

AND(
No_MQL_Due_to_Email_type__c = FALSE,
TEXT(Nurture_Reason__c) <> "Current Acrolinx User",
ISBLANK(TEXT(Contact_Role__c)),
Most_Recent_Campaign_Date_Time__c) = NOW(),
has_an_Open_MQL__c = FALSE,
pi__score__c >= 50,
OR(
pi__grade__c = "A+",
pi__grade__c = "A",
pi__grade__c = "A-",
pi__grade__c = "B+",
pi__grade__c = "B",
pi__grade__c = "B-",
pi__grade__c = "C+",
pi__grade__c = "C"
),
OR(
MQL_Trump__c = TRUE,
),
OR (
Account.open_Sales_Opps__c >= 1
)
 

In short, there are 3 scenarios where I would like this checkbox to 'true':

Scenario 1-
No MQL Due to email = false
AND
Nurture reason <> 'Current Acrolinx User'
AND
The Contract role field is blank
AND
Most recent campaign date/time = now
AND
there isn't an open MQL 
AND
Pardot score is greater or equal to 50
AND
the pardot grade must be one of the following
A+,A,A-,B+,B,B-,C+,or C

Scenario 2:

MQL Trump Checkbox = TRUE

Scenario 3:

The account's tracker for the number of open sales opps is greater or equal to one.

Is this formula accomodating these 3 separate scenarios?

Thanks in advance,

Paul

Best Answer chosen by Paul Blomerth
Tony TrinhTony Trinh
Hi Paul,

Please try this:
OR(
AND(
No_MQL_Due_to_Email_type__c = FALSE,
TEXT(Nurture_Reason__c) <> "Current Acrolinx User",
ISBLANK(TEXT(Contact_Role__c)),
DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
has_an_Open_MQL__c = FALSE,
pi__score__c >= 50,
OR(
pi__grade__c = "A+",
pi__grade__c = "A",
pi__grade__c = "A-",
pi__grade__c = "B+",
pi__grade__c = "B",
pi__grade__c = "B-",
pi__grade__c = "C+",
pi__grade__c = "C"
)
),
MQL_Trump__c = TRUE,
Account.open_Sales_Opps__c >= 1
)
I wrapped all your scenario 1 lines into an AND function. I removed the other ORs as you were using them improperly. In a nutshell it looks like this:
OR(
Scenario 1,
Scenario 2,
Scenario 3
)
and within Scenario 1 there is:
AND(
condition 1,
condition 2,
condition n...
)
Also if you're checking for a date, I would convert the date/time field to a date value
​​​​​​​DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
It is extremely unlikely, even almost impossible to check if date/time is equal to NOW() because every passing second will make that untrue.

All Answers

Tony TrinhTony Trinh
Hi Paul,

Please try this:
OR(
AND(
No_MQL_Due_to_Email_type__c = FALSE,
TEXT(Nurture_Reason__c) <> "Current Acrolinx User",
ISBLANK(TEXT(Contact_Role__c)),
DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
has_an_Open_MQL__c = FALSE,
pi__score__c >= 50,
OR(
pi__grade__c = "A+",
pi__grade__c = "A",
pi__grade__c = "A-",
pi__grade__c = "B+",
pi__grade__c = "B",
pi__grade__c = "B-",
pi__grade__c = "C+",
pi__grade__c = "C"
)
),
MQL_Trump__c = TRUE,
Account.open_Sales_Opps__c >= 1
)
I wrapped all your scenario 1 lines into an AND function. I removed the other ORs as you were using them improperly. In a nutshell it looks like this:
OR(
Scenario 1,
Scenario 2,
Scenario 3
)
and within Scenario 1 there is:
AND(
condition 1,
condition 2,
condition n...
)
Also if you're checking for a date, I would convert the date/time field to a date value
​​​​​​​DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
It is extremely unlikely, even almost impossible to check if date/time is equal to NOW() because every passing second will make that untrue.
This was selected as the best answer
Paul BlomerthPaul Blomerth

Hi Tony,
 

Thanks for the help with this--much appreciated! One more question: I need to add 2 more qualifiers to the option where the # of open sales ops >=1. if I wanted to add that there was also no open MQL and the contact was just associated with a campaign today, would the below adjustment accomplish that? I just want to make sure my AND ( isn't going to mess with any of the other logic that's layered in there.

Thanks!
Paul

 

OR(
AND(
No_MQL_Due_to_Email_type__c = FALSE,
TEXT(Nurture_Reason__c) <> "Current Acrolinx User",
ISBLANK(TEXT(Contact_Role__c)),
DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
has_an_Open_MQL__c = FALSE,
pi__score__c >= 50,
OR(
pi__grade__c = "A+",
pi__grade__c = "A",
pi__grade__c = "A-",
pi__grade__c = "B+",
pi__grade__c = "B",
pi__grade__c = "B-",
pi__grade__c = "C+",
pi__grade__c = "C"
)
),
MQL_Trump__c = TRUE,
AND (
DATEVALUE(Most_Recent_Campaign_Date_Time__c) = TODAY(),
has_an_Open_MQL__c = FALSE,

Account.open_Sales_Opps__c >= 1
)
)