Wanting to write an Arcade expression, and schedule it to run daily, that will populate a "Last Inspected" attribute on a feature layer, based on the most recent related table record.
I've tried a few examples I've copied from other posts, but I'm guessing "FeatureSetBy..." doesn't work in field calculator?
Any assistance would be greatly appreciated!
The FeaturesetBy* functions should be available.
You can do it like this:
// load the table, this assumes you have a relationship class
var inspections = FeaturesetByRelationshipName($feature, "RelationshipName")
// sort descendingly by the date field
var latest_inspection = First(OrderBy(inspections, "DateField DESC"))
// no inspection found -> return a default value
if(latest_inspection == null) { return null }
// else return the date field of the latest inspection
return latest_inspection.DateField
Thanks for the reply!
It's advising there's an error on line 5:
Do you actually have a relationship class between your feature class and the inspection table? Is it named "gas.GU.VALVEINSP"?
Oops...no. But, I did change to the correct name, but it still gives the same error:
Are you set on using Arcade? I have a script in python that I use for this same purpose, but haven't translated into any other languages.
I'm not dead-set on using Arcade. I'd be open to other languages, as long as they result in the same outcome. 😄
The below Python3 code is what I use to update the Last Assessment field for stormwater assets that we maintain. You can run the individual geoprocessing tools and copy the python script to your clipboard from the History pane if you need to get the correct formatting for all the variables. I haven't automated our use of it yet, but I believe it's just a matter of scheduling it to run.
arcpy.management.Sort("Stormwater_Assets.STORM_ADMIN.DSTRUCTUREASSESSMENT", r"R:\Derek\ArcPro\WAMS - Work Order Updates\Stormwater Assets.gdb\DSTRUCTUREASSESSMENT_DescendingDate", "InspectionDate DESCENDING", "UR")
arcpy.management.AddJoin(r"dDropStructure", "FacilityID", "DSTRUCTUREASSESSMENT_DescendingDate", "FacilityID", "KEEP_ALL", "NO_INDEX_JOIN_FIELDS")
arcpy.management.SelectLayerByAttribute(r"dDropStructure", "NEW_SELECTION", "Ownership = 'PAC'", None)
arcpy.management.CalculateField(r"dDropStructure", "Last_Asmt", "!DSTRUCTUREASSESSMENT_DescendingDate.InspectionDate!", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")
arcpy.management.RemoveJoin(r"dDropStructure", "DSTRUCTUREASSESSMENT_DescendingDate")
arcpy.management.Delete("DSTRUCTUREASSESSMENT_DescendingDate", "")