Question

Setting a Rule Criteria for a Null Date Field

  • 14 March 2016
  • 9 replies
  • 144 views

Userlevel 6
Badge +5
  • Gainsight Employee: Golden Ruler
  • 318 replies
I have come across several times where I need to write a rule to either include or exclude null date fields. However, this doesn't appear to be an option:





Sometimes the field has yet to be populated (ex. Ticket hasn't been closed yet). Or we need to determine what data needs to be fixed.



It would be great to be able to pull null fields for dates in rules.

9 replies

Userlevel 6
Badge +1
Hello Lane - 



You actually can pull null date fields in rules - getting there is just a little bit quirky due to the date range options we provide in the dropdown (adding or subtracting from rule date, first or last day of week/month, etc.).  



Because a NULL date is a hard value, like 10/3/2004, you have to select the "custom" option first, in essence telling Gainsight you're looking for a specific value, not a relative one.



As soon as you do that, the "Check for Null Values" box appears and you're in business! Hope that helps - 

Scott



Userlevel 6
Badge +5
Hi Scott!



Thanks for the quick response. Is it possible to get it to work for "not equal to"? For the rule I'm writing, I need it to only pull fields that have a value. 
Badge
Hi Lane -



To follow-up on Scott's reply and your added question, yes you can also set the criteria to "Not equal to" and then check the Null value box.





Kristin
Userlevel 6
Badge +5
Hi Kristin!



We were able to get this to work partially:



For the rule I'm writing, I need it to only pull fields that have a value. 



In some instances, I'm able to put the filter in the criteria of the action:





But in others, I am not:





The issue in the second example is when the rule runs it throws partial errors because some do not have a "Last_closed" date.
Userlevel 6
Badge +1
Ahhh - misunderstood - sorry! Yes, my first post allows you to find nulls.



To exclude, you basically you have to trick the rules engine another way: set your date as Greater Than "1/1/1900" or some other date that you know covers all possibilities (I just aim for "before computers were invented).  Since this forces the query to hunt for a valid date of some kind, nulls are excluded.



Will that get the job done?
Userlevel 6
Badge +5
No worries! I still get an error on my rule when i set the date to "greater than" "1/1//1900". It says the value is null. 



It looks like it is because of the Action. I can create an action to Create a CTA where the criteria states that last_closed is not null, but if I try to load to customer or usage I cannot set the criteria as last_closed is not null. 
Userlevel 6
Badge +1
OK - now I'm embarrassed - I assumed 1900 would work without checking our specs.



Just walked it back one year at a time, and the earliest date you can enter is 1/1/1970.  So, as long as you won't be missing anything dated earlier, greater than 1/1/1970 should work for you.



And for proof that I checked, screenshot below! :)





Our team needs to set rules on null date values as well.  Would love to see this feature added.  Thank you!
We tried the 1/1/1970 workaround but we were not able to get it to work. We want our rule to exclude date fields with a null value. Voted this one up! Thank you. 

Reply