automatic sequential numbering when creating objects

[JoeGerner]

I have an ArcSDE with several tables. I want to create a new field in each table. This field should be filled automatically when the object is created. The field should contain a six-digit sequential number, i.e., 000001 for the first object, 000002 for the second object, and so on.

I have already tried using the tool enerate database sequence and arcade in the attribute rules, but I cannot achieve the desired result.

Arcade Code

var v = NextSequenceValue($datastore, "SEQ_KNR");
return Text(v, "000000");

I use ArcGIS Pro 3.3

[RichardDaniels]

My first question is why do you want the field to be TEXT in the database, could you not have it be an INT then just convert it in code when needed? Secondly, you did not say the database type, but if it is in SQL or SQLite you can add an autoincrement field to the table.

Copy code

CREATE TABLE users (
    my_new_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

• Auto-increment fields cannot be updated manually unless explicitly allowed.
• If you delete rows, the auto-increment counter does not reset unless you manually reset it.
• Use BIGINT instead of INT if you expect very large numbers.
• you can set a 'start' number for the sequence as well.
• an autoincrement field DOES NOT have to be a primary key (or a key at all)

[D_Atkins]

Hi Joe!

I was able to use the NextSequenceValue in our test environment, but in our case, there was only one parameter for that -- the name of the sequence we generated with the "Create Database Sequence" tool. 

We followed along with this set of examples: Attribute rule script expression examples—ArcGIS Pro | Documentation

It was a little touchy getting the sequence name just right (i.e, schemaOwner.YourSequenceName), and we had some conflicting locks (because of course we had our feature table open while we were tinkering) -- but once we had the sequence created and named, the attribute rule properly validated and saved, then, our new features were getting their sequential integer!

That said, we did not try to convert the integer to text -- so that's another step!


Just for completeness sake, I've included a screenshot of our attribute rule as configured for a DBO schema, where our expression is simply:

return NextSequenceValue("DBO.TEST_RULES_PTS_sequence")

______________________

Richard's suggestion is good too!  But our DBA's have advised us to try to keep our work in ArcGIS.