Take action after comparing two date fields

  • 2
  • Question
  • Updated 3 weeks ago
  • Answered
I need to build a rule that raises a CTA and includes a date comparison in the criteria.  The dates are 2 fields on the Account record, and I need to use the one that is latter for each Account.  e.g. If School Start Date is later than Implementation Start Date, use School Start Date, otherwise use Implementation Start Date.  

I have tried to create a bionic rule and use a formula field to determine the latter date, but there are only 2 Date functions (Date() and DateDiff()) which do not solve this need. 

And the date comparisons in the rule setup and rule action criteria both only allow you to compare a field to a fixed set of options, e.g. Rule Date or X days since Rule Date.  I can't choose another date field on my object/in my data set. 

I cannot find another option, so I thought I'd ask the Community for any ideas!
Photo of Sarah Johnson

Sarah Johnson

  • 478 Points 250 badge 2x thumb

Posted 3 weeks ago

  • 2
Photo of Seth

Seth, Champion

  • 12,642 Points 10k badge 2x thumb
Hi Sarah,
You might be able to get around this because the criteria in the Setup Action step allow you to compare a field against a field.

So, you could have two CTA Actions that generate identical CTAs:
  • Implementation Date is X days ago (or whatever you criteria are)
    AND
    Implementation Date > School Start Date

  • School Start Date is X days ago
    AND
    School Start Date > Implementation Date
Photo of Ben

Ben, Champion

  • 5,772 Points 5k badge 2x thumb
I was thinking the same thing Seth. That Field comparison is clutch.
Photo of Sarah Johnson

Sarah Johnson

  • 478 Points 250 badge 2x thumb
Thanks, Seth.  I am not seeing the same ability to compare a field against another field under Setup Action, so I can't do this part of the condition:

School Start Date > Implementation Date

Both of those fields are showing under Rule Setup, so I can choose them on the left side when setting up the criteria under Setup Action, but they aren't available on the right side.  

Am I missing something?
Photo of Seth

Seth, Champion

  • 12,642 Points 10k badge 2x thumb
Hi Sarah,
Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
Photo of Sarah Johnson

Sarah Johnson

  • 478 Points 250 badge 2x thumb
Oh for pete's sake, YES!  I wasn't changing the dropdown from "Value" to "Field" -- that's why I couldn't pick one of my date fields from the right side.  I was so baffled that this wasn't possible, and it turns out I just wasn't seeing it.

... I still don't want to create 2 rules every time I need this condition, though.  I will need it a lot. 

I think what we'll do is create a rule/rules that compare the dates and store the latter date on Customer Info, that way I can use that 1 value with confidence in all of my CTA rules downstream.  Feels more efficient and scalable.

Thank you SO much. 
Photo of Seth

Seth, Champion

  • 12,642 Points 10k badge 2x thumb
Glad I could help, Sarah!! If you're going to use this comparison a lot, it's probably worth it to create a formula custom field on Account or Customer Info that just does the calculation for you.

Unfortunately it looks (from a bit of Googling) like the formula to take the max of two date fields is a real bear. You use IF statements, and have to account for situations where one or both is null, plus it gets dicier if one is a date and one is a datetime. But, again, maybe worth it if this is a common thing for you :-)