Python Relate New Table Records

BradOleson — Tue, 13 Aug 2019 15:55:58 GMT

I have a feature class that has a related table that tracks user edits. The manual way to perform the task is:

The user performs an edit on the polygon
The user adds a new record to the related table and updates attributes accordingly (type, edited by, edited date, etc.). This is done in the Attribute Editor window using the interface provided for adding a related record. The relationship is tracked via the polygon ID, which the user copies into the new related record.

I have written a Python script that automates the record creation tasks. It works as follows:

User edits one or more polygons
User runs the script
Script creates the new records in the edit table, enters the polygon ID and the script dialog provides the remaining update info for the script to fill in the other necessary fields

What I need to know is how, in Python, to update the relationship for the newly created records to relate them back to their respective poly using the poly ID. Currently, when the updated poly is selected and viewed the newly added record is not shown in the Attribute Editor dialog as a related record.

Can anyone provide to me where to go for the missing link? The usual Google search didn't really yield the secret to me when I started digging yesterday.

Here's the code as it exists now:

**************************************

import arcpy, arcgisscripting, sys, os, time, datetime, getpass, shutil, smtplib
from arcpy import env
from time import strftime

mxd = arcpy.mapping.MapDocument("CURRENT")

# From dialog, get the table field default values. To set different defaults, use the
# parameters dialog inside ArcMap/toolbox interface.
structuresFC = arcpy.GetParameterAsText(0)
structureEditLogTable = arcpy.GetParameterAsText(1)
qcFlag = arcpy.GetParameterAsText(2)
userComments = arcpy.GetParameterAsText(3)
modifiedBy = arcpy.GetParameterAsText(4)
modifiedDate = arcpy.GetParameterAsText(5)
lastEditedUser = arcpy.GetParameterAsText(6)
lastEditedDate = arcpy.GetParameterAsText(7)
clearSelected = arcpy.GetParameterAsText(8)

# Determine query date
queryDate = datetime.datetime.strptime(lastEditedDate, '%m/%d/%Y').strftime('%Y-%m-%d')

# Determine if any edits have been performed on the Structure Edit Log today; if so, determine the latest time of the updates
timeQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " 00:00:00' and MODIFIED_BY = '" + modifiedBy + "'"
arcpy.SelectLayerByAttribute_management(structureEditLogTable, "NEW_SELECTION", timeQuery)

# Determine if there is a selection; if there is, edits have been made today
describeTable = arcpy.Describe(structureEditLogTable)
if describeTable.FIDSet == '':
anyEdits = "No"
timestampList = []

    # Set the timestamp for the list to Midnight today - no edits have taken place yet
    timestampList.append("00:00:00")
    queryTime = "00:00:00"
else:
    # Edits have been made, read the times of the selection set into a list
    fieldsSEL = ["LAST_EDITED_DATE"]

timestampList = []

    with arcpy.da.SearchCursor(structureEditLogTable, fieldsSEL) as srchCursor:
        for row in srchCursor:
            timestamp = (row[0])
            timestampList.append(timestamp)
    lastEditTime = max(timestampList)
    queryTime = (lastEditTime).strftime('%H:%M:%S')

# Clear the selections to prep for next phase
arcpy.SelectLayerByAttribute_management(structuresFC, "CLEAR_SELECTION")
arcpy.SelectLayerByAttribute_management(structureEditLogTable, "CLEAR_SELECTION")

# Create select query based on current user being last edited user and edit time being greater than 00:00:00
selectionQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " " + queryTime + "'"

# Select records from the Structure Edit Log table that were edited by the user in this edit session
arcpy.SelectLayerByAttribute_management(structuresFC, "NEW_SELECTION", selectionQuery)
arcpy.RefreshActiveView()

# Describe the table state to determine if records are selected
describeTable = arcpy.Describe(structuresFC)

try:

    # Test to see that there is a selection set first. If not, message the user and exit.
    if describeTable.FIDSet == '':
        arcpy.AddMessage("No features are selected in the Structures Feature Class; exiting!")

else:

        # Set the parameters for the selected features *only*
        fieldsFC = ["STRUCTUREID"]
        fieldsSEL = ["STRUCTUREID", "QC_FLAG", "COMMENTS", "MODIFIED_BY", "MODIFIED_DATE"]

try:

            # Set up a cursor to get the current StructureID in the FC
            with arcpy.da.SearchCursor(structuresFC, fieldsFC) as srchCursor:
                for row in srchCursor:
                    currentStructureID = row[0]
                    with arcpy.da.InsertCursor(structureEditLogTable, fieldsSEL) as insertCursor:

                        insertCursor.insertRow((currentStructureID, "Y", userComments, modifiedBy, modifiedDate))

                del insertCursor
                del srchCursor, row

except RuntimeError as err:

            # This will run if the user is not currently in an edit session
            if "cannot be updated outside an edit session" in err.message:
                arcpy.AddMessage("Not currently in an edit session. Exit tool, start an edit session and rerun the tool!")

else:
raise err

        selectionQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " 00:00:00' and EDIT_ID IS NULL"
        arcpy.SelectLayerByAttribute_management(structureEditLogTable, "NEW_SELECTION", selectionQuery)
        arcpy.RefreshActiveView()

        fieldsSEL = ["OBJECTID","EDIT_ID"]
        with arcpy.da.UpdateCursor(structureEditLogTable, fieldsSEL) as updateCursor:
            for row in updateCursor:
                row[1] = row[0]
                updateCursor.updateRow(row)

del updateCursor, row

except Exception as e:
arcpy.AddMessage("Exception")
arcpy.AddError(e.args[0])

if clearSelected == "true":

    # Clear selection and refresh the map
    arcpy.SelectLayerByAttribute_management(structuresFC, "CLEAR_SELECTION")
    arcpy.SelectLayerByAttribute_management(structureEditLogTable, "CLEAR_SELECTION")
    arcpy.RefreshActiveView()
else:
    arcpy.RefreshActiveView()

Re: Python Relate New Table Records

BradOleson — Tue, 13 Aug 2019 16:46:35 GMT

I think I may have answered my own question, but here's my sanity check...in case anyone is reading or has read this:

I need to add the requisite records to the relationship table providing the structure ID and its new related Edit_ID. I'm assuming like any other table in SDE, there shouldn't be a problem doing this via the Insert cursor, probably in the next line of my code. I'll be giving this a try, but if anyone has any caveats to this I'd appreciate your input!

Thanks!

Brad

Re: Python Relate New Table Records

JoeBorgione — Tue, 13 Aug 2019 16:56:36 GMT

Can you just use Editor Tacking?

Enabling and managing editor tracking—Help | ArcGIS Desktop ('classic desktop')

Enable Editor Tracking—Data Management toolbox | ArcGIS Desktop (pro)

topic Re: Python Relate New Table Records in Python Questions

Python Relate New Table Records

Re: Python Relate New Table Records

Re: Python Relate New Table Records