Use SQL statements instead of Arcade in Calculate Field Tasks for ArcGIS Solutions

[BrianShepard]

A task to "Create new Gravesite IDs" in the Cemetery Management Solution uses the Calculate Field tool and is prepopulated with an Arcade Statement. In my case, I have ~18,000 gravesites and the process timed out after a couple of hours. Using a SQL statement instead of the Arcade statement (prepopulated by Esri) took seconds to complete.

I'd suggest Esri follow its own best practices and use SQL instead of Arcade for such tasks.

Optimize web feature layers—ArcGIS Pro | Documentation

[DanielWickens]

@Brian - Thanks for the feedback, I am aware of this limitation with the Tasks in the Cemetery Management solution's ArcGIS Pro project. We will definitely consider re-writing the tasks with SQL statements where possible in a future release. One issue is that SQL calculations can't be used on feature services when they're participating in a join. For example, one of the tasks involves joining Gravesites and Burials and calculating parent Global IDs into the Burials table. We can't use SQL for that calculation.

However, you can use SQL to just calculate the Gravesite IDs themselves. For now, I would recommend re-purposing the task in ArcGIS Pro to use SQL, or you can run the SQL calculation on the field from the Data tab in ArcGIS Online or ArcGIS Enterprise. 

[DanielWickens]

@BrianShepard - Thanks for the feedback. I am aware of this limitation with the tasks in the Cemetery Management solution's ArcGIS Pro project. We'll consider replacing the Calculate Field expressions with SQL statements when possible in a future version of the solution. One limitation is that calculating field values on joined tables is not supported with the SQL option. For example, in one of the task steps, we instruct the user to join Gravesites and Burials in order to calculate parent Global IDs into the Burials table. For that calculation, we can not use SQL.

But in your case, you should be able to calculate the Gravesite ID with SQL. Either by re-purposing the calculated expression in the task to use SQL instead of Arcade. Or you can also run the calculation directly from the Data tab of the feature layer's item details page in ArcGIS Online or ArcGIS Enterprise.

[BrianShepard]

@DanielWickens Unfortunately, I'm at the point of calculating the Parent Global IDs for Burials and it's taken most of the day to get through a few thousand records. I have more than one burial per gravesite, but can't perform a 1:many join, and I can't use a SQL expression. It takes a couple hours to churn through a few thousand records before Calculate Field times out and I have to select a subset of null Parent Global IDs and rerun Calculate Field on that subset. It will take several days to populate Burials and Owners at this rate. 

I'm doing this as a proof of concept to get buy in for additional AGO licenses and migrate our cemetery management to this Esri Solution. I will have to go through much of this again, once I have the go ahead and can dedicate the time to cleaning up our data. I'd love to find a more efficient way of doing this next time. Please let me know if you have any suggestions. It's a lot of effort to calculate a field when Gravesite ID is already being used to join Gravesites to Burials.

[DanielWickens]

@BrianShepard - I would do everything locally first and then append the data to the feature services in AGOL.

1) Export and download the Gravesites and Burials feature layers as FGDBs.

2) Append your data to the local FGDB feature classes and perform the tasks of calculating the Gravesite ID and Parent Global ID fields.

3) Export/zip the calc'd and prepped local data to a FGDB.

4) Use the 'Update Data' function on the Gravesites and Burials item details pages to append your FGDB data into the feature services in AGOL.

As long as you've correctly calc'd the Parent Global IDs, the relationship between Gravesites and Burials will be honored. Let me know how it goes.

[BrianShepard]

@DanielWickens, that worked. Although it involved more steps, it was tremendously more performant. I think that should be the preferred method for initially populating the data, updating AGO only after that. Going forward, one-off updates can easily be done in AGO, but updating the thousands of records that cemeteries can have doesn't seem viable to do in ArcGIS Pro on the AGO feature layers/tables. I'm going to update my other post with the local process I followed to close out that thread. Thanks for your help with this.