Trying to use arcpy find duplicates in layers from mxd.

StudentFernie

Basically i want to find duplicate records from my attribute tables in my mxd not my data base because my mxd. has several definition query's i would like to preserve. I am essentially trying to script this query  

[FIELD_NAME] In (SELECT [FIELD_NAME] FROM [TABLE_NAME] GROUP BY [FIELD_NAME] HAVING Count(*)>1 )

Any tips as to why this wont cooperate? I either get a cannot combine string and layer objects error or an invalid query error because it says my layer does not exist because it wont find the table related to my mxd. 

Cheers,  

# Set workspace environments. Workspace must be a connection file to an ADMIN (sde) connection
arcpy.env.workspace = r"\\fs\GIS\admin\temp_connect_files\COF_temp.sde"
arcpy.env.overwriteOutput = True
print "workspace environment connected"
# Set workspace variable
workspace = arcpy.env.workspace

# Local variables:

mxd = arcpy.mapping.MapDocument(r"P:\Joey_Plessis\service_cards\pdf_map_automation_script\DEFAULT_bc_one_call.mxd")

df = arcpy.mapping.ListDataFrames(mxd,"*")[0] #DONT WORRY ABOUT THIS PART

layer = arcpy.mapping.ListLayers(mxd, raw_input("enter layer name"), df)[0]

field = raw_input("enter field name")

print layer
print field
where = field +" " + "In" + " " + "(SELECT" +" "+ field + " "+ "FROM" +" " + "layer" + " "+ "GROUP BY" +" " + field +" "+ "HAVING Count(*)>1 )"
print where

arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION" ,where)

JoshuaBixby

Firstly, it would be helpful to community members if you formatted the code in your post: /blogs/dan_patterson/2016/08/14/script-formatting?sr=search&searchId=02fa3b47-d128-40af-9c35-fb69e3ab521f&searchIndex=0‌.  Secondly, I suggest you take a look at Python Format String Syntax.  The line where you are building your SQL WHERE statement is very difficult to read with the long string (no pun intended) of concatenations.

You say the code isn't cooperating, what are you expecting it to do and what is it actually doing?