Calculate field using Arcade expression

469
4
Jump to solution
07-19-2022 12:48 PM
Bryan_Wade
Occasional Contributor

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? 

Wade_Bryan_0-1658260007473.png

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.

 

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS

View solution in original post

0 Kudos
4 Replies
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
Bryan_Wade
Occasional Contributor

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

Execution Error:Runtime Error: Identifier Not Found. reviewed

Any thoughts?

0 Kudos
jcarlson
MVP Esteemed Contributor

What's the actual expression you're using?

- Josh Carlson
Kendall County GIS
0 Kudos
Bryan_Wade
Occasional Contributor

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

Wade_Bryan_0-1658345368085.png

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.

 

0 Kudos