Create a table with domains as columns

[EleanorCervigni]

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;

[KenBuja]

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?

[EleanorCervigni]

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

[KenBuja]

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.

function InvalidTable(message) {
  return FeatureSet(
    {
      fields: [{ alias: "error", name: "error", type: "esriFieldTypeString" }],
      geometryType: "",
      features: [{ attributes: { error: message } }]
    }
  );
}

function Memorize(fs) {
  var temp_dict = {
    fields: Schema(fs)["fields"],
    geometryType: "",
    features: []
  };
  for (var f in fs) {
    var attrs = {};
    for (var attr in f) {
      attrs[attr] = Iif(TypeOf(f[attr]) == "Date", Number(f[attr]), f[attr]);
    }
    Push(temp_dict["features"], { attributes: attrs });
  }
  return FeatureSet(temp_dict);
}

// Define the field to extract coded domains from
var targetFields = ["xAxisField", "xAxisField"]; //Change this to match the actual field names
var targetXField = targetFields[0]; 
var targetYField = targetFields[1];

var inputFS = FeatureSetByPortalItem(
  Portal("your portal"),
  "itemId",
  0,
  targetFields
);

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

// Get the schema for the layer to retrieve domain information
var fields = Schema(inputFS).fields;
var containsXTargetField = false;
var containsYTargetField = false;
var xDomainInfo;
var yDomainInfo;

// Loop through the schema fields to extract field names
for (var i = 0; i < Count(fields); i++) {
  var field = fields[i];
  if (field.name == targetXField) {
    containsXTargetField = true;
    // Retrieve the domain information for the XTarget field
    if (field.domain != null) xDomainInfo = field.domain;
  }
  if (field.name == targetYField) {
    containsYTargetField = true;
    // Retrieve the domain information for the YTarget field
    if (field.domain != null) yDomainInfo = field.domain;
  }
}

// Check if the target field exists in the list of field names
// If the field does not exist, return an error
if (!containsXTargetField)
  return InvalidTable(
    `Error: Field '${targetXField}' not found in the feature set.`
  );
if (!containsYTargetField)
  return InvalidTable(
    `Error: Field '${targetYField}' not found in the feature set.`
  );

// If no domain is found, return a message
if (IsEmpty(xdomainInfo))
  return InvalidTable(`Error: No domain found for field '${targetXField}'.`);
if (IsEmpty(ydomainInfo))
  return InvalidTable(`Error: No domain found for field '${targetYField}'.`);

// Create a dictionary of domain mappings (assumes it's a coded value domain)
var xDomainMappings = {};
if (xDomainInfo.type != "codedValue")
  return InvalidTable(
    `Error: Domain for field '${targetXField}' is not coded-value.`
  );
// If the domain type is coded value, we can extract the coded values and their descriptions
for (var i = 0; i < Count(xDomainInfo.codedValues); i++) {
  var codedValue = xDomainInfo.codedValues[i];
  xDomainMappings[`${codedValue.code}`] = codedValue.name;
}

var yDomainMappings = {};
if (yDomainInfo.type != "codedValue")
  return InvalidTable(
    `Error: Domain for field '${targetYField}' is not coded-value.`
  );
// If the domain type is coded value, we can extract the coded values and their descriptions
for (var i = 0; i < Count(yDomainInfo.codedValues); i++) {
  var codedValue = yDomainInfo.codedValues[i];
  yDomainMappings[`${codedValue.code}`] = codedValue.name;
}
console(xDomainMappings)
//Create the fields to hold the counts
var fieldList = [{ alias: "Domain", name: "Domain", type: "esriFieldTypeString" }];
for (var key in xDomainMappings) {
  Push(
    fieldList,
    { alias: xDomainMappings[key], name: key, type: "esriFieldTypeInteger" }
  );
}

//Store the fs in memory for multiple Filter calls
var fs = Memorize(inputFS);

//Loop through all the combinations of domains
var features = [];
for (var ykey in yDomainMappings) {
  var attributes = { Domain: yDomainMappings[ykey] };
  for (var xkey in xDomainMappings) {
    var query = `${targetXField} = @xkey AND ${targetyField} = @ykey`;
    attributes[xkey] = Count(Filter(fs, query));
    //console(`ykey: ${ykey}, xkey: ${xkey}: ${attributes[xkey]}`);
  }
  Push(features, { attributes: attributes });
}

var dict = { fields: fieldList, features: features };
return FeatureSet(dict);

 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.