Creating a rule to pull back values on a field that don't exist

  • 1
  • Question
  • Updated 8 months ago
We have a field for every contact on an account to mark the contact level for that contact which could be 3 options; Recruiter, Manager, or Executive. 

I am trying to create a rule that will create a CTA for an account when there are 0 contacts with an Executive contact level marked. Based on basic filtering options within the rule, I don't see anyway I can do this. Is there any other way I can do this since I am essentially trying to pull back accounts where a certain value for a field does not exist.
Photo of Ky Powell

Ky Powell, Champion

  • 2,594 Points 2k badge 2x thumb

Posted 8 months ago

  • 1
Photo of Mindy


  • 754 Points 500 badge 2x thumb
How about using a pivot task to count the number of Executive contacts?  I have a similar rule when an account has 0 touches by the CSM. 

There is probably a better way to do it, but here's the approach I've taken in the past:
1) pull one data set that has all of the contacts and their contact level
2) do a pivot task to get the total number of executives per account (accounts that have no executives will not show in this table)
3) pull another dataset of all of your accounts
4) merge the pivoted dataset with the all accounts dataset and use the merged data to trigger the CTA (where count of executives = null).

Hope this helps (or that someone else steps in with a better way that I can learn from)
Photo of Karl Rumelhart

Karl Rumelhart, Official Rep

  • 10,342 Points 10k badge 2x thumb
Go Pivot Task! 
This sounds like a great approach.  A slight variant would be to merge with the Account list first.  Then when you do the pivot you can default those with no contacts to zero.  
Photo of Samantha Braastad

Samantha Braastad, Champion

  • 7,780 Points 5k badge 2x thumb
We use a pivot task to perform a similar action with contacts. We pivot the 'Contact Role' field and set the customers health measure based on the number of contacts they have identified in, for example, the Decision Maker role. Customers with no contacts with null values in that field are moved to red.