Show list based on most recent visit (time)

07-14-2020 01:16 PM
New Contributor II

Does anyone know if there is a way to show a list in Operations Dashboard based on the most recent time an asset was visited?

Example ArcGIS Dashboards 

Say I was using Survey123 to do a hydrant inspection. I do my inspection and mark the hydrant as inoperable and list the problems. Now I have 8 Hydrants that are "Inoperable Hydrants". The next time I go back, I fix the hydrant and then re-inspect. Now I create a new record because I'm doing a new inspection and I mark the new survey as operable. So I have 2 records one from the past where the hydrant was inoperable and a new one saying it is now operable. How can I see that update in the dashboard without having to go back and change my initial inoperable record? The main issue is we want to preserve the history of the asset so we know when it was inoperable, but we want the dashboard to show the most recent status.



Tags (1)
0 Kudos
3 Replies
New Contributor II

Seems like this may be my answer.

0 Kudos
Esri Contributor

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('');
var relatedTable = FeatureSetByPortalItem(

// 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 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,


0 Kudos
New Contributor III

Hi MJ,


Thank you for sharing this very helpful arcade sample. I couldn't quite figure out how to get it to work right away for me, until I realized I left off the call to sort date descending! Now it's working exactly the way I want it! Bravo

0 Kudos