ArcGIS Pro query layer loses selected workspace

[julian_svcs]

I have a number of query layers in my ArcGIS Pro map. When I try and perform and arcpy code on these query layers it loses the connection to the workspace.

A simple task I am trying to do is update the Display Field for all these query layers (see code below).

When I run this, I get the datasource missing red exclamation mark next to my query layers. When I go to the Source in the layer's Properties and view the query, the connection to my enterprise gdb is still showing there but the code syntax colours are not shown (only white text) (I also can't select the List of Tables - ERROR: The selected workspace is invalid):

If I select another database connection, then reselect my correct connection it restores the connection again:

Why does it loose the database connection? I checked the CIM in an exported lyrx file for both and the connections seem to be the same.

Is there at least a way I can automate the connection re-connection with python instead of having to do the above for every layer?

Python code to update the displayField:

aprx = arcpy.mp.ArcGISProject("CURRENT")
for m in aprx.listMaps():
    for lyr in m.listLayers():
        if lyr.isFeatureLayer:
            cim_lyr = lyr.getDefinition("V2")
            cim_lyr.featureTable.displayField = "NAME"
            lyr.setDefinition(cim_lyr)

Thanks.

Julian.

[julian_svcs]

I think I found the issue with this. When I run the display field and same the layers' definition, the cim loses the 'queryField' and 'featureTemplates' values. 

As a workaround for now:

1. create a lyrx file
2. update the display field
3. update the queryFields and featureTemplates using the key, values from the lyrx file

This seems to restore the workspace connection.

[MickHuska-NAU]

Hi Julian,

I have the same issue using CIM v2 to alter datasources on query layers. This method drops some information necessary for the workspace connection. The only way I've been able to find the information is using the lyrx file like you said to restore the queryFields.

This is a bummer because CIM is the only efficient way to automate changing datasources on APRX files with lots of layers, and it is not practical to maintain a copy of the queryFields for every layer needed. It would be helpful if anyone at ESRI knows if this a bug or expected behavior.

-Mick

[JeffMoulds · Accepted solution]

This is has been fixed ArcGIS Pro version 3.2. In the Updating Data Sources help topic, go to the CIM Section, and check out the third code sample:

# Reference project, map and layer 
p = arcpy.mp.ArcGISProject(r'C:\Projects\USA.aprx')
m = p.listMaps('USA')[0]
l = m.listLayers('States')[0]

# Get the layer's CIM definition
lyrCIM = l.getDefinition('V3')         

# Update the sql query where clause for the layer
sql = lyrCIM.featureTable.dataConnection.sqlQuery
newsql = sql.replace("WHERE SUB_REGION = 'Mtn'", "WHERE SUB_REGION = 'Pacific'")
lyrCIM.featureTable.dataConnection.sqlQuery = newsql
    
# Set the layer's CIM definition
l.setDefinition(lyrCIM)

The above sample changes the sql query, but other properties of the Query Layer, such as the Display Field, can also be updated using similar logic.