AND operator in filter widget

[NicoleDaniels802]

I'm trying to use the filter widget on a data table. I want a user to complete all 3 criteria in the filter (first name, date of birth, and last name in order for the table to filter. I've used the below SQL expression. However, if a user enters 1 of the 3 criteria, the table filters. I want to enforce the AND such that all 4 criteria MUST be completed. How do I do this?

SQL Expression:

((First Name = ?) and (Date of Birth = ?)) and (Last Name = ?))

[CodyPatterson]

Hey @NicoleDaniels802 

You're probably looking for something like this here:

(First Name = ? AND ? IS NOT NULL AND ? <> '') 
AND (Date of Birth = ? AND ? IS NOT NULL) 
AND (Last Name = ? AND ? IS NOT NULL AND ? <> '')

? is not null makes sure that there's a value and ? <> '' makes sure that it's not an empty string if that applies.

As long as all of these are separated by AND, each must be true in order to pass.

Cody

[NicoleDaniels802]

Thanks so much, Cody! For simplicity, I tried this with 2/3 fields; however, it's still not forcing the AND.

Here is the updated SQL Statement:  ((First Name = ?) and (First Name ≠ 'NULL') and (First Name ≠ '<>')) and (Date of Birth = ?) and (Date of Birth ≠ 'NULL')

I’m assuming that ‘NULL’ is being treated as a string (which we don’t want) – but I can’t find a way to filter for “is not null” otherwise. I tried “is not blank” and it also does not accomplish what I need it to.

[CodyPatterson]

Hey @NicoleDaniels802 

Hmm that is some strange action, when you say that "is not blank" didn't accomplish what you wanted, I think it may be related to this bug here: BUG-000159713 , would you be able to provide any screenshots of what you're seeing on your side?

Cody

[NicoleDaniels802]

Yup! The actual dataset has some PII so here is a replication with dummy data (DOB loaded in with a timestamp which my actual data doesn't have). Please note that DOB is string in both datasets (dummy and actual). 

[NicoleJohnson]

I think what you're trying to achieve isn't possible right now, but I would definitely kudo an idea if you find one or create one.

I think the statements you're building (with the exception of the "is" ones, where the user can enter information), aren't working because they're only removing certain data (nulls) from consideration. I bet if you put some null First_Name records in your table, then tried to use your filter (with "is not blank"), you'd get those records filtered out, but those statements aren't actually checking whether your user entered anything.

[NicoleDaniels802]

Thanks, NicoleJohnson - that's sort of how I was starting to feel. Any idea if this could be achieved with different widgets (Search or Query instead of Filter or List instead of Table, for example)? Or is this a place where playing with Developer Edition would be appropriate? Just trying to identify some potential next steps.

[NicoleJohnson]

I don't have any experience with Developer Edition, but there is a Tips and Tricks group where you may find more info on that. I also tried the query and list widgets since those also contain filters, and no luck.

[JeffreyThompson2]

When you created the Filter, did you use Add Clause or Add Clause Set? I think Add Clause Set should work for you.

[NicoleDaniels802]

I've tried both and no success with either. Clause Set: