Skip to main content
sarah_johnson
sarah_johnson
Contributor ⭐️⭐️⭐️⭐️
April 5, 2018
Solved

Take action after comparing two date fields

  • April 5, 2018
  • 6 replies
  • 169 views
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!
    Best answer by seth
    Hi Sarah,
    Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u

    6 replies

    seth
    seth
    Expert ⭐️
    April 5, 2018
    Hi Sarah,
    You might be able to get around this because the [i]criteria in the [i]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
    AI Transformation @ Gainsight
      ben
      ben
      Helper ⭐️⭐️⭐️
      April 5, 2018
      I was thinking the same thing Seth. That Field comparison is clutch.
      -Ben
      sarah_johnson
      sarah_johnsonAuthor
      Contributor ⭐️⭐️⭐️⭐️
      April 5, 2018
      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?
      seth
      sethAnswer
      Expert ⭐️
      April 5, 2018
      Hi Sarah,
      Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
      AI Transformation @ Gainsight
        sarah_johnson
        sarah_johnsonAuthor
        Contributor ⭐️⭐️⭐️⭐️
        April 5, 2018
        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. 
        seth
        seth
        Expert ⭐️
        April 5, 2018
        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 🙂
        AI Transformation @ Gainsight
          Terms & ConditionsAccessibility statement