topic Setting very specific exclusion in Query widget in ArcGIS Online Questions

Setting very specific exclusion in Query widget

SimonCrutchley — Fri, 01 Oct 2021 08:44:22 GMT

Hi there,

I have a very specific request that I'm struggling with. Within my map, I have a layer which contains a PERIOD field, which for complex reasons contains multiple values e.g. 'ROMAN\EARLY MEDIEVAL\LATE MEDIEVAL\MEDIEVAL\POST MEDIEVAL'. Because this is a multiple entry, the only way to search is by using 'CONTAINS'. Unfortunately, whilst someone looking for features of a 'BRONZE AGE' date, who enters 'BRONZE AGE' as their criteria, will be happy to find 'EARLY BRONZE AGE', 'LATE BRONZE AGE' as well as just 'BRONZE AGE', POST MEDIEVAL is a different time period. Therefore, someone looking for 'MEDIEVAL', although happy to find 'LATE MEDIEVAL' as well as just 'MEDIEVAL' will not want 'POST MEDIEVAL'. I've been trying to find a way to exclude 'POST MEDIEVAL', but everything I've tried also excludes 'MEDIEVAL'. I guess what I need is the query 'CONTAINS 'POST MEDIEVAL' AND DOES NOT CONTAIN 'MEDIEVAL\POST MEDIEVAL', but used as an excluder. BTW I know that because of the structure, if both 'MEDIEVAL' and 'POST MEDIEVAL' occur they will always occur as 'MEDIEVAL\POST MEDIEVAL' so that string is searchable.

Does that make sense?

Thanks

Re: Setting very specific exclusion in Query widget

JohannesLindner — Fri, 01 Oct 2021 11:46:14 GMT

CONTAINS 'MEDIEVAL' AND DOES NOT CONTAIN 'POST MEDIEVAL'

Re: Setting very specific exclusion in Query widget

SimonCrutchley — Fri, 01 Oct 2021 12:21:12 GMT

Hi Johannes,

Thanks for that, but how do you actually get this into the query? It only has specific options and I can't see how to put such a combined query into it.

Cheers

Re: Setting very specific exclusion in Query widget

JohannesLindner — Fri, 01 Oct 2021 12:35:55 GMT

Assuming the input mask looks the same in AGOL as it does in Portal (bad Paint edit because my Portal is in German):

Re: Setting very specific exclusion in Query widget

SimonCrutchley — Fri, 01 Oct 2021 13:12:46 GMT

Hi Johannes,

Sorry, I haven't explained clearly enough. This will exclude those examples which are both MEDIEVAL and POST MEDIEVAL, which is not what I need. Unfortunately, I need to have those examples where it is both, but not just POST MEDIEVAL.

Sorry

Re: Setting very specific exclusion in Query widget

JohannesLindner — Fri, 01 Oct 2021 13:57:06 GMT

Aaah, now I get the problem...

Yeah, this is going to be tricky. I tried a few things and couldn't find a good solution, but maybe someone more versed in SQL than me can.

If you're willing/able to edit your field, there would be a simple solution:

Make sure that "MEDIEVAL" always has the slashes at the start and end
- e.g. have the logic that is filling that field insert the slashes at the start and end of the string, too
search for CONTAINS '/MEDIEVAL/'

Re: Setting very specific exclusion in Query widget

SimonCrutchley — Fri, 01 Oct 2021 14:29:17 GMT

Unfortunately I have c200,000 entries, some of which include MEDIEVAL, some POST MEDIEVAL, some both, and some none, so going back to edit the data isn't really an option :(. I'd also need to ask users to search using /MEDIEVAL/ instead of just entering the period, which is tricky.

Thanks for your help in trying.