Setting very specific exclusion in Query widget

813
6
10-01-2021 01:44 AM
SimonCrutchley
Occasional Contributor III

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

Tags (2)
0 Kudos
6 Replies
JohannesLindner
MVP Frequent Contributor

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


Have a great day!
Johannes
0 Kudos
SimonCrutchley
Occasional Contributor III

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

0 Kudos
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_0-1633091717331.png

 


Have a great day!
Johannes
0 Kudos
SimonCrutchley
Occasional Contributor III

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

0 Kudos
JohannesLindner
MVP Frequent Contributor

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/'

Have a great day!
Johannes
0 Kudos
SimonCrutchley
Occasional Contributor III

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.

0 Kudos