Arcade Orderby logic for strings

Question

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 · Answer

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.

RyanBohan · Answer

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.

kenbuja · Answer

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