Arcade Orderby logic for strings

[RyanBohan]

Hi Esri community,

I am trying to use OrderBy on a String field.  It appears 5-characters numbers are being ordered before 6-character numbers.  Which is not ideal.  I can't change the Project Id field to a number as the database sometimes uses a letter at the end.  

Does anyone have a clever arcade trick to order by numbers that are stored as a string?  Ideally, I want it to return 135176 --> 612239 largest to smallest. 

Thank you!

[jcarlson]

The "sometimes uses a letter at the end" bit is going to complicate matters. Any time a function lets you use a SQL expression, you're free to attempt things like CAST(PROJECT_ID AS INT) and see if it does something you want. Trouble is, whether or not that works will depend on the data store where the layer is stored; not all functions will be available across the board.

Here's a pretty safe approach: keep the numbers as strings, but prepend "0" characters. Here's a made up feature set with the same problem. Sorting by ID gets me this:

var sql = `
CASE
WHEN char_length(id) < 2 THEN '000000' + id
WHEN char_length(id) < 3 THEN '00000' + id
WHEN char_length(id) < 4 THEN '0000' + id
WHEN char_length(id) < 5 THEN '000' + id
WHEN char_length(id) < 6 THEN '00' + id
WHEN char_length(id) < 7 THEN '0' + id
ELSE id
END
`

return groupby(
  fs,
  [
    {name: 'sort', expression: sql}
  ],
  {name: 'count', expression: '1', statistic: 'count'}
)

It's not exactly elegant, but the results:

[KenBuja]

Another way to do this is to make a new FeatureSet containing the fields you want to list. This example uses the sample FeatureSet from the Playground, converting the BUILDINGIDs to a number and sorts them.

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(
  Portal("https://www.arcgis.com"),
  // portal item id
  "6200db0b80de4341ae8ee2b62d606e67",
  0, // layer id
  ["*"], // fields to include
  true // include or exclude geometry
);
var filteredfs = Filter(fs, "OBJECTID < 20") //take only the first 20, since the full FeatureSet has 68K records and takes a while to process

//return OrderBy(filteredfs, "BUILDINGID DESC") - this checks the order of the original FeatureSet, showing the problem of sorts by text values

var features = [];

for (var f in filteredfs) {
  var num = Number(f.BUILDINGID);
  //this line removes the last character if it isn't numeric
  if (IsNan(Number(Right(f.BUILDINGID,1)))) num = Number(Left(f.BUILDINGID, Count(f.BUILDINGID)-1));
  var feat = { 
    'attributes': { 
      'newID': num,
      'oldID':f.BUILDINGID
    }
  };
  Push(features, feat)
}

var out_dict = { 
    'fields': [
        {'name': 'newID', 'alias': 'New ID', 'type': 'esriFieldTypeInteger'},
        {'name': 'oldID', 'alias': 'Old ID', 'type': 'esriFieldTypeString'}
    ],
  'geometryType': '', 
  'features': features 
}; 

// Convert dictionary to feature set. 
var newfs = FeatureSet(Text(out_dict)); 
return OrderBy(newfs, 'newID DESC')

[RyanBohan]

Hi @jcarlson & @KenBuja,

Both great ideas!  Thank you for your time, I will take them back to the team.  The occasional letter sure makes it more complicated.  

It's a one-to-many relationship that is being built in the pop-up with the order driving how they are shown.  On the worst-case scenario, it's about 800-1000 items in the FeatureSet that will shown in a long pop-up, the full Dataset is about a million records.  Which makes processing time a consideration.

We may consider updating the service to include a 0 for the 5-digit results, or the other idea we have been floating around is creating an Ordering Attribute during the ETL to create the Rest service.

[jcarlson]

A million records! Definitely don't do a for loop, you'll be waiting all day. Anything you can offload to the ETL or database is going to pay off. The popup really isn't the place for that sort of work.