Assistance with Arcade creating new incrementing record ID

[LindsayRaabe_FPCWA]

I am trying to use the below Arcade code to generate a new incremental ID value for features in a layer when edited in Experience Builder. Features are typically submitted via Survey123 with a default value of "NEW". Once submitted, our OHS people review them in Experience Builder and edit the feature to show they have done so. The below is a calculation applied in Field Maps Designer that "should" check all features in the layer, find the highest existing ID value (e.g. HAZ - 095) and calculate a new ID value (e.g. HAZ - 096). This also needs to happen if a new feature is created in Experience Builder (which would have an empty value instead of "NEW"). If a feature has been previously edited and has an "HAZ - ###" value, then it should be retauned. 

Can anyone show me whats going wrong? Currently all NEW or blank values are being calculated as "HAZ - 001" and exisiting values are being returned correctly. 

// Fetch the current value of the field
var currentValue = $feature["Hazard_ID"];

// Check if the current value is "NEW" or blank
if (currentValue == "NEW" || IsEmpty(currentValue)) {
    // Fetch all features to determine the latest number
    var features = FeatureSetById($map, 'Form FPC94 Hazard Report - OSHE Review', ['Hazard_ID'], false);
    
    var maxNumber = 0;

    // Iterate through features to find the highest number
    for (var f in features) {
        var value = f["Hazard_ID"];
        if (Find("HAZ - ", value) == 0) {
            var number = Number(Mid(value, 6));
            if (number > maxNumber) {
                maxNumber = number;
            }
        }
    }

    // Increment the highest number by 1
    var newNumber = maxNumber + 1;

    // Format the new number with leading zeros
    var formattedNumber = Text(newNumber, "000");

    // Return the new value
    return "HAZ - " + formattedNumber;
} else {
    // Return the current value if it's not "NEW" or blank
    return currentValue;
}

[LindsayRaabe_FPCWA]

Pretty sure the problem is with the FeatureSetById. I've also tried FeatureSetByPortalItem but not having any luck in getting it to look at the features in the layer. Thats the point I've managed to get it to work up to. What am I doing wrong here? That is my layer name as per the map layers. 

[jcarlson]

We do something similar for a permits layer, but we don't use a loop, as that can run pretty long with a large layer. Do points ever get deleted from this layer? If records persist, you could just use Count to return the number of records, then add one to it? And you could filter it first using SQL like Hazard_ID LIKE '%[0-9][0-9][0-9]' to get features that end in a 3-digit number.

[LindsayRaabe_FPCWA]

Hi Josh. Thanks for your suggestions. They could potentially work, though it's possible a feature could be deleted if something is submitted by accident. 

I've been experimenting since my original post, so I'm confident the loop part is OK, but it can't actually access the layer to do the loop. I had the same concern about the loop getting large and slow over time so added in a filter prior to only get the last month of records which should keep it manageable. Just need to find a way to get it to read the whole layer in the first place. 

I've just tried this but still no joy:

var features = FeatureSetByName($map, 'Form FPC94 Hazard Report - OSHE Review', ['Hazard_ID'], false);

[ChristopherCounsell · Accepted solution]

for (var f in features) {

        var value = f["Hazard_ID"];

        if (!IsEmpty(value) && Find("HAZ - ", value) == 0) {

            var number = Number(Mid(value, 6));

            if (number > maxNumber) {

                maxNumber = number;

This should handle null values.

Otherwise I'd recommend creating an additional integer field for the id values, which could be concatenated to 'HAZ-' in this arcade expression.

It's much easier to access a table of integer values and sort/filter them for max value instead of doing a for loop against a text field. Use featureset, filter, orderby and the get the first result.

Could also be worth considering $originalFeature so that checks are made against the existing values, not the current field value. Doesn't seem to be an issue but feels like you're prone to self intersecting loops.

[LindsayRaabe_FPCWA]

Thanks for the tips - definitely going to include the $originalfeature context. Still working on the rest of it but it's all helping out. 

[LindsayRaabe_FPCWA · Accepted solution]

Right - so here's where I've landed on a final product (with findings thrown in around the issues I faced). 

I have adopted your suggestion @ChristopherCounsell to use an integer field to create the initial hazard number (Hazard_Num) as well as maintained my previous text field (Hazard_ID). When a form is submitted through Survey123, the Hazard_Num is null and the Hazard_ID is defaulted to "NEW". Both are hidden in the survey so this is all in the background. 

When the entry is reviewed by our OSHE team using experience builders Edit widget, there are now 2 separate calcs running. 

1st - the Hazard_Num field runs:

// Check if Hazard_ID is empty
if (IsEmpty($feature.Hazard_Num)) {
    // Query all features to find the highest Hazard_Num
    var allFeatures = OrderBy($layer, 'Hazard_Num DESC');
    var highestFeature = First(allFeatures);

    // Calculate the new Hazard_Num
    var newHazardID = highestFeature.Hazard_Num + 1;

    return newHazardID;
} else {
    // Retain the current Hazard_Num
    return $feature.Hazard_Num;
}

2nd - the Hazard_ID field runs:

// Check if Hazard_ID is new
if ($feature.Hazard_ID == "NEW" ||IsEmpty($feature.Hazard_ID)) {
    // Get Hazard Number
    var HazNum = $feature.Hazard_Num;
    
    // Calculate new Hazard_ID
    var newHazardID = "HAZ - " + Text(HazNum, "000");

    return newHazardID;
} else {
    // Retain the current Hazard_ID
    return $feature.Hazard_ID;
}

Findings - the main issue I was running into was that the "for" loop flat-out refused to work with the text field. Even with the "Haz - " component being removed and formatting the values as numbers, it just kept returning errors. 

By breaking it out into 2 parts, I can now generate a unique, incrementing number at the point of review by the OSHE team for features with no number (null), and that value is then pulled and calculated into a pretty Text string in the ID field. Any value previously updated (no longer equals "NEW") gets retained as is. Any value added in manually by the OSHE team is also captured and created by the IsEmpty function.