Joining a table and calculating a field in ArcGIS Pro SDK

[GyanendraGurung]

Joining a table is easy. In ArcGIS Pro, you simply right-click a table, goto "Join and Relate" and then select "Add Join". This takes you to a Geoprocessing tool where you select the input join field and output join field and voila, your table is joined !

1. But, when it comes to using the "management.AddJoin" with ExecuteToolAsync(), you actually end up with a new table with the two tables joined together: 

1. var argsAddJoin = gp.MakeValueArray("C:\\Project\\Sample_database.gdb\\Input_Table", "INPUT_ID", "Table_to_Join", "OUTPUT_ID", "KEEP_ALL");  
2. var resultAddJoin = await gp.ExecuteToolAsync("management.AddJoin", argsAddJoin);  
3. string resultingJoinedTable= resultAddJoin.ReturnValue;  

2. Then, if you want to recalculate a field in the input table, you would have used the following python code:

1. // Calculate Field //  
2. arcpy.management.CalculateField("Resulting_Joined_Table", "Resulting_Joined_Table.FIELDNAME", "!Table_to_Join.FIELDNAME!", "PYTHON3", '', "TEXT")  

and expect to use the following code in C#:

1. var argsCalculateField = gp.MakeValueArray("Resulting_Joined_Table", "Resulting_Joined_Table.FIELDNAME", "!Table_to_Join.FIELDNAME!", "PYTHON3", "", "TEXT");  
2. var resultCalculateField = await gp.ExecuteToolAsync("management.CalculateField", argsCalculateField);

But, this won't work. You actually have to reference the original input table that you had "wished to join" in as the input table's name:

1. var argsCalculateField = gp.MakeValueArray("Resulting_Joined_Table", "Input_Table.FIELDNAME", "!Table_to_Join.FIELDNAME!", "PYTHON3", "", "TEXT");  
2. await gp.ExecuteToolAsync("management.CalculateField", argsCalculateField);

[RichRuh]

Joins are read-only, although you can edit the underlying tables that make up a join.  This is by design.

Note that instead of using geoprocessing to create a join, you can also call the low-level geodatabase routines directly, as described here.

[GyanendraGurung]

Hi Rich, 

Once again, thank you for you kind reply. Ah, I see. Joins are read-only. That makes sense. 

Coming from python to C#, I use alot of these processing tools. Now, I think it is a bad habit (because it is slow most of the time). Thank you for sharing an alternative (and better way) to perform this task. I shall definitely explore the low-level geodatabase routine and make it a habit to find similar alternatives for other routine geoprocessing tasks.

Once again, many many thanks again. 

Kenny 

[LesleyBross1]

I used Kenny's code to implement a similar solution. A few notes:

1. The return value from the AddJoin GP tool is the name of the layer created by the join. This string should be used as one of the inputs to the CalculateField tool. The code example has it quoted as an actual string.
2. Be sure that you supply a  GPExecuteToolFlags value that adds the join layer to the Pro display. I normally have this disabled because I'm working in a non-interactive mode, but if the join isn't in the map view then it can't be used for the calculation.
3. To clean up when you are done, just use the Map.RemoveLayer api to remove the join layer from the map. Again, use the return value from the AddJoin tool to get the layer name.

[AnupamKumar]

Hi Rich, 

I am of the idea that Geoprocessing tools AddJoin and CalculateField would give a better performance than the low-level geodatabase routines like Join, QueryDef, QueryTable which are read only. We have to apply QueryFilter in a loop and Update the values. Is that i am missing anything from my side for this scenario.