Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Denise_Greenb12
Level 7

Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Hey Everyone - Be careful using "Is before" and "in past before" date field operators. Marketo Support has just informed me that it is expected behavior for the date field operators "is before" and "in past before" to qualify records where the date field is empty!

In other words - suppose the person puka@targetrightmarketing.com was never synced to SFDC. This Smart List does not come up empty. You have to add an additional filter for SFDC Created Date is not empty.

pastedImage_0.png

The same thing applies to this example:

pastedImage_2.png

Since puka@targetrightmarketing.com has an empty SFDC Created Date, you'd expect the smart list to turn up empty - but it doesn't. It contains puka@targetrightmarketing.com.

Obviously this isn't my real use case. I'm just using the above as a simplified way to describe the situation. I discovered this behavior when a couple of segments I created having to do with end dates for certain products contained unreasonably large numbers of people. I will be creating an idea around this and will add a link to it to this thread when I do.

Tags (1)
13 REPLIES 13
Grégoire_Miche2
Level 10

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Yes, always combine with an [is not empty] filter.

greg

Denise_Greenb12
Level 7

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Am I the only one who thinks this is a bad design because it's illogical? I mean - if you say to a roomful of people - "raise your hand if you bought a car before December 30th, 2000" - would you expect people who had never purchased cars to raise their hands? There must be people who are using these date operators incorrectly without realizing it.

Grégoire_Miche2
Level 10

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Hi Denise,

I think you are right here! In fact I have been knowing a teaching this as is for a quite some time now, without questioning the design....

Enter the idea, i’ll add it to the Just do it post.

Greg

Denise_Greenb12
Level 7

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Thanks, Greg! I just created the idea. It's here:

Just noticed the typo in the Idea Name! I'd fix it but that doesn't seem to be possible.

Denise

SanfordWhiteman
Level 10 - Community Moderator

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

A closer example might be "Enter a number for the date you last bought a car. Use 0, not blank, if you never bought a car."  And then later converting the number to a date.

The problem is this can't be changed without breaking people's existing logic. It has to be a new feature that is turned off by default.

Denise_Greenb12
Level 7

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Hey Sandy -

I don't see how your example fits the experience of the user interface here. In ordinary language - date is before mm/dd/yyyy means the date is not blank and it was prior to the cited date.

This wouldn't break people's existing logic if their existing logic had the additional filter for "is not empty." It would break existing logic for anyone who used "is before" and knew they were also including "or is empty" by using "is before." I wonder how many examples of this there are out there. I also wonder how many people are just using "is before" (or "in past before") without realizing they are including people for whom the date field is empty.

At the very least - Marketo could comment the operator in the same way that they add this comment to Data Value Changed filters: "Note: Data of this activity type is archived after 90 days." Perhaps I'll add this to my idea post.

SanfordWhiteman
Level 10 - Community Moderator

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

I don't see how your example fits the experience of the user interface here. In ordinary language - date is before mm/dd/yyyy means the date is not blank and it was prior to the cited date.

I was saying that's the effective technical experience. (Although it would be better to have said "Use -1" instead of "Use 0".  An empty date is effectively December 31 of the year 999, i.e. just below the lowest allowed date of January 1, 1000.)

Here's a situation that has more of a real-world echo: instead of asking, "Was your car purchased before January 1, 2000?" ask the reverse: "Was your car not purchased on or after January 1, 2000?" If you don't have a car, you're in a rhetorical bind, but the answer is closer to Yes than No (if those are your only choices). Of course a more robust UI (or better forms from the DMV or IRS, which often have the same frustrating twists) wouldn't put you in such a bind!

It would break existing logic for anyone who used "is before" and knew they were also including "or is empty" by using "is before."

Right, anyone who is using the established behavior of the platform will suddenly be broken! Breaking changes like that are never OK... you can't go back on production features, let alone after years and years. I agree that documenting it properly would be better, alongside a million other things...

Denise_Greenb12
Level 7

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Grégoire Michel​, Sanford Whiteman​ - I modified my idea to just ask Marketo to comment the filter rather than change the logic because of Sandy's note. Please vote for it!

Grégoire_Miche2
Level 10

Re: Be Careful with "Is before" and "In past before" date field operators - they include records where the date field is empty

Hi Denise,

I think that the original idea was good provided that ne new behavior could be activated with a setting in the admin, and this setting would not be activated by default for existing instances. Therefore, the current behavior would remain the same for existing instance and the new one could be activated by admins at will.

-Greg