topic Re: Create a table with domains as columns in ArcGIS Dashboards Questions

Create a table with domains as columns

EleanorCervigni — Thu, 13 Mar 2025 04:22:00 GMT

I am trying to use the table widget to build a table that has grouped values for both columns and rows. Eg this example:

	Field 2, domain 1	Field 2, domain 2	Field 2, domain 3
Field 1, domain 1
Field 1, domain 2
Field 1, domain 3

Obviously rows are easy but if i understand correctly i would need to restructure my data so that i have a column for each of the domains in Field 2? I have tried to do this with arcade and get to the point where I have a dictionary but cannot convert it into a featureset to use in the table.. Here is where I am at with the code, but I am getting an error with the push function.

I am new to arcade so am probably making many mistakes, Any help is appreciated!

Thanks

```

// Define the field to extract coded domains from
var targetField = "sys_workflow_status"; // Change this to match the actual field name

var fs = FeatureSetByPortalItem(
Portal("portall"), // URL to your Portal or ArcGIS Online
"id", // Replace with your actual layer item ID
0, // Layer index (0 for the first layer)
["*"] // Fields to include (all fields in this case)
);

// Check if the feature set is empty
if (IsEmpty(fs)) {
return "Error: No features found in this dataset.";
}

// Get the schema for the layer to retrieve domain information
var layerSchema = Schema(fs);
var fieldNames = [];

// Loop through the schema fields to extract field names
for (var i = 0; i < Count(layerSchema.fields); i++) {
var field = layerSchema.fields[i];
Push(fieldNames, field.name); // Access the 'name' property of the field object
}

// Check if the target field exists in the list of field names
var fieldIndex = IndexOf(fieldNames, targetField);

// If the field does not exist, return an error
if (fieldIndex == -1) {
return "Error: Field '" + targetField + "' not found in the feature set.";
}

// Retrieve the domain information for the target field
var domainInfo = null;
for (var i = 0; i < Count(layerSchema.fields); i++) {
var field = layerSchema.fields[i];
// Check if the field has a domain (not null or undefined)
if (field.name == targetField && field.domain != null) {
domainInfo = field.domain;
break;
}
}

// If no domain is found, return a message
if (domainInfo == null) {
return "Error: No domain found for field '" + targetField + "'.";
}

// Create a dictionary of domain mappings (assumes it's a coded value domain)
var domainMappings = {};
if (domainInfo.type == "codedValue") {
// If the domain type is coded value, we can extract the coded values and their descriptions
for (var i = 0; i < Count(domainInfo.codedValues); i++) {
var codedValue = domainInfo.codedValues[i];
domainMappings[codedValue.code] = codedValue.name;
}
}

// Create a dictionary to store the modified feature results
var resultDict = {};

// Loop through all the features in the feature set
for (var f in fs) {

// Create a new result object for each feature, starting with the original attributes
var featureResult = {};
var geom = Geometry(f); // Get the geometry from the original feature

// Copy the original fields into the result (to retain the original attributes)
for (var fieldName in f) {
featureResult[fieldName] = f[fieldName];
}

// Get the value for the target field in the current feature
var targetValue = f[targetField];

// Loop through the domain mappings and create a new column for each domain value
for (var domainCod in domainMappings) {
var domainValue = domainMappings[domainCod];

// If the value matches the domain code, set the new column value to 1, otherwise set it to 0
if (targetValue == domainCod) {
featureResult[targetField + "_" + domainValue] = 1;
} else {
featureResult[targetField + "_" + domainValue] = 0;
}
}

// Use a string to store the key (e.g., ObjectID or any unique identifier)
var objectID = Text(f["ObjectID"]);

// Add the modified feature result to the dictionary using the string key (ObjectID)
resultDict[objectID] = featureResult;
}

// Now, convert the dictionary back into a FeatureSet
var resultFeatures = [];
for (var key in resultDict) {
var fData = resultDict[key]; // Changed variable name from 'feature' to 'fData'

// Create a new feature from the geometry and the updated attributes
var newFeature = Feature(geom); // Initialize a new feature with geometry

// Set the fields for the new feature
for (var fieldName in fData) {
// Add the field and its value to the new feature
newFeature[fieldName] = fData[fieldName];
}

// Add the new feature to the result features list
resultFeatures.push(newFeature);
}

// Create a FeatureSet from the result features array
var newFeatureSet = FeatureSet(resultFeatures);

// Return the new FeatureSet for use in the table widget
return newFeatureSet;

Re: Create a table with domains as columns

KenBuja — Thu, 13 Mar 2025 19:04:39 GMT

Can you give a better idea of what you'd like your final table to look like? Are you trying to get a summary of how many records are in the domains for each pair of fields? I'm guessing if your table looks like this

id	Field 1	Field2
1	domain 1	domain 1
2	domain 2	domain 1
3	domain 3	domain 3
4	domain 1	domain 1
5	domain 1	domain 2

you want your output table looking like this.

	Field 2, domain 1	Field 2, domain 2	Field 2, domain 3
Field 1, domain 1	2	0	0
Field 1, domain 2	1	1	0
Field 1, domain 3	0	0	1

Is this correct?

Re: Create a table with domains as columns

EleanorCervigni — Mon, 17 Mar 2025 01:10:48 GMT

Yes thats it, the domains are different in each field. For clarity here is an example with some fake dataö

Original table

ID	Report status	Suburb
1	submitted	perth
2	assigned	booragoon
3	finalised	booragoon
4	finalised	perth
5	assigned	armadale

Output table:

	Submitted	Assigned	Finalised
Perth	1	0	1
Booragoon	0	1	1
Armadale	0	1	0

Re: Create a table with domains as columns

KenBuja — Mon, 17 Mar 2025 19:41:02 GMT

Here's the script I came up with. It does your error checking, returning a table (InvalidTable function) with the error message if the featureset doesn't contain the fields or fields with domains. Since this could be a very memory-intensive code as it uses the Filter function for each combination of domains, I also include @jcarlson's Memorize function to read the FeatureSet into memory.

Note that in my test, this used domains with a numeric code, so I used the description for its name (lines 111 and 121). If you use string codes, you can just use "key" and "ykey" instead of "xDomainMappings[key]" and "yDomainMappings[ykey]" in those lines.

I ran this on my data and got this table.

I tested these results using this GroupBy function,

return GroupBy(fs, targetFields, {name: "Count", expression: '1', statistic: 'Count'})

which gave me a table of the counts of each combination that was greater than zero.