Author Topic: Should the "is not null" filter match empty strings, or not? (Read 1282 times)

TwoMice · August 22, 2011, 02:09:34 pm

Today I noticed that CiviReport's "Is Not Null" filter includes empty strings as not null.

I'm guessing that's because, well, empty strings are not null. Of course.

But now I'm looking for a way to filter for only records where a given field actually has a value, something like this: WHERE `my_field` is not null and `my_field` > ''

Is this currently possible? Or, would it be worth trying to change the behavior of this filter? Any thoughts?

- Allen

TwoMice · August 22, 2011, 02:21:39 pm

Just to add a little background here:

We have a client who's created a custom field for participants called "Dietary Restrictions," and they're trying to get their report to show only participants who have a value in this field. "Not null" works fine for contacts who've never had a value in this field, but once they add a value, the field is never null again even after removing the value; so participants in that situation will show up on the report, even though the field is empty.

The client -- naturally, I think -- has a hard time understanding the difference, why the contact isn't in the list before adding the field value, but is in the list after the field value is cleared.

Thanks,
Allen

Donald Lobo · August 23, 2011, 03:20:27 pm

should we rename IS NOT NULL to IS NOT EMPTY?

and then check that the string IS NOT NULL and length(string) > 0?

empty (or something other than NULL) seems a better word than null from a usability perspective. Also calling it something like empty makes it a bit more accurate for the techno-folks among us

lobo

TwoMice · August 23, 2011, 03:41:00 pm

Yeah, I wouldn't expect most users to grok the concept of NULL, and certainly not to distinguish between NULL and empty(). For a user who can't distinguish between the two, the current label of "Is not empty (Null)" seems redundant, and on top of that produces an unexpected result.

So, yes, I'd say it should be relabeled and should be made to treat NULL and "" the same way.

I think one concern here may be that people have already built reports based on the current behavior. But if so, I'd like to hear how they're explaining that to their users.

- Allen

CiviCRM Community Forums (archive)

News:

Author Topic: Should the "is not null" filter match empty strings, or not? (Read 1282 times)

TwoMice

Should the "is not null" filter match empty strings, or not?

TwoMice

Re: Should the "is not null" filter match empty strings, or not?

Donald Lobo

Re: Should the "is not null" filter match empty strings, or not?

TwoMice

Re: Should the "is not null" filter match empty strings, or not?