How to re-assign large sets of List of Values (Domains) selections to other choices in AGOL

[Paul_Keidel]

What are ways to edit large groups of Feature Layer entries that use List of Values (Domains)?

I have an Inspector column with a list of employees names. Some of these employees no longer work for the company. Granted, I can edit the List of Values and remove select employee names so they are no longer selectable (the ones no longer employed). However, the database still shows their names associated with entries. I want to re-assign those Inspector entries to 'Former employee' so I added 'Former employee' as a new entry in the List of Values. Now it would simple to switch up to ~50 entries by double-clicking each one in Table view, but I have over 1,000 entries to change. When I tried Calculate Field, only the SQL option is available since this Feature Layer has Sync and Created/Edited enabled. So no Arcade! 😞 The SQL interface there does not appear to know of existing Lists of Values. If I simply enter "Former employee" in the equation field, I get the error 'Invalid field name'.

In the case above, I want to re-assign all occurrences of Employee D and Employee F to 'Former employee'. In another case, I want to re-assign a subset of tree species to 'Unknown'. Some entries require re-evaluation, so I want to re-assign some [not all] of Species A and Species G to 'Unknown species'. Again, Sync and Created/Edited is enabled for this Feature Layer.

My Arcade brain wants to write an equation like "If name is This, then change it to That." But alas, Arcade is not a Calculate Field option here.

I thought of exporting the Feature Layer to ArcGIS Pro, performing Arcade editing there, appending the updated dataset to the original dataset, and then deleting the rows of original data. But I was concerned with losing already established map symbology or having to re-create the Field Maps apps. I've conducted a lot of searching and wanted to learn whether there is a slick AGOL-way to make my changes.

Thanks!

[wayfaringrob · Accepted solution]

If you do have Pro available, add the layer from your portal, select by attributes where Inspector = Employee D OR Inspector = Employee E, and from the attributes pane, ensure the layer name is selected (not the individual features) and make the change. It'll update all the records.

[Paul_Keidel]

@wayfaringrob  I was aware that AGP could reach out to AGOL files but was unaware that the List of Values entries could be selected and changed. Wow! Then other than waiting a few minutes for the AGP changes to propagate up to AGOL, this process worked just fine. Thank you for taking the time to help and teach! 😄

[Clubdebambos]

Hi @Paul_Keidel 

You can do this in an AGOL WebMap. Use a filter to only show records that contain the names of the former employees (or for your species re-allocation). Open the attribute table and click on the field name and click Calculate. You can use SQL, all you need to enter is the coded value of the domain, not the description. If this is a number simply put in 5, if it is text use quotes like 'Former Employee' for example. The SQL update will only act on the records in the filter so do a quick check on the table to see that the records are what you want updated. 

[Paul_Keidel]

@Clubdebambos I thought this option was going to be the fastest and provide instant visual feedback on the WebMap. So far, this option is not working for me. If I go into the Data tab for my hosted Feature Layer, I can right-click my Category column and see the Calculate option. But back in the WebMap, after I filter the the records, then click Show table and click the 3-dots at the top of my Category column, the Calculate option is not there.

I found an Esri Community post from 2017, Where is CALCULATE? A resolution was never reached in that post. As in that post, I am the Creator and Owner of the particular Feature Layer that I want to change.

At the Settings tab for my hosted Feature Layer, the four Editing boxes are checked (Enable editing, Track changes, Track who, and Enable Sync). The three types of Editing boxes are also checked (Add, Delete, and Update).

I went back to the TEST copy of my original data (a subset). I highlighted some rows in the Table view of this Feature Layer. Then using Calculate from the top of the column, I entered Code 707 into the SQL equation field. Clicking Calculate then proceeded to change *every* entry in my TEST data, not just the highlighted rows. Great lesson learned there on a TEST file!!

I'm wondering if Enabling Sync and Enabling Created/Edited in the Feature Layer locks out the Calculate option in a WebMap? Hmmm ...

There's always the possibility there's one more button I need to check. Not much comes up in goo_gle searches for Where is Calculate.

I do appreciate you taking the time to present this method as an option to address my original question. It sure 'feels' as if this option should work. If/when I figure out the reason Calculate is not a WebMap Show table Attribute column option, I will get back to this post.

Thanks!! 😄

[Clubdebambos · Accepted solution]

When you open the the attribute table in the WebMap and left-click on the field there's no Calculate option like below? (also try opening the feature service in a new webmap). The feature service below has Full Editing, Sync and Editor tracking enabled and all the bells and whistles.

Sounds like an AGOL Gremlin has got into your system, we've had a couple recently ourselves, someone must have fed a Mogwai after midnight 😄

This has been a good thread with @wayfaringrob and @jcarlson providing great solutions too.

[Paul_Keidel]

When I open my WebMap in Map Viewer Classic, the Calculate option is there! Just as you show above.

When I open my WebMap in Map Viewer, there is no Calculate option in the Show table columns.

And yes, this thread with @wayfaringrob and @jcarlson and @Clubdebambos has been very instructive. Once again, there are multiple ways to perform a complex task using the Esri products. These suggestions will save me a bajillion mouse clicks, because so many of the Esri suggestions talk about double clicking one entry at a time. But I have 1,000s!  I hope these suggestions help other users as well. --PK

[jcarlson · Accepted solution]

Using the SQL Field Calculator, you can do a CASE statement. As you note, you'll need to know your domain's codes, as opposed to the labels, but here's how it might look:

CASE
WHEN Inspector IN ('Employee D', 'Employee F') THEN 'Former Employee'
ELSE Inspector
END

Or suppose you wanted to do something different depending on different conditions, like reassigning one employee's work to one person, and another to someone else:

CASE
WHEN Inspector = 'Employee D' THEN 'Employee E'
WHEN Inspector = 'Employee F' THEN 'Employee B'
ELSE Inspector
END

You can use other SQL commands like LIKE or what have you, but this is the equivalent of an if/then in the SQL field calculator tool. The line "ELSE Inspector" simply retains the current field value in the event that the name does not match anything in your conditions.

 If you're able to consistently identify your species by attribute in some way, you can do the same for that.

CASE
WHEN (Species = 'Species A' AND some_other_attribute = 'some other value') OR (Species = 'Species G' AND another_attribute = 'a different value') THEN 'Unknown species'
ELSE Species
END

[Paul_Keidel]

@jcarlson Admittedly I'm light on SQL. I read through the Calculate Field Values page but did not see an if-then-else (CASE) type of option in the tables. I mistakenly took the quote "This section provides a list of the operators and SQL functions you can use for SQL calculations in ArcGIS Online" literally. What they probably mean is that all of Standardized SQL-92 may be used, and that the tables there are simply a subset. Now I know!

I generally set my Lists of Values to be Integer types. The Labels will be Employee A or Species B while the Codes will be 1-2-3 or 701-702-703. Pros/Cons, yes. 

I dropped down into my hosted Feature Layer > Data > Table view > right-click Category > Calculate > SQL. I tried first the coding option, ..WHEN Category = 708 THEN 701...   Didn't work. Tried single quotes around Codes. Still didn't work. Kept getting the message Invalid sql expression.

Next I tried the coding option, ... WHEN Category IN (708) THEN 701...   That worked just fine.

I appreciate you taking the time to teach me different SQL options that addressed my original question.

Thanks! 😄