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