Makes sense. Well, if a Data Expression is an option, you can probably get it to work. There are a couple of approaches people use, but first, get your features:
var fs = FeatureSetByPortalItem(
Portal('your portal url'),
'itemid of your service',
0, // layer index of the feature layer
['objectid', 'Inspection_Date'],
false
)
A common approach from here is to loop through the entire FeatureSet, check the date field, and push features to an output FeatureSet with a new category field in place. By including the objectid, the resulting data can still be used to filter the rest of the dashboard.
It will work, and it's easy to work with Arcade's built-in Date functions. But if you have a lot of features, this can take a lot of time to evaluate.
var out_dict = {
fields: [
{name: objectid, type: 'esriFieldTypeOID'},
{name: inspection_complete, type: 'esriFieldTypeString'}
],
geometryType: '',
features: []
}
var threshold = Date(2024, 3, 1) // months are 0-indexed, so 3 is april
for (var f in fs) {
// check date
var insp_complete = Iif(
DateDiff(threshold, f['Inspection_Date']) >= 0,
'Incomplete',
'Inspection Completed'
)
// push into array
Push(
out_dict['features'],
{ attributes: { objectid: f['objectid'], inspection_complete: insp_complete }}
)
}
return FeatureSet(Text(out_dict))
Another method is to use GroupBy or Distinct. These functions let you use SQL to create new "fields" in the output, but excecute on the server side, and are much faster. Not as fun to work with timestamps, though. The particular format of the date functions will depend on the database your service is coming from.
var sql = `CASE
WHEN Inspection_Date < TIMESTAMP '2024-04-01 00:00:00'
THEN 'Incomplete'
ELSE 'Inspection Complete'
END`
return Distinct(
fs,
[
{ name: 'objectid', expression: 'objectid' },
{ name: 'inspection_complete', expression: sql }
]
)
If your system doesn't like the timestamp syntax, you can get away with using EXTRACT most of the time, something like:
CASE
WHEN EXTRACT(MONTH FROM Inspection_Date) >= 4 AND EXTRACT(YEAR FROM Inspection_Date) = 2024
THEN 'Inspection Complete'
ELSE 'Incomplete
END
- Josh Carlson
Kendall County GIS