Dashboard List Data Expression Groupby Help with Arcade Statistics no "Last"

[TylerJones]

Hi Y'all,
This is probably a novice arcade question but I am tired of banging my head on the keyboard.

Background:
I have a FS on a Portal that is a running list of points that are being populated by a geoform. The data collected is the status of tools (18 items in a dropdown) in our utilities tool room (checked in or out - text dropdown) and as well as other information. I am trying to show the status of each tool using the GroupBy function for a data expression inside a dashboard.

Problem:
All of the statistics I need are availablefor each tool group in the list except the text field “Status”. I am struggling to figure out how to show the latest “Status” (in or out) for each tool, because ‘LAST’ is not a choice. (Possible Values: SUM | COUNT | MIN | MAX | AVG | STDEV | VAR). Obviously if I use ‘MAX’ it will return the “Out” every time because it is ‘MAX’ in the alphabet. If I get the MAX “ObjectID”, is there a way to link it to the “status” in the same row? Or am I just hopeless?

In the end all I really need to show is if a tool is checked in or out of the room based on the latest point in a group. 

I’m open to any ideas.

Here’s the Arcade expression as it stands:

var portal = Portal('https://gis.myorg.org/portal/'); 
var fs = FeatureSetByPortalItem( portal,'8787864654654654654654654c', 10,
    ['Tool_Description',
    'last_edited_date',
    'Status',
    'OBJECTID'], False  ); 

Var allstat = GroupBy(fs, ['Tool_Description'],
[ 
{name: 'LastDate', expression: 'last_edited_date', statistic: 'MAX'},

{name: 'Status', expression: 'Status', statistic: 'COUNT'},

{name: 'OID', expression: 'OBJECTID', statistic: 'MAX'},
//Possible connection to the LAST "Status" in a row   


{name: 'OIDtoIN', expression: 'OBJECTID*1', statistic: 'MAX'},
//OIDtoIN is to relate lists 

]) 

Return allstat

Thanks,

[jcarlson]

How is your data organized? Is it 18 features being edited repeatedly, or are you just adding to the table each time something changes?

[TylerJones]

@jcarlson , thanks I’d hope to see your name on this one😁.  We’re just adding to the table repeatedly. That’s why the stats and groups are being used. Like I said, it’s a geoform.  My utility techs like the format but the geometry is really not even necessary.  It’s just a table of coded values, dates, users and status (in or out). 

[jcarlson · Accepted solution]

Got it. Well, ordinarily I would say that GroupBy is the a good bet, but since we're trying to keep the status of the tool as well, the grouping statistics make that a bit harder. I had initially thought there was no way to avoid writing a FeatureSet "from scratch" using a loop of some kind, but I think we can actually do this. It was this sentence that did it:

If I get the MAX “ObjectID”, is there a way to link it to the “status” in the same row?

So, consider the output of your expression. You'll have a FeatureSet that includes the objectID of the most recent status per tool. We can loop through that FeatureSet, grab the IDs, then use that as a list to filter the original layer. And actually, assuming nobody's "postdating" their entries on that form, the max objectID is going to be the same record as the most recent date, right? So we don't even need to worry about the date in our grouping expression. Here's what it might look like:

var portal = Portal('https://gis.myorg.org/portal/'); 
var fs = FeatureSetByPortalItem(
  portal,
  '8787864654654654654654654c',
  10,
  ['*'], // since GroupBy sends the query to the server, where all fields are available, what we specify for this parameter doesn't actually matter
  False
); 

// get latest date per tool, plus objectid
Var allstat = GroupBy(
  fs,
  'Tool_Description',
  {name: 'OID', expression: 'OBJECTID', statistic: 'MAX'}
)

// loop through allstat and get the ids, push into array
var ids = []

for (var a in allstat){
  Push(ids, `'${a['OBJECTID']}'`)
}

// get ids as single SQL filter statement
var sql = `OBJECTID IN(${Concatenate(ids, ',')})`

// filter the featureset for matching ids
returh Filter(fs, sql)

[TylerJones]

Thanks for the help Josh!

I'm sorry it took a couple weeks to get back to this reply (holiday/work). 

I finally got a chance test your suggestion. As far as the concept your idea makes sense and seems like a much better solution but I am getting an ‘Unable to Execute Arcade Script’. Everything seems to working fine connecting to the feature service (if I delete everything below “//loop through” it can return ‘fs’) but the expression is giving me a blank result.

Like I said in the first post, I’m in over my head. The only change I made was changing “returh” to “return”. I sort of get what you are doing on the loop but I’m lost in the sql (again a novice). Embarrassingly, I’ve spent an hour trying to work through this to no avail. 

Thanks in advance if other ideas are forthcoming.     

           

Best,

[TylerJones · Accepted solution]

I ran out of Ideas and tried AI and got aproximatly this after some cleanup:

var portal = Portal('https://gis.myorg.org/portal/');
var fs = FeatureSetByPortalItem(
  portal,
  '8787864654654654654654654c',
  10,
  ['*'], // since GroupBy sends the query to the server, where all fields are available, what we specify for this parameter doesn't actually matter
  false // Use lowercase 'false'
);

// get latest date per tool, plus objectid
Var allstat = GroupBy(
  fs,
  'Tool_Description',
  {name: 'OID', expression: 'OBJECTID', statistic: 'MAX'}
)


/// loop through allstat and get the ids, push into array
var ids = [];

for (var a in allstat) {
  Push(ids, a['OID']); // Remove single quotes and use 'OID' as the key
}

// get ids as a single SQL filter statement
var sql = 'OBJECTID IN (' + Concatenate(ids, ',') + ')';

// filter the featureset for matching ids
return Filter(fs, sql); // Correct 'returh' to 'return'

 Thank you so much for your help Josh, I never could have made this far otherwise. 

[ArmstKP]

@TylerJones @jcarlson I tried the exact same code, but with my field names and the expression won't execute.

var portal = Portal('https://www.arcgis.com/');
var fs = FeatureSetByPortalItem(
  portal,
  'xxx',
  0,
  ['checkin_time', 'checkin_fullname', 'checkin_status'], 
  false 
);

/// get latest date per checkin, plus objectid
Var allstat = GroupBy(
  fs,
  'checkin_fullname',
  {name: 'OID', expression: 'OBJECTID', statistic: 'MAX'}
)


/// loop through allstat and get the ids, push into array
var ids = [];

for (var a in allstat) {
  Push(ids, a['OID']); 
}

// get ids as a single SQL filter statement
var sql = 'OBJECTID IN (' + Concatenate(ids, ',') + ')';

// filter the featureset for matching ids
return Filter(fs, sql);

When I test the expression it works, but when I click on the "Done" button, nothing happens.  And when I go back to the Data expressions, I get this error triangle that says, "Unable to execute Arcade script":

[ArmstKP]

Update:  For some reason,  now it is working....