Pulling newly created records from one feature class to another

Question

Hi,

I am working on a project that requires me to pull newly created records that are populated in a bronze dataset into a silver dataset. I am working to automate this process and with my limited python knowledge have created a model builder, but am stuck at a particular point in the logic.

I am unsure how to use the Select by Attribute, or Select by Date functions with a variable date input. I need to find a way to take the date of the oldest record of the silver dataset, then go to the bronze data set and pull everything that is newer then that date.

I've been toying around with creating a new field on the bronze data set indicating its been pulled or not, then pull all records that don't have a value in this field, then write in this field that its been pulled. But was wondering if there was a more elegant way.

Again, I have limited python knowledge.

Thanks for any direction.

RogerDunnGIS · Answer

Would it work better just to use the Append tool?  The Append tool allows you to merge changes from bronze to silver using the "Update Options" group.  Specify one or more fields in bronze and silver which together, indicate the same feature.  These may or may not be unique ID fields, but could be combination of date, name, and type fields or something.

JakeSkinner · Answer

Hi @ericcrossen,

You could do something like below:

import arcpy

# Variables
silverDataset = r"c:\Database Connections\GIS.sde\VECTOR.silver"
bronzeDataset = r"c:\Database Connections\GIS.sde\VECTOR.bronze"
silverDatasetDateField = 'inspectdate'
bronzeDatasetDateField = 'editdate'

# Create list of all dates
dates = []
with arcpy.da.SearchCursor(silverDataset, [silverDatasetDateField]) as cursor:
    for row in cursor:
        dates.append(row[0])
del cursor

# Sort list
dates.sort()

# Get most recent date
mostRecentDate = dates[-1]

# Select records from bronze dataset
arcpy.SelectLayerByAttribute_management(bronzeDataset, "NEW_SELECTION", f"{bronzeDatasetDateField} >= '{mostRecentDate}'")

If the data is in a File Geodatabase, the query in the Select Layer By Attribute would be slightly different:

arcpy.SelectLayerByAttribute_management(bronzeDataset, "NEW_SELECTION", f"{bronzeDatasetDateField} >= date '{mostRecentDate}'")

ericcrossen · Answer

Yeah, that is how I am building it currently. It just seems clunky to me and was curious if there was a better way. If there is a way to tell it to compare the oldest record on the target layer, to the source layer. then take all records between that value and the current system time.