How to flag duplicate values in Dashboard table

[MeganHomison]

Hi, I'm pretty new to ArcGIS Online Dashboards, and I'm working with Dashboards tables for this. I'm looking to create an arcade function that will allow me to assign a background color to cells in the table that have identical values within a field. The field in question should only have 1 instance of each value, so I'd like to flag duplicates with a red background so the errors are easy to find.

I've figured out how to use Arcade to flag empty cells and those with leading or trailing spaces, but this is stumping me! 

Thank you!

[AustinAverill]

For a featureset that looks like that following:

You could call the value of your cell as a filter on a given field of the feature service and use the "Count()" function on the filtered feature service to determine if the value is appear multiple times in a given column. Like below: 

[KenBuja]

Unfortunately, this is not something that can be directly done through Advanced formatting in the Values tab. This is because the Arcade code in there only works with individual rows and not the entire dataset. You would have to create an additional field, which can be done with Arcade in a data expression that powers the table. Then you can use the value for the field to highlight the cell with Advanced formatting with Arcade.

This code adds the extra UniqueCount column to your dataset. It does run slowly, but I don't know if that was due to network issues we seem to be having today. I'll have to do some more testing to see if I can make it more efficient.

var fs = FeatureSetByPortalItem(
  Portal("your Portal"),
  "yourItem",
  0,
  false
);

var uniqueField = "your field";

var grouped = GroupBy(
  fs,
  uniqueField,
  { name: "Count", expression: "1", statistic: "COUNT" }
);

var fields = Schema(fs)["fields"];
Push(fields, { name: "UniqueCount", type: "esriFieldTypeSmallInteger" });

var temp_dict = {
  fields: fields,
  geometryType: "",
  features: []
};

for (var f in fs) {
  var attrs = {};
  var value;
  for (var attr in f) {
    attrs[attr] = f[attr];
    if (attr == uniqueField) {
      value = f[attr];
    }
  }
  var sql = `${uniqueField} = ${value}`;
  //var sql = `${uniqueField} = '${value}'` // if uniqueField is a text field

  attrs.UniqueCount = First(Filter(grouped, sql)).Count;

  Push(temp_dict["features"], { attributes: attrs });
}
return FeatureSet(temp_dict);

You don't need to add the UniqueCount field to the Value fields when setting up the table, but you'll use that field in the Advanced formatting tab. In this example, since all the Depth_ft values in my test table have multiple instances, I'm highlighting those that have more than 10 instances in line 1

var bgColor = iif ($datapoint.UniqueCount > 10, '#ff0000', '#ffffff')
return {
  cells: {
    FIELD_ID: {
      displayText : Text($datapoint.FIELD_ID),
      textColor: '',
      backgroundColor: '',
      textAlign: 'right',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    },
		
    LAT: {
      displayText : Text($datapoint.LAT),
      textColor: '',
      backgroundColor: '',
      textAlign: 'right',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    },
		
    LONG: {
      displayText : Text($datapoint.LONG),
      textColor: '',
      backgroundColor: '',
      textAlign: 'right',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    },
		
    DEPTH_FT: {
      displayText : Text($datapoint.DEPTH_FT),
      textColor: '',
      backgroundColor: bgColor,
      textAlign: 'right',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    },
		
  }
}

That yields this table