nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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 = ?))

Vind meer berichten getagd met

Accepted answers

All comments

NicoleDaniels802

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

JeffreyThompson2

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

johnsonn35_MDHHS

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.

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.

johnsonn35_MDHHS

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

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).

Just one criteria filters the table.png

No filter applied.png

Expression Builder.png

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

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

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.