How do I use AddRelate_management in a standalone python script?

[JesseTemplin2]

Hello,

I have a python script that creates a feature layer and standalone table, saves them to a file geodatabase, then adds them to a map. Next I would like to relate the feature layer to the standalone table.

If I open the project in ArcGIS Pro and run the following AddRelate command in a Jupyter window it works.

arcpy.AddRelate_management("Feature", "feature_id", "Table", "table_id", "Feature", "ONE_TO_ONE")

But if I use the same command from Spyder, I get the following error:

ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Layer Name or Table View: Dataset Feature does not exist or is not supported
Failed to execute (AddRelate).

I have also tried calling AddRelate with full paths to the feature and table in the file geodatabase using os.path.join(path-to-gdb, "Feature"). In that case it doesn't throw an error, but when I open the project the feature and table have been removed from the map. If I manually add them back in from the gdb, the relate is not present.

Am I doing something wrong, or have I entered bug territory?

Thanks!

[JoeBorgione]

Perhaps you could post what works and what does not in a code edit window?

I'm not a fan of using os.path, but lots of folks are.  I prefer just copying and pasting the full path of a feature class or table into a script.  In Pro, open a catalog pane while in a map view.  Highlight the object you want and click Copy Path.

use it this way:

fc = r'M:\HansenSQL\HansenGeocoding\HansenGeocoding.gdb\ADDRESS_Geocoded'

[JesseTemplin2]

At the moment I don't have a self contained example project I can drop into a code block. The feature layer pipeline looks like this: MakeQueryLayer -> CopyFeatures -> MakeFeatureLayer -> Delete -> addDataFromPath -> save. And the standalone table part looks like this: MakeQueryLayer -> CopyRows -> MakeTableView -> addDataFromPath -> save.

Following your advice, I noticed the slashes in my path were facing the other direction. Aside from that they were the same, and fixing it made no difference (although probably good to have corrected them).

Each of the above pipelines run in a function that assigns aprx = arcpy.mp.ArcGISProject(...), creates the layer, then saves aprx. I think this was interfering with the aprx variable in the outer scope. If I reassign it after creating the feature and table, but before AddRelate, then save, I don't lose the feature or table. But the relate still isn't getting applied.

[JoeBorgione]

I noticed the slashes in my path were facing the other direction. Aside from that they were the same, and fixing it made no difference (although probably good to have corrected them).

Slash direction should'nt matter to python, what does matter is if you don't use the r in front of it so the interperter reads it as raw text.  Otherwise you have to escape the slash with another slash and I don't like doing that.

[DanPatterson]

A Jupyter notebook and the meek python window within Pro are all knowing about the environment within Pro.  

A separate python IDE, such as the great Spyder, are not part of the "collective" and have to be "told" where things are and what things are.

So as @JoeBorgione suggests, do the path copy or set your environment within a script

import arcpy
pth = arcpy.env.workspace = "c:/temp"
pth
'c:/temp'
arcpy.env.workspace
'c:/Temp'

[JesseTemplin2]

Yeah, I've learned this the hard way. The standalone script is already doing some extra gymnastics to work outside of ArcGIS Pro. The features getting deleted was my mistake, but still no luck with AddRelate after confirming the paths are correct.

[JesseTemplin2]

I've reduced my standalone script to a reasonable size. It still requires an existing project with a feature layer and standalone table, since I don't know how to make a feature layer from scratch in arcpy (MakeFeatureLayer takes a feature layer as input).

import arcpy, os

def main():

    report_path = r"C:\sandbox\ArcGIS\Demo"
    aprx_path = r"C:\sandbox\ArcGIS\Demo\Demo17.aprx"
    
    # feature and table paths for arcgis pro
    # emitter = "Emitter"
    # table = "Location_Table"
    
    # feature and table paths for spyder
    emitter = r"C:\sandbox\ArcGIS\Demo\Demo17.gdb\Emitter"
    table = r"C:\sandbox\ArcGIS\Demo\Demo17.gdb\Location_Table"

    # set workspace variables
    os.chdir(report_path)
    arcpy.env.overwriteOutput = True
    arcpy.env.workspace = os.getcwd() # report_path
    print(f"arcpy.env.workspace: {arcpy.env.workspace}")
    
    # confirm layers exist
    aprx = arcpy.mp.ArcGISProject(aprx_path)
    emitter_layer = aprx.listMaps()[0].listLayers("Emitter")[0]
    location_table = aprx.listMaps()[0].listTables("Location_Table")[0]
    print(emitter_layer.name)
    print(location_table.name)
    
    # in arcgis pro this works
    # in spyder this runs without errors, but the relate doesn't get applied
    arcpy.AddRelate_management(emitter, "emitter_id",
                               table, "emitter_id",
                               emitter, "ONE_TO_ONE")
    aprx.save()
    
    
if __name__ == "__main__":
    main()

The existing project structure looks like this:

I'll also submit this to ESRI tech support and follow up here if they have an answer.

[JesseTemplin2 · Accepted solution]

Tech support confirmed this is a bug.

BUG-000137606

[JeffMoulds]

I might have a workaround for this. The following script will add a relate to a layer in a ArcGIS Pro project. This should work if run outside of ArcGIS Pro, like in a Python IDE or the python command line. The relate will get saved with the ArcGIS Pro project. It uses arcpy CIM, which you can learn about here. 

aprx = arcpy.mp.ArcGISProject(r"C:\temp\RelateTest_NoRelate.aprx")
m = aprx.listMaps()[0]
lyr = m.listLayers()[0]

# Get the layer's CIM definition
lyrCIM = lyr.getDefinition('V2')

# Create a new CIM data connection to the feature class to be used in the relate
dc = arcpy.cim.CreateCIMObjectFromClassName('CIMStandardDataConnection', 'V2')
# Specify the geodatabase
dc.workspaceConnectionString = "DATABASE=c:\\temp\\Data\\FGDB.gdb"
# Specify the workspace type
dc.workspaceFactory = "FileGDB"
# Specify the dataset name
dc.dataset = "us_cities"

# Create a new CIM relate
r = arcpy.cim.CreateCIMObjectFromClassName('CIMRelateInfo', 'V2')
# Specify the data connection to the data - use the one you created above
r.dataConnection = dc
# Set all the other relate properties
r.foreignKey = "ST_ABBREV"
r.primaryKey = "STATE_ABBR"
r.cardinality = "esriRelCardinalityOneToMany"
r.name = "cim_relate"

# Relates must be stored in a list, even if there is just one
my_relates = []
my_relates.append(r)
lyrCIM.featureTable.relates = my_relates

# Set the layer's CIM definition
lyr.setDefinition(lyrCIM)

# Save a copy of the project
aprx.saveACopy(r"c:\temp\RelateTest_CIM.aprx")

print('done')

[JesseTemplin2]

Thanks for following up. We ended up getting the Standard license which let us use CreateRelationshipClass. That, and we later found out the client is using ArcMap, so the hole thing has gone through a number of revisions.