Setting Definition Query on Multiple Layers Using Python

[SepheFox1]

I am using a simple python script to add a definition query to all the layers in my map. The definition query appears to be successfully added to each layer, but isn't being applied until I open the definition query properties  of each layer individually and then hit OK. Is there something else I should be adding to my script to actually apply the query to each layer? Here is my script:

aprx = arcpy.mp.ArcGISProject("CURRENT")
m = aprx.listMaps("Staking Job")[0]
for lyr in m.listLayers():
    if lyr.supports("DEFINITIONQUERY"):
        lyr.definitionQuery = "st_workorder_ref_guid = '19205_SF'‍‍‍‍‍" 

And here is a screenshot showing the contents when I open the query definition properties:

Again, the def query gets added, but doesn't actually get applied until I open the properties and hit OK without making any changes.

Additional info:

I tried the same thing in ArcMap (script was slightly different), and although it did update the definition query, I also got ArcMap Drawing Errors, with the message: "Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '‍'.]". If I add the definition query manually, although it looks identical to the one created by the script, I don't get any error and the layer draws normally.

[RhettZufelt]

What version are you using, and is the script "ending", or still processing something else?

Seems as if it waits to refresh until the script has finished.

I used your script in 2.9 both python window and Notebook.

the definition query was applied, and the view refreshed immediately once the script ended.

R_

[SepheFox1]

The script completes. I am using 2.8.3, and ArcMap 10.8.1.

[RhettZufelt]

No clue then.

I did see one post where they set the reference scale at the end to force a refresh of the map.

Not sure it that would work for you or not.

m.referenceScale = 80000
m.referenceScale = 0        # resets to None

Upon testing, setting it to 0 refreshes the map, even it it is already None.

R_

[SepheFox1]

Thanks for the suggestion. Unfortunately a map refresh does not activate the definition query. It seems they can only be activated by opening the layer properties, clicking on the definition query, and then clicking OK.

[NSCHLAGE]

Hi there,
Its been a couple months since your post, not sure if this will help you but I had the same issue and this was the only forum post I found related. I was able to fix the issue by tinkering around with these two documentation pages:
https://pro.arcgis.com/en/pro-app/latest/arcpy/mapping/layer-class.htm
https://thispointer.com/python-check-if-value-exists-in-list-of-dictionaries/

The ESRI Layer class page, specifically the last example, says the "layer.updateDefinitionQuery(dql)" line is required. I don't know why as I can filter some layers without this using layer.definitionQuery = "...", but update function fixes the issue of needing to go into some layers' properties and click 'Ok'. You'll also need the loop to set all of the layers existing queries to off ('isActive' = False) to avoid a 'multiple active queries' error with this snippet.

for dq in dql:
dq['isActive'] = False

You need to query through the list with list indexing to a specific definition query dictionary entry that you want to turn on (if its an existing one), and if its a new one your adding query through to make sure you're not adding a duplicate. This isn't needed I suppose if you currently have no existing queries and don't plan to run more, but a check for this will future proof your code a bit and make it more easily reusable if you want to search for other work orders on the same layer.

Once you've queried the list of defQuery dictionaries you can then query each element in that dictionary (the 'sql' for the query, and the 'isActive' to turn it on). Then print the definition query list to make sure your changes stuck, and use that new updated list as the arguments in the layer.updateDefinitionQuery(new_dql) and that should do the trick.

I tried to modify the code I got working for my application to match your variable names (screenshot attached). Might take some extra tweaking, but something like this should work for you if you still need a solution.

[NSCHLAGE]

I guess you wouldn't need mapdoc as an argument for the custom function though. I did my layer list assignment inside the function and needed it myself, forgot to remove that argument when I converted to fit your example.

[RikiKobayashi]

Would you be able / willing to share your code? I am getting a similar error, and this post is the only one pertinent that I have found thats related to my issue?!