nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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:

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

Any suggestions from the Esri Community?

Thank you!

Encontre mais publicações marcadas com

ArcGIS Online

Accepted answers

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!

All comments

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!

Neal_t_k

You can do something similar in the GroupBy as well.

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

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'

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.