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!
Solved
Take action after comparing two date fields
Best answer by seth
Hi Sarah,
Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
+9Hi 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:
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
I was thinking the same thing Seth. That Field comparison is clutch.
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?
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?
+9Hi Sarah,
Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
Here's what I had in mind -- does this help clarify? https://cl.ly/3P2g2e0F0W2u
AI Transformation @ Gainsight
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.
... 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.
+9Glad 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 🙂
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
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.