Pulling newly created records from one feature class to another

[ericcrossen]

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.

[BobBooth1]

I have used the pattern of having a field with a value that I change to indicate that some data has been processed. In my case, it is mostly Survey123 data, so I add a field to the feature class (outside of the survey) and set it so it has a default value, say of 1.  When my script runs, I get the records that have a value of 1, process them (this is where you would append them to your "silver" dataset) and then once that succeeds, set the value of those records to something else, such at 0, or 9999...

It has worked well for me, and eliminates the need to do queries with dates or keep track of the last pulled date.

[ericcrossen]

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. 

[JakeSkinner]

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}'")

[RogerDunnGIS]

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.