topic Re: Arcade Expression to Calculate Field based on a Sort Date field in ArcGIS Pro Questions

Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Wed, 24 May 2023 18:51:15 GMT

hello,

I am trying to use the SequentialNumber() for Calculate Field but I would like to calculate the Field based on the a field called SampleDate in ascending order.

I have tried using the helpers and modify the code block but failed.

i tried a arcade expression(below) but got null values as retuned values

// Arcade for SequentialNumber var rec = 0; function SequentialNumber() { var pStart = 1; var pInterval = 1; var SampleDate = $feature.SampleDate; // Name of the field used for sorting // Access the global feature set var Opp_table = FeatureSetByName($datastore, 'SDEP.SDEADMIN.WaterSampleInspectionsOperator'); // Sort the features based on "SampleDate" field var sortedFeatures = Sort(Opp_table, SampleDate, 'ASC'); if (rec == 0) { rec = pStart; } else { rec = rec + pInterval; } // Assign the sequential number based on the sorted order return rec; }

Any help would be much appreciated

Re: Arcade Expression to Calculate Field based on a Sort Date field

JohannesLindner — Wed, 24 May 2023 19:06:23 GMT

This should work as long as you don't have exactly the same dates in multiple rows:

var sd = $feature.SampleDate return Count(Filter($featureset, "SampleDate < @SD")) + 1

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Wed, 24 May 2023 19:09:59 GMT

I do have dates that are identical.😬

Re: Arcade Expression to Calculate Field based on a Sort Date field

JohannesLindner — Wed, 24 May 2023 19:25:22 GMT

var sd = $feature.DateField1 var oid = $feature.OBJECTID var previous = Filter($featureset, "DateField1 < @SD") var same_lower = Filter($featureset, "DateField1 = @SD AND OBJECTID < @oid") return Count(previous) + Count(same_lower) + 1

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Wed, 24 May 2023 20:16:51 GMT

Maybe I am not setting this up correctly, I'm still getting null values... The field I am trying to calculate is a LONG field will this cause an issue with the expression?

This is what I used is this correct in what you suggested?

// Arcade for SequentialNumber var rec = 0; function SequentialNumber() { var pStart = 1; var pInterval = 1; var SampleDate = $feature.SampleDate; // Name of the field used for sorting var sd = $feature.SampleDate var oid = $feature.OBJECTID var previous = Filter($featureset, "$feature.SampleDate < ") var same_lower = Filter($featureset, "$feature.SampleDate = AND OBJECTID < @oid") // Access the global feature set var Opp_table = FeatureSetByName($datastore, 'SDEP.SDEADMIN.WaterSampleInspectionsOperator'); // Sort the features based on "SampleDate" field var sortedFeatures = Sort(Opp_table, SampleDate, 'ASC'); if (rec == 0) { rec = pStart; } else { rec = rec + pInterval; } // Assign the sequential number based on the sorted order return Count(previous) + Count(same_lower) + 1 }

Re: Arcade Expression to Calculate Field based on a Sort Date field

JohannesLindner — Thu, 25 May 2023 10:08:24 GMT

No, just use my expression as it is.

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Thu, 25 May 2023 13:04:25 GMT

Good Morning,

Running this Code produced this error... See Below

var sd = $feature.SampleDate var oid = $feature.OBJECTID var previous = Filter($featureset, "SampleDate < @SD") var same_lower = Filter($featureset, "SampleDate = @SD AND OBJECTID < @oid") return Count(previous) + Count(same_lower) + 1

Calculate Field
=====================
Parameters

Input Table Operator Inspection
Field Name (Existing or New) UID
Expression

var sd = $feature.SampleDate
var oid = $feature.OBJECTID

var previous = Filter($featureset, "SampleDate < @SD")
var same_lower = Filter($featureset, "SampleDate = @SD AND OBJECTID < @oid")
return Count(previous) + Count(same_lower) + 1

Expression Type ARCADE
Code Block
Updated Input Table Operator Inspection
Field Type TEXT
Enforce Domains NO_ENFORCE_DOMAINS
=====================
Messages

Start Time: Thursday, May 25, 2023 8:58:46 AM
ERROR 002717: Invalid Arcade expression, Arcade error: Failed to query statistics, Script line: 6
Failed to execute (CalculateField).
Failed at Thursday, May 25, 2023 8:58:46 AM (Elapsed Time: 0.50 seconds)

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Thu, 25 May 2023 14:15:04 GMT

Good Moring Johannes,

Here is a some data that is in my table for an example

SiteName	UID	SampleDate	Cl
Lab Site 1		2021-01-01 5:00:00	1.8
Lab Site 2		2021-01-01 5:00:00	1.6
Lab Site 3		2021-01-01 5:00:00	1.8
Lab Site 4		2021-01-01 5:00:00	2.6
Lab Site 5		2021-01-01 5:00:00	1.6
Lab Site 6		2021-01-01 5:00:00	1.6
Lab Site 7		2021-01-01 5:00:00	2.6
Lab Site 8		2021-01-01 5:00:00	2.3
Lab Site 9		2021-01-01 5:00:00	4.1
Lab Site 10		2021-01-01 5:00:00	1.6
Lab Site 11		2021-01-01 5:00:00	2.6
Lab Site 12		2021-01-01 5:00:00	2.5

Re: Arcade Expression to Calculate Field based on a Sort Date field

JohannesLindner — Thu, 25 May 2023 15:38:54 GMT

I have no idea why this is happening...

Try doing it with a Python script instead (not in the Field Calculator, in the Python Window):

table = "TestPolygons" # the table path or layer name sort_field = "DateField1" # the sort field number_field = "IntegerField1" # the field that will be calculated i = 1 with arcpy.da.UpdateCursor(table, [number_field], sql_clause=[None, "ORDER BY {}".format(sort_field)]) as cursor: for row in cursor: row[0] = i cursor.updateRow(row) i += 1

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Thu, 25 May 2023 20:21:17 GMT

Hello Johannes,

I ran as python and received a RuntimeError..,

table = "Water Sample Inspections Operator" # the table path or layer name
sort_field = "SampleDate" # the sort field
number_field = "UID" # the field that will be calculated

i = 1
with arcpy.da.UpdateCursor(table, [number_field], sql_clause=[None, "ORDER BY {}".format(sort_field)]) as cursor:
for row in cursor:
row[0] = i
cursor.updateRow(row)
i += 1

Traceback (most recent call last):
File "<string>", line 9, in <module>
RuntimeError: Invalid parameter value passed to function [SDEP.SDEADMIN.WaterSampleInspectionsOperator]

Re: Arcade Expression to Calculate Field based on a Sort Date field

JohannesLindner — Thu, 25 May 2023 21:26:05 GMT

Export your table to a gdb and run either the Field Calculator or the script on that exported table. If the errors persist, I'm absolutely out of ideas.

Re: Arcade Expression to Calculate Field based on a Sort Date field

BrianHumphries1 — Fri, 26 May 2023 15:53:30 GMT

Good morning Johannes,

I am able to run the Arcade expression on the table Inside of the FGDB

My data does have identical values for the for the sample date

see below

Results after running the Calculation and sorting by Sample Date

It seems that the codeworks after the first identical values change, Then its picks up the next record count after the first set of identical records are ran through.

So in this example I had 4 records that matched the same sample date in ascending order ,

then it picked up the count of 5 after the first set of identical passed.

Getting closer to making this work.

Thank you Johannes for all of your help so far I would be lost with out it?

I also ran the Python to see what results I would get and received this

Again thank you for your support Sir.

Brian.

Re: Arcade Expression to Calculate Field based on a Sort Date field

RobynSnookCCB — Thu, 27 Jul 2023 18:30:27 GMT

Is there a way to do this and have the numbering restart when a new date starts?