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(PieC...
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
