Sorting data by a predefined list

[SimonCrutchley]

Some of you have, in the past, very helpfully provided me with guidance, and indeed scripts for sorting data from ‘/’ separated strings into individual entries and also sorting and removing duplicates from these strings. This works fine for my ‘Type’ data which can happily be sorted alphabetically. Unfortunately, the same cannot be said for my ‘Period’ data, which needs to be in date order. Unfortunately, again, it’s not actual dates, which could be sorted numerically, but rather periods written as text e.g. Roman, Iron Age, Medieval.

I tried to rename the data, adding a number at the beginning, but I hit a snag in that there are entries with duplicate words e.g. MEDIEVAL, POST MEDIEVAL, IRON AGE, EARLY IRON AGE etc and it’s tricky to edit the string for MEDIEVAL, without also editing POST MEDIEVAL; instead you have to edit everything MEDIEVAL, then all the POST or other MEDIEVALs, which is a hassle.

I’m wondering whether there is a way of using some sort of predefined list to create the order?

Thanks

[Robert_LeClair]

One workflow I can think of is you have two attribute fields, one call EPOCH or PERIOD where you have attribute values of MEDIEVAL, POST MEDIEVAL, IRON AGE, EARLY IRON AGE  and so.  Then another attribute field (short integer) that you field calculate where MEDIEVAL = 1 and POST MEDIEVAL = 2 and so on.  If you want the attribute table to be permanently sorted by PERIOD, then use the Sort (Data Management Tools) | ArcGIS Pro documentation to sort by attribute.  Is this what you're attempting to accomplish or am I off?

[SimonCrutchley]

Hi Robert,

Sort of. My issue is that the querying in AGOL, using the data from Pro has to be done by text; I can't expect users to know that Neolithic = 3 and Post Medieval = 17 etc. Furthermore, as well as users to being able to query by text, I also need to display the concatenated data e.g. IRON AGE\ROMAN for those just viewing.

I'm afraid I don't understand enough of what you've suggested to know whether I could this?

Sorry

[Robert_LeClair]

I know in ArcGIS Experience Builder, you can create a predefined unique values in a specific order for the Query widget and Select widgets.  I'm pretty sure the ArcGIS Online Map Viewer doesn't support a predefined or custom order list the way ExB does...

[SimonCrutchley]

I've built my app in ExB so that shouldn't be a problem. The issue is that the query has to be a 'contains' rather than an 'equals' or a 'select from a picklist' due to the variety of combinations e.g. IRON AGE\ROMAN; IRON AGE\LATE PREHISTORIC\ROMAN; IRON AGE\ROMAN\MEDIEVAL etc

[Robert_LeClair]

GREAT!  So in my screen grab below, I have a Query widget where I created a picklist of combinations that you provided (and I'm guessing for order) where the user would pick the value of interest, then click Apply to complete the query.  Are we getting close?

[SimonCrutchley]

Sadly not :(. Because of the variety of combinations, there are literally hundreds of possible options, so a picklist isn't viable.

[Robert_LeClair]

Hmmm...I did get the CONTAINS working on the text field.  The key is creating the perhaps LONG list of combinations you mentioned but this is just picking on the initial creation.

[MErikReedAugusta]

I'll readily admit it's been a while since I've stuck my nose in Experience Builder, so what I'm about to describe might not actually be possible.

But it sounds to me like what's needed here is a way to filter first the list of all possible items (rather than all possible combinations of items) in the options, and then a way to select from that list, and then a way to run a CONTAINS filter against the slash-delimited values.

So, some pseudocode to make sure we're all on the same page:

Step 1: Split the options in the source:

UNIQUE(SPLIT(epoch_field,'/'))

This would produce a list of individual items, e.g.:

• MEDIEVAL
• POST-MEDIEVAL
• IRON AGE
• EARLY IRON AGE

Step 2: Provide that list of options to the user as a pick-list.

Step 3: User selects an option (say, "IRON AGE"), and we query the data.  Again, some rough pseudocode:

'IRON AGE' IN STRING_SPLIT(epoch_field,'/')

The idea here is to grab all features that have "IRON AGE" somewhere in their epoch_field.  By doing it as a string-split, we can grab "IRON AGE" but not "EARLY IRON AGE".  I'm assuming of course that STRING_SPLIT is available in ArcGIS' implementation of SQL? But possibly not, if we're limited to Widget-based SQL in ExB, still.  If it's not available, you'd have to settle for CONTAINS, and searching for "IRON AGE" would either also return "EARLY IRON AGE", or would break in a case of "IRON AGE/EARLY IRON AGE".

It seems to me that the biggest hurdle is Step 1.  In the past, with other things like Dashboards and ExBs, I've been able to hard-code a list like that in the top of an Arcade script, but I've never found a way to do it in the highly-limited SQL we get in those places.

If baking that list in like that isn't possible, you might have to fork the source data and split all of those values to a series of BOOLEAN columns:

Feature	ERA_IronAge	ERA_EarlyIronAge	ERA_Medieval	ERA_PostMedieval	ERA_Roman
NAME	TRUE	TRUE	FALSE	FALSE	TRUE

And then you build a series of queries based on those split fields.

You could potentially even write either an Attribute Rule or a Field Calculation (depending on how you're storing & accessing the data) that would auto-populate those columns based on user entry in the combined "epoch_field", if new rows need to be added in the future.