Joins are changing name every time I run my script

[Anthony_R_McKay]

I'm currently using a Virtual Feature class, adding a field from a spatial
join, then exporting the result to Excel.

I'm deleting the virtual fc and join, by running

arcpy.management.RemoveJoin(virtual_FC,countryFC)

and

arcpy.Delete_management(virtual_FC)

..but despite me closing everything down, this - keeps happening.

I need to run four different 'join' and 'calculate' operations in my script, so this is really throwing a spanner in the works.

I am turning ArcGIS Pro and PyCharm off and on - so I've got no idea where this
information about the previous runs is being stored.

[DanPatterson]

print the field being used for CalculateField to check its name.  It can't have punctuation in it and it must be a field in the target table that is being used

[Anthony_R_McKay]

Thanks Dan,

The script worked fine first time it was run, "....Join.COUNTRY" worked  - but since then the join fields became "....Join_1.COUNTRY" with the number going up with each run.

I'm nearly at "....Join_20.COUNTRY" now!

The only way I can get the script to run now is by changing the name of the input field to predict the next change.

I'm at a loss to find out how these previous versions are being referenced.

I need to re-set this somehow.

[DanPatterson]

Is it a field mapping issue?

Add Spatial Join (Data Management)—ArcGIS Pro | Documentation

Mapping input fields to output fields—ArcGIS Pro | Documentation

[Anthony_R_McKay]

Hi Dan,

I've been staring at this documentation until, literally, my head hurts.

It's crazy, my script is running better than I hoped - but I keep having to 'up' the "Join_X" number every time I run.

I just tried it on another FC  -started at Join. and now it's Join_01.


I want to string four joins together using different field classes - so have to get to the bottom of this.

Thanks for you help - but I'll sleep on it for now - and take some paracetamol.

[DanPatterson]

When doing a join, if a field exists in the source and destination table that has the same name and is not the join field, then _0X is appended to the field name from the source table leaving you with two fields with the same base name (eg county and count_0X).  That is what the field mapping option is set up to do.  It allows you to specify what to do with duplicate names that you want to appear in the same column

[Anthony_R_McKay]

Thanks Dan,

I can see the logic in this, but am having no luck with my efforts so far.

I have an issue with 

arcpy.DeleteFeatures_management(virtual_FC)

Deleting all the  data in my FC source file.

So, I am using 'sacrificial' versions on the source file to be on the safe side - using different file names meant that I only had to add  _1, _2, _3  to three of my join parameters.

So, for the time being, I'll use this work-around, and just keep changing the in put FC feature names.

Thanks, Dan, for your help.

[RichardDaniels]

For filenames used with a datafactory constructor they often support wildcard characters that support the addition of incremental numbers on a filename. Other option is to add code to delete temp files before each run.

[DuncanHornby]

Anthony,

You are not the only one affected by this behaviour. I too noticed if I make and break a single join created by the new Add Spatial Join tool introduced in ArcPro 3, you get that ever increasing table name join AddSpatialJoin_X where X is an ever increasing number.

It makes using the tool in modelbuilder or a scripting environment impossible to use as you can't predict what the next increment is. I'm tagging @KoryKramer  into this thread so he can rattle the cage and see what drops out!

To the developers behind this tool:

1. Is this really the intended behaviour?
2. If so where is this information stored?
3. Is it accessible so developers can build scripts to be able to construct valid expressions for field calculates?
4. Unless I'm missing something, in an ideal world if I make then break the join I don't expect to see an ever increasing number, I think it should always be _AddSpatialJoin.

This seems like a very powerful and useful tool but as it stands I can't use it as it breaks continuity in models, especially if they are exposed as model tools.

[DuncanHornby]

Just want to add to this thread, I discovered this technical article on the ESRI support site which enables developers to identify if layers have joins (including a spatial join). Unfortunately for ArcPro 3.0.2 it bombs out but the fix is easy!  I have left feedback for the page to inform them the page is offering a bugged solution, lets hope someone reads it and corrects the error. For the record it is not source_table its SourceTable.