Data Expression Null Values and Total Score

[AndrewWilson1]

I have the following dashboard data expression that looks at the results (view) of a Survey123 form and groups by Truck Number and does some basic addition. The Survey123 form displays fields based on a parameter URL. Each station, such as Fingers 1, Barrel, or Plow Drop, has a QR code that determines the fields that judges see. All of the results are populated into a single HFL. We can can calculate a Total Score field using ArcGIS Pro but we would like for this to be dynamic in a Dashboard. 

var fs = FeaturesetByPortalItem(
    Portal("https://cohgov.maps.arcgis.com"), // your Portal's url
    "b8411078d5fc40c9b65012fe72e02a4d",      // the service id
    0,                                       // the sub-layer id
    ['Truck_No', 'Fingers1', 'Fingers2', 'Fingers3', 'Barrel', 'Serpentine', 'AlleyDock', 'StreetOffset', 'Mailbox', 'TirePush', 'PlowDrop'], // the fields you want to load
    false                                    // do you want to load geometries
    )

return GroupBy(fs, ['Truck_No'],
[
    {name: 'Fingers1score', expression: 'Fingers1', statistic: 'SUM' }, 
    {name: 'Fingers2score', expression: 'Fingers2', statistic: 'SUM' }, 
    {name: 'Fingers3score', expression: 'Fingers3', statistic: 'SUM' }, 
    {name: 'BarrelScore', expression: 'Barrel', statistic: 'SUM' }, 
    {name: 'SerpentineScore', expression: 'Serpentine', statistic: 'SUM' }, 
    {name: 'AlleyDockScore', expression: 'AlleyDock', statistic: 'SUM' }, 
    {name: 'StreetOffsetScore', expression: 'StreetOffset', statistic: 'SUM' }, 
    {name: 'MailboxScore', expression: 'Mailbox', statistic: 'SUM' }, 
    {name: 'TirePushScore', expression: 'TirePush', statistic: 'SUM' },
    {name: 'PlowDropscore', expression: 'PlowDrop', statistic: 'SUM' },

    // Add a new aggregation to sum all the score fields
    {
        name: 'TotalScore', 
        expression: 'Fingers1 + Fingers2 + Fingers3 + Barrel + Serpentine + AlleyDock + StreetOffset + Mailbox + TirePush + PlowDrop', 
        statistic: 'SUM' 
    }
]
)

The featureset looks decent.

I'm running into two issues:

1. The summed null values should convert to 0.
2. TotalScore isn't working at all.

Any suggestions from the Esri Community?

Thank you!

[Neal_t_k]

Try something like this:

{
  name: 'TotalScore',
  expression: 'DefaultValue(Fingers1, 0) + DefaultValue(Fingers2, 0) + DefaultValue(Fingers3, 0) + DefaultValue(Barrel, 0) + DefaultValue(Serpentine, 0) + DefaultValue(AlleyDock, 0) + DefaultValue(StreetOffset, 0) + DefaultValue(Mailbox, 0) + DefaultValue(TirePush, 0) + DefaultValue(PlowDrop, 0)',
  statistic: 'SUM'
}

This should set the value to 0 when the items are 'null'  

[Neal_t_k]

You can do something similar in the GroupBy as well.

 {name: 'Fingers1score', expression: 'DefaultValue(Fingers1, 0)', statistic: 'SUM' }

[AndrewWilson1]

Thank you for your responses! We got it to work using the code below. This was created using a combination of your responses and fed into Gemini and Copilot.

var fs = FeaturesetByPortalItem(
    Portal("https://cohgov.maps.arcgis.com"), // your Portal's url
    "b8411078d5fc40c9b65012fe72e02a4d",       // the service id
    0,                                        // the sub-layer id
    ['Truck_No', 'Fingers1', 'Fingers2', 'Fingers3', 'Barrel', 'Serpentine', 'AlleyDock', 'StreetOffset', 'Mailbox', 'TirePush', 'PlowDrop'], // the fields you want to load
    false                                     // do you want to load geometries
    )

var fs_clean_features = [];
// Define the fields for the new featureset structure
var fields_to_include = [
    { name: 'Truck_No', type: 'esriFieldTypeString' },
    { name: 'Fingers1', type: 'esriFieldTypeDouble' },
    { name: 'Fingers2', type: 'esriFieldTypeDouble' },
    { name: 'Fingers3', type: 'esriFieldTypeDouble' },
    { name: 'Barrel', type: 'esriFieldTypeDouble' },
    { name: 'Serpentine', type: 'esriFieldTypeDouble' },
    { name: 'AlleyDock', type: 'esriFieldTypeDouble' },
    { name: 'StreetOffset', type: 'esriFieldTypeDouble' },
    { name: 'Mailbox', type: 'esriFieldTypeDouble' },
    { name: 'TirePush', type: 'esriFieldTypeDouble' },
    { name: 'PlowDrop', type: 'esriFieldTypeDouble' }
];

for (var f in fs) {
    var newFeature = {
        attributes: {
            Truck_No: f['Truck_No'],
            // IIF(IsEmpty(f['FieldName']), 0, f['FieldName']) ensures the result is a number (0) or the original number.
            Fingers1: IIF(IsEmpty(f['Fingers1']), 0, f['Fingers1']),
            Fingers2: IIF(IsEmpty(f['Fingers2']), 0, f['Fingers2']),
            Fingers3: IIF(IsEmpty(f['Fingers3']), 0, f['Fingers3']),
            Barrel: IIF(IsEmpty(f['Barrel']), 0, f['Barrel']),
            Serpentine: IIF(IsEmpty(f['Serpentine']), 0, f['Serpentine']),
            AlleyDock: IIF(IsEmpty(f['AlleyDock']), 0, f['AlleyDock']),
            StreetOffset: IIF(IsEmpty(f['StreetOffset']), 0, f['StreetOffset']),
            Mailbox: IIF(IsEmpty(f['Mailbox']), 0, f['Mailbox']),
            TirePush: IIF(IsEmpty(f['TirePush']), 0, f['TirePush']),
            PlowDrop: IIF(IsEmpty(f['PlowDrop']), 0, f['PlowDrop'])
        }
    };
    Push(fs_clean_features, newFeature);
}

// -----------------------------------------------------
// CRITICAL FIX: Explicitly pass the schema and features
// -----------------------------------------------------
var fs_clean = Featureset({
    fields: fields_to_include,
    geometryType: '', // No geometry
    features: fs_clean_features
});


// Use fs_clean in the GroupBy function
return GroupBy(fs_clean, ['Truck_No'],
[
    // The individual score fields are calculated using the clean data (0 instead of null)
    {name: 'Fingers1score', expression: 'Fingers1', statistic: 'SUM' }, 
    {name: 'Fingers2score', expression: 'Fingers2', statistic: 'SUM' }, 
    {name: 'Fingers3score', expression: 'Fingers3', statistic: 'SUM' }, 
    {name: 'BarrelScore', expression: 'Barrel', statistic: 'SUM' }, 
    {name: 'SerpentineScore', expression: 'Serpentine', statistic: 'SUM' }, 
    {name: 'AlleyDockScore', expression: 'AlleyDock', statistic: 'SUM' }, 
    {name: 'StreetOffsetScore', expression: 'StreetOffset', statistic: 'SUM' }, 
    {name: 'MailboxScore', expression: 'Mailbox', statistic: 'SUM' }, 
    {name: 'TirePushScore', expression: 'TirePush', statistic: 'SUM' },
    {name: 'PlowDropScore', expression: 'PlowDrop', statistic: 'SUM' },
    
    // TotalScore will now correctly sum non-null values (0 for originals nulls)
    {
        name: 'TotalScore', 
        expression: 'Fingers1 + Fingers2 + Fingers3 + Barrel + Serpentine + AlleyDock + StreetOffset + Mailbox + TirePush + PlowDrop', 
        statistic: 'SUM' 
    }
]
)

Screenshot of the finished Dashboard.

We have ideas for how we can improve for 2026!