topic Re: AND operator in filter widget in ArcGIS Experience Builder Questions

AND operator in filter widget

NicoleDaniels802 — Thu, 01 May 2025 18:41:14 GMT

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

Re: AND operator in filter widget

CodyPatterson — Thu, 01 May 2025 18:47:58 GMT

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

Re: AND operator in filter widget

NicoleDaniels802 — Thu, 01 May 2025 19:02:11 GMT

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.

Re: AND operator in filter widget

CodyPatterson — Thu, 01 May 2025 19:14:51 GMT

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

Re: AND operator in filter widget

NicoleDaniels802 — Thu, 01 May 2025 19:32:13 GMT

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

Re: AND operator in filter widget

NicoleJohnson — Fri, 02 May 2025 13:07:20 GMT

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.

Re: AND operator in filter widget

NicoleDaniels802 — Fri, 02 May 2025 13:07:01 GMT

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.

Re: AND operator in filter widget

NicoleJohnson — Fri, 02 May 2025 13:15:01 GMT

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.

Re: AND operator in filter widget

JeffreyThompson2 — Fri, 02 May 2025 13:51:55 GMT

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

Re: AND operator in filter widget

NicoleDaniels802 — Fri, 02 May 2025 14:09:03 GMT

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