Calculate field using Arcade expression

[Bryan_Wade]

I have a feature layer with a mishmash of values that should be the same.  IE:  Features that should be marked as "Reviewed" might be "Reviewed" or "reviewed", resulting in a legend with 4x classes instead of two? 

This issue occurred when I updated and republished my survey. It was suggested I recalculate the field using an arcade expression. I have never done this before and was hoping someone could give me an idea of how to go about it.

[jcarlson · Accepted solution]

If you go to the Data tab of your item and look at the Table, clicking a field heading lets you open up the Field Calculator. I would suggest using SQL for this, but either SQL or Arcade will work.

Arcade:

var s = $feature.status

return When(
    Find('ubmitted', s) != -1, 'Submitted',
    Find('eviewed', s) != -1, 'Reviewed',
    s
)

By using Find, we can tell our expression to return "Submitted" any time it finds 'ubmitted' in the field value and so on, which would account for capitals, extra spaces at the beginning / end.

In SQL, we can use LIKE, which is a bit more straightforward to me, but works on the same principal:

CASE
WHEN status LIKE '%ubmitted%' THEN 'Submitted'
WHEN status LIKE '%eviewed%' THEN 'Reviewed'
ELSE status
END

In both cases, we leave unmatched values at their initial value. The lists of conditions can easily be expanded for other statuses.

[Bryan_Wade]

When I use the arcade expression I end up getting this error message:

Execution Error:Runtime Error: Identifier Not Found. reviewed

Any thoughts?

[jcarlson]

What's the actual expression you're using?

[Bryan_Wade]

I was able to get it to work. but when I go to the map I'm still getting this:

Also, the indicator tiles in my dashboard are not picking up the field 'Status' correctly. I have a tile with a filter set for reviewed and one for submitted.