Displaying Survey123 multiple choice values in a Dashboard chart that is also Filterable by other attributes

mross57

We have a survey that collects data and several fields are multiple choice. Because of this, Survey123 has configured a look up table to accommodate this in the form, but it actually stores the values as a long concatenated list of values in a single text field. I want to display this information so that I can summarize all features that *contain* one of the unique concatenated values and I can't seem to find an easy way to do this.  There is no "contain" option on a dashboard chart widget like there is on the filter widgets. There is also no way to automatically pull out and summarize uniquely configured values that have all been smashed together in a single text field so even if there was some "contain" operator for the chart widget, I would have to manually configure every single value and there are sometimes over a hundred options (too much room for error).

I saw the posts about "splitting the choices" using an expression like what's here: https://github.com/Esri/arcade-expressions/blob/master/dashboard/dashboard_data/SplitCategories(PieChart).md

However, this doesn't work for me because the chart is sourced by a feature set that has essentially two attributes: the count of each unique thing and the name of the unique thing. Doing it this way gives me a chart but there is no way to then filter the chart by other attributes. Let me use the CIP Solution as an example. I would like to show a chart of projects by collaborating departments (a multiple choice field in survey123) and then filter this based on whether the project is active or delayed, or perhaps by a project's type, etc. There are over two dozen ways we want to symbolize and filter on this data and 7 of them are multiple select fields in Survey123. 

So my bright idea was to generate some sort of denormalized feature set using a data expression that produces cartesian products or a set of all possible unique combinations for each feature for all fields that contain concatenated values. I want to know from the community if there is potentially a better way to do this? The logic of such an expression is extremely complex and I am actually pretty close to figuring it out but keep getting stuck in the entanglement of it all.

Here is my pseudo code if you're curious but I don't think it is working as expected. I didn't paste the arcade expression because I am moreso interested in general guidance on the logic of accomplishing this. Has anyone tried this? Has anyone gotten a chart of split up choices that is filterable by other values?

 

BEGIN
    // Step 1: Define Inputs and Configuration
    SET portalURL = "maps.arcgis.com/"
    SET itemID = "XXXXXXXXXXXXXXXXXXXXXXXXX"
    SET layerIndex = 2
    SET concatenatedFields = ["Field1", "Field2", ...]
    // define words that may follow a comma but should not be split because they are supposed to be stored as a single unique value including the commas
    SET wordsToProtect = ["infrastructure", "or", "and", "but"]

    // Step 2: Fetch the Feature Set
    SET fs = FeatureSetByPortalItem(Portal(portalURL), itemID, layerIndex, ["*"], false)

    // Step 3: Define Helper Functions
    FUNCTION SafeSplit(value)
        FOR EACH word IN wordsToProtect
            SET pattern = ", " + word
            SET value = REPLACE(value, pattern, "||" + word)
        END FOR
        SET splitArray = SPLIT(value, ",")
        FOR EACH item IN splitArray
            SET item = REPLACE(item, "||", ", ")
        END FOR
        RETURN splitArray
    END FUNCTION

    FUNCTION GenerateCombinations(arrays)
        SET result = [[]]
        FOR EACH array IN arrays
            SET temp = []
            FOR EACH combination IN result
                FOR EACH value IN array
                    SET newCombination = CONCATENATE(combination, [value])
                    PUSH(temp, newCombination)
                END FOR
            END FOR
            SET result = temp
        END FOR
        RETURN result
    END FUNCTION

    // Step 4: Prepare Output Data Structure
    SET outputDict = {
        'fields': [],
        'geometryType': '',
        'features': []
    }

    // Step 5: Add Original Fields to Output
    FOR EACH field IN SCHEMA(fs).fields
        PUSH(outputDict.fields, {'name': field.name, 'type': field.type})
    END FOR

    // Step 6: Add New Fields for Split Values
    FOR EACH field IN concatenatedFields
        PUSH(outputDict.fields, {'name': field + '_split', 'type': 'esriFieldTypeString'})
    END FOR

    // Step 7: Process Each Feature
    SET index = 0
    FOR EACH feat IN fs
        SET splitValues = []
        FOR EACH field IN concatenatedFields
            SET fieldValue = feat[field]
            SET updatedValue = REPLACE(fieldValue, "_", " ")
            SET splitArray = SafeSplit(updatedValue)
            SET filteredArray = []
            FOR EACH value IN splitArray
                SET value = TRIM(value)
                IF value IS NOT EMPTY AND value IS NOT NULL
                    PUSH(filteredArray, value)
                END IF
            END FOR
            PUSH(splitValues, filteredArray)
        END FOR

        // Step 8: Create New Features for Each Combination
        SET combinations = GenerateCombinations(splitValues)
        FOR EACH combination IN combinations
            SET newFeature = {}
            FOR EACH attr IN feat
                SET newFeature[attr] = feat[attr]
            END FOR
            FOR EACH field IN concatenatedFields
                SET newFieldName = field + '_split'
                SET newValue = combination[INDEX_OF(field)]
                IF newValue IS EMPTY OR newValue IS NULL
                    SET newFeature[newFieldName] = "No Data Available"
                ELSE
                    SET newFeature[newFieldName] = newValue
                END IF
            END FOR
            SET outputDict.features[index] = {'attributes': newFeature}
            SET index = index + 1
        END FOR
    END FOR

    // Step 9: Return the Output
    RETURN FEATURESET(TEXT(outputDict))
END

 

  

mross57

VOILA! 

I figured it out... I actually need a single expression for each choice field because otherwise I would have duplicate results, misleading numbers in my serial charts. Anyways, here is the Arcade script I used to denormalize a single multiple choice Survey123 field for use in a dashboard chart that can also be filtered by other attributes (including other multiple choice fields using the contains operator):

 

// Reference the feature layer
var portalURL = "https://ORG.maps.arcgis.com/";
var itemID = "1234567898765432123456787654"; // Example public layer item ID
var layerIndex = 2; // Use layerIndex 0 or 1 depending on the layer you want to access
var fs = FeatureSetByPortalItem(Portal(portalURL), itemID, layerIndex, ["*"], false);

// Fields with concatenated values
var concatenatedField = "FieldName"

// Words to protect from splitting (e.g., phrases containing commas)
var wordsToProtect = ["lorem", "ipsum", "dolor"];

// Function to split safely (avoiding splits before protected words)
function SafeSplit(value) {
    for (var word in wordsToProtect) {
        var pattern = ', ' + wordsToProtect[word]; // Comma followed by space and the word
        value = Replace(value, pattern, '||' + wordsToProtect[word]);
    }
    var splitArray = Split(value, ','); // Split by remaining commas
    for (var i in splitArray) {
        splitArray[i] = Replace(splitArray[i], '||', ', '); // Restore placeholders
    }
    return splitArray;

}

// Prepare the dictionary structure to hold processed data
var outputDict = {
    'fields': [],  
    'geometryType': '',  
    'features': []  
};

// Add all fields to the output dictionary
for (var field in Schema(fs).fields) {
    Push(outputDict.fields, {'name': Schema(fs).fields[field].name, 'type': Schema(fs).fields[field].type});
}

// Add new fields for split choice
Push(outputDict.fields, {'name': concatenatedField + '_split', 'type': 'esriFieldTypeString'});

var index = 0;
var incrementalOID = 1; // Initialize the incremental OBJECTID counter

// Loop through features in the feature set
for (var feat in fs) {  
    // Split each concatenated field and store the results in an array

    var fieldValue = feat[concatenatedField];
    var updatedValue = Replace(fieldValue, "_", " ");
    var splitArray = SafeSplit(updatedValue);

    // Add the split values to the new feature
    for (var j in splitArray) {
        // Copy all attributes from the original feature
        var newFeature = {};
        for (var attr in feat) {
            newFeature[attr] = feat[attr];
        }
        newFeature['OBJECTID'] = incrementalOID; // Overwrite OBJECTID with a unique incremental value for the returned featureset
        incrementalOID++; // Increment the counter
        var newvalue = splitArray[j];
        newFeature[concatenatedField + '_split'] = newvalue;

        // Add the new feature to the output dictionary
        outputDict.features[index] = {  
            'attributes': newFeature
        };
        index++;
    }
}

// Return the output dictionary
return FeatureSet(Text(outputDict));