featurelayer.calculate variable append in calc_expression

[RhettZufelt]

I am trying to update rows of a field on a hosted feature layer.

fl.calculate(where="1=1", calc_expression={"field": "Description", "value" : appendval })

fl.calculate(where="1=1", calc_expression={"field": "Description", "sqlExpression" : "SignShape"})

Both of the above work.  The first one will calculate the "Description" field to the value of the variable appendval.

The second one will calculate the "Description" field with the value from the "SignShape" field.

What I need to do is calculate the "Description" field with the value from the "SignShape" field and the value from appendval concatenated to it as shown below.

fl.calculate(where="1=1", calc_expression={"field": "Description", "sqlExpression" : "SignShape" + newval})

However, it seems I can use the "sqlExpression" to utilize values from the field itself, OR, "value" to utilize a variable or other value.  

Any idea on how I can combine the two to get the desired results?

value from "SignShape" field = 'Generic Sign'
appenval = 'Updated'

the value I want to insert would be:
"Generic Sign - Updated"

Thanks for any suggestions,

R_

[RhettZufelt]

How about some better documentation on how this is supposed to work?

In the arcgis.features module, show a single format as:

{“field” : “<field name>”, “value” : “<value>”}

 But shows two examples:

calc_expression={"field": "ZONE", "value" : "R1"}
calc_expression={"field": "A",  "sqlExpression" : "B*3"}

And references the REST API shows the same two examples, but shows the syntax format as:

"calcExpression":[{"field": <fieldname>, "value" | "sqlExpression": <value> | "expression"},...]

Which suggests the expression can be Field/Value  OR  sqlExpression/Value  OR  expression,...

Is "expression" available in the API, and if so, does anyone have example of "expression" format, since that doesn't seem to be listed anywhere?

Thanks,

R_

[EarlMedina]

Hi,

There is documentation, but perhaps it's not completely obvious. Since Enterprise/AGOL relies on ArcGIS Server, this documentation on supported SQL operations applies:

Enforce standardized SQL queries—ArcGIS Server | Documentation for ArcGIS Enterprise

Additionally, this documentation for Pro applies:

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

[EarlMedina · Accepted solution]

Hi there,

I have usually avoided calculate because it is much slower than using dataframes and  edit_features to perform updates, but here's the syntax for string concatenation (there are 2 ways I know of):

fl.calculate(where="1=1", calc_expression={"field": "test", "sqlExpression" : 'Activity || Description'})

OR

fl.calculate(where="1=1", calc_expression={"field": "test", "sqlExpression" : 'CONCAT(Activity, Description)'})

This examples assumes you are calculating a field "test" to be the concatenation of fields "Activity" and "Description." In your case, it seems you actually have a variable?

In that case, you have to do some string substitution. Here I make use of the f-strings (introduced at Python 3.6):

fl.calculate(where="1=1", calc_expression={"field": "test", "sqlExpression" : f"CONCAT(Activity, '{newVal}')"})

Hope this helps!

[RhettZufelt]

Thanks for the suggestions.  I tried the .format method earlier but couldn't get it to work.

However, the f-string method seems to be working as expected.  Thanks again for that!!!!!

the calculate works pretty well for me, except of course when the AGOL servers throw a bad gateway error right in the middle (still not sure what/how/why that means, but have modified my script(s) to keep track of what has been updated and keep trying until it finishes).

I looked at dataframes a bit, but that seemed even more confusing that the basic calc function, and not sure how to approach it using edit and df's.

Basically, I have a dictionary of Code[Values].  I need to go trough every matching record, in the FC, if the Code matches my MUTCDcode field, then populate the Description field with the dictionary values as such:

for mc in MCodeList:
          if DescrDict[mc]:
                    sqlclause = "MUTCDcode LIKE 'D3-1 %'"
                    (fl.calculate(where=sqlclause, calc_expression={"field": "Description", "value" : DescrDict[mc]}))

which is working fine.  However, this will overwrite any data that is currently in that field.

For certain MUTCDcodes (LIKE 'D3-1 %'), If there is already a value in the Description field, I need to keep the data that is there and append the text result of the newValue variable to the end of the existing description.  

Does this still sound like something better handled with edit and df's?  If so, any chance you can point me towards a similar example (pull data from dictionary to update cell values)?

Thanks again for the help,

R_