Filter Options - similar to SQL "in"

330
9
Jump to solution
05-24-2019 09:49 AM
SaraMoulton1
New Contributor II

I am creating a map in AGO in which I would like to show only 4 types of sitetypes in 7 towns.
(using this dataset:  https://maps.vcgi.vermont.gov/arcgis/rest/services/EGC_services/OPENDATA_VCGI_EMERGENCY_SP_NOCACHE_v...)

In ArcMap, I am able to filter the dataset using this query:
"TOWNNAME" IN ('TOWN1','TOWN2','TOWN3','TOWN4','TOWN5','TOWN6','TOWN7') and "SITETYPE" IN ('SINGLE FAMILY DWELLING', 'SEASONAL HOME', 'MOBILE HOME', 'MULTI-FAMILY DWELLING')


But I am finding it awkward to replicate this in AGO.
Below is a screen shot of how it is starting out, but it is going to be a laborious process to get all the towns and sitetypes accounted for in the filter.  

Is there an easier way to replicate the SQL query in AGO?

0 Kudos
1 Solution

Accepted Solutions
SaraMoulton1
New Contributor II

I went around and around on it for a bit, but I got the combination you were trying to show.  Here is what I ended up with and it is working.

Top of the Edit tab dialog

Middle of the Edit tab dialog

Thank you!

View solution in original post

0 Kudos
9 Replies
Katherine_Clark
MVP Regular Contributor

The link you provided appears to be broken so I wasn't able to look at the dataset. However, I think you could you achieve this in AGO using sets. This will group the criteria.

So under the edit tab, check "add set". For the dropdown of the set, choose "Any of the following expressions in this set are true". This creates "OR" statements. You would have a set for the town name and the housing type. 

Does that achieve what you are looking for? 

0 Kudos
SaraMoulton1
New Contributor II

Link to dataset:  Layer: VT E911 Site Locations (ID: 29) 

I tried using Sets, but didn't get the results I needed.

This try returned no records:

This one returned too many (included data outside of Brattleboro):

I couldn't see how to set up the selection set differently.

0 Kudos
Katherine_Clark
MVP Regular Contributor

The reason the first one you tried didn't return any results is because it looks like it's set up as an "And" statement. You want to make sure that the word "or" shows up in the view. Example:

This is what the edit view looks like for that: 

What does your edit view look like?

SaraMoulton1
New Contributor II

I went around and around on it for a bit, but I got the combination you were trying to show.  Here is what I ended up with and it is working.

Top of the Edit tab dialog

Middle of the Edit tab dialog

Thank you!

0 Kudos
Katherine_Clark
MVP Regular Contributor

Yup, exactly. Glad it worked for you! 

MichaelVolz
Esteemed Contributor

It worked but wouldn't an In statement make it cleaner and simpler (KISS)?

0 Kudos
Katherine_Clark
MVP Regular Contributor

Michael,

If I understood the post correctly, that's what the OP did in ArcMap and it worked fine, but the ArcGIS Online web map filtering system is set up a little differently. Based on the drop down options (is, is not, starts with, ends with, contains, does not contain, is blank, is not blank) I'm not sure how you would implement an IN statement. 

I could be wrong though? I know there is still much that I have to learn. If you know how to do this in ArcGIS Online, that would really be a help to both me and the OP! 

0 Kudos
MichaelVolz
Esteemed Contributor

Katherine:

I was just hoping someone from ESRI would see this thread and implement the use of IN as part of AGOL web map filtering.  If I recall correctly, ArcMap did not always have the IN option available and queries were able to be simplified when ESRI implemented the use of IN for SQL queries.

Katherine_Clark
MVP Regular Contributor

Ah ok, great idea! I completely agree that it would make life so much easier and make logical sense if queries/calculations (both language type and syntax) were the same between ArcGIS Online and ArcMap/Pro. 

Personal pet-peeve: It drives me crazy that we can use Python syntax to calculate fields in ArcMap but we can't in ArcGIS Online!

0 Kudos