AnsweredAssumed Answered

Join a Table to a Feature Layer with a Where Clause

Question asked by DavidNarkewicz on Jan 25, 2018
Latest reply on Jan 28, 2018 by rvburton

I am trying to calculate a field in the feature class from a table. The table and feature class have a common field for a join. I want to filter the feature class to only edit rows that have been edited in the past 7 days. I have created a feature layer with a Where Clause filtering all rows that have been edited in the past 7 days. I am unable to join the table to the feature layer. When I make the feature layer without the where clause the join works and I am able to calculate the field. With the Where Clause in Python I receive an "An invalid SQL statement was used" error. I have also tried each scenario in ArcMap. Without the where clause works but when joining with a feature layer with a Where Clause I receive an error in the join validation:

 

"The number of matching records for the join:
- Unexpected error encountered.
- No matches found by joining"

 

Has anyone else ran into this problem?

 

Python code with Where Clause

# Get start and end times for report
start_time= datetime.timedelta(days = 7)
end_time = datetime.datetime.now()
report_time = end_time-start_time #this is the time that gets used to filter records

 

# Find all records that are later than or = to report_time
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr, dateSearch)

 

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

 

Python code without Where Clause

# Find all records
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr)

 

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

Outcomes