Hi @MikeCooperstein ,
If you are collecting your inspection data into a related layer, you can use a data expression like the example below to query the related record with the most recent data and use it as a data source for the list widget on your dashboard. Make sure to modify the script according to your environment and your data schema (e.g. item id, field names, etc.)
// Create a featureSet from the related table. Replace the ITEMID and LAYERID to reference your related layer.
var portal = Portal('https://www.arcgis.com');
var relatedTable = FeatureSetByPortalItem(
portal,
'ITEMID',
LAYERID,
['*'],
false);
// Identify unique values in the FOREIGNKEY field to use in filter statement later. Replace FOREIGNKEY with the name of the field that the relationship is based on.
var uniqueFkeys = Distinct(relatedTable, ['FOREIGNKEY']);
// Define a dictionary to write the attributes of the selected features into. Field names here are optional and you need to specify the field type based on the data that you want to write into each field. A table created from this dictionary is the final output of this script.
var dict = {'fields': [
{'name': 'FIELDANAME', 'type': 'esriFieldTypeString'},
{'name': 'FIELDBNAME', 'type': 'esriFieldTypeString'},
{'name': 'FIELDCNAME', 'type': 'esriFieldTypeDate'}],
'geometryType': '',
'features': []};
// Query the records for each feature based on another field. For instance, you can query the most recent inspection of each site, or the inspection with the highest reading for each site. In this example, related records are filtered by DATEFIELD to return the most recent inspection.
var index = 0
for (var x in uniqueFkeys) {
var id = x.FOREIGNKEY;
var filterstatement = ('FOREIGNKEY = @id');
var filtrecords = Top(OrderBy(Filter(relatedTable, filterstatement),'DATEFIELD DSC'),1);
// Populate attributes from the selected features into the fields in the dictionary. In this example, FROREIGNKEY, FIELD1, and DATEFIELD are used. You can replace these with the name of the fields from your related table that you want to include in the final table.
for (var y in filtrecords){
var FIELDAVALUE = y.FOREIGNKEY;
var FIELDBVALUE = y.FIELD1;
//Dates should be converted to number to write into a date type field in the dictionary
var FIELDCVALUE = number(y.DATEFIELD);
// populate the attributes of the selected feature into the dictionary
dict.features[index] = {
'attributes': {
'FIELDANAME': FIELDAVALUE,
'FIELDBNAME': FIELDBVALUE,
'FIELDCNAME': FIELDCVALUE
}};
}
index += 1;
}
// return the featureset with selected records from the related table
return FeatureSet(Text(dict));
All the best,
MJ
