Best way to create a sequence based ID hosted feature layer

[KimGarbade]

I have a hosted feature layer on AGOL.  This layer is editable.  When a new feature is added I have to create a new "SmartID" prefaced with the side of town it falls within based on overlay analysis and then using the next highest value from a database sequence (IE VALV-N-0001 the next one could be VALV-S-0002).  I originally wrote this pretty fast in the Pro I published the service from using Arcade, an Attribute rule and a database sequence....Come to find out Attribute rule don't work in AGOL....OUCH....I thought I'd come up with a work around using a Template/Form for data entry in AGOL and using Arcade again to create a calculated value in the AssetID field, but now I find out that AGOL (I guess) doesn't know what database sequences are either since Arcade doesn't understand "nextsequencevalue" in the context of the Form (and I definitely had a database sequence contained in the database I publish from, but I guess it doesn't get published automatically and I can't find how to publish the sequence for use in AGOL).... AGOL also (to the best of my knowledge) doesn't have an iterating data type I can use as a sequence replacement.... What am I missing!?  There has to be a way to assign the next highest ID (other than ObjectID) to a new asset created in AGOL?  Any help would be appreciated.

[JoshuaSharp-Heward · Accepted solution]

Hi Kim,

The new "Form" functionality in Map Viewer and Field Maps allows you to have a calculation using Arcade. I've done something similar and I believe my steps were something along the lines of:

1. Intersect the current feature with your overlay layer, indicating which side of town it's in https://developers.arcgis.com/arcade/function-reference/featureset_functions/#intersects
2. Get the layer to query itself (filter function https://developers.arcgis.com/arcade/function-reference/featureset_functions/#filter) to find records with an "S" or "N" in the AssetID field, then use Order By (filtered features, "AssetID Desc") to get the one with the highest number at the top, and First(OrderedFeatures) to get the top one
3. Grab the final four characters of the id by doing Right(FirstFeature.AssetID, 4) , then cast it to a number by using Number(RightFeature)
4. Get the next number by doing += 1 on the above number
5. Add the leading zeroes by taking your number and doing Text(mynum, "0000")
6. Concatenate "VALV-" + the N/S string + "-" + the number with the padded zeroes from the step above

This will then work in that web map or field maps app. Not quite the same as using attribute rules but the best way I've found of doing it in AGOL so far, hope that helps!

[KimGarbade]

Thank you Joshua,

I like this a lot.  It makes perfect sense to me.  We used to do this in the old days before database sequences.  Since this has to be an everyday problem, I thought I was missing something that had to be built into AGOL natively to solve it, but I guess I'm not.  Note to Esri though.  Support for attribute rules would be AWESOME, and a database sequence object would be appreciated.  If it's there already and I'm missing it, please let me know and I apologize to Esri.  Thank you again Joshua.

[JoshuaSharp-Heward]

My pleasure, glad that worked and that I could help out! I am also eagerly awaiting the day that attribute rules are supported, but glad this is an interim solution at least!

[Justin_Greco]

This approach does work, but the reason we don't use the form calculations for this is the risk that two people create features at the same time.  Since this just calculates the value after you click on the map and before you hit the create button.  If someone else starts creating another feature, before the other hits create, there will be duplications. 

Our approach is to setup webhooks on feature creation and use Power Automate to get the next ID and then update the feature.  Ideally there would be something similar to a database trigger, which is what attribute rules are designed for. 

However, attribute rules are not supported for hosted layers yet.

[Nick_Creedon]

I am also considering using power automate to trigger the flow of creating ID's. What have you done to set this up? We currently do not have a premium subscription to power automate so I haven't tried anything yet.