Arcade Expression to Calculate Field based on a Sort Date field

[BrianHumphries1]

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 

[JohannesLindner]

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

[BrianHumphries1]

I do have dates that are identical.😬

[JohannesLindner]

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

[BrianHumphries1]

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
}

[JohannesLindner]

No, just use my expression as it is.

[BrianHumphries1]

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)

[BrianHumphries1]

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

[JohannesLindner]

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

[BrianHumphries1]

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]