topic Re: Reiterate Database Sequence in Attribute Rules Questions

Reiterate Database Sequence

drWood — Mon, 22 May 2023 18:58:01 GMT

Hello all.

Does anyone know if it is possible to reiterate a database sequence upon reaching a specified cap/maximum?

We have it coded in SQL to create unique FacilityIDs for our assets with the format: "<AssetPrefix>_DDMMYYYY_<6-digit number>".

I would like for the last 6 numbers to reset to the initial starting point of the Database Sequence in use upon the sequence breaching the number 999999.

I found some walk-throughs on how to put this together in Arcade using the CreateDatabaseSequence gp with the exception of being able to reset the value of the Database Sequence.

Re: Reiterate Database Sequence

RhettZufelt — Mon, 22 May 2023 21:11:01 GMT

I don't use database sequences because I got tired of the "gaps" in the sequence whenever a feature got created, but not saved, etc.

SQL server can easily script the drop/create of the database sequence. No example (and haven't tested), but one option would be to put an insert trigger on the table and check if the number has reached 999999, if so, drop/re-create the sequence.

You can follow the image below to see how to have SSMS create the drop/create SQL for you:

Re: Reiterate Database Sequence

JohannesLindner — Mon, 22 May 2023 21:25:14 GMT

You can't use Arcade to reset the sequence, but you can do it without a sequence:

// Calculation Attribute Rule // field: FacilityID // triggers: insert // get the feature that was created last var fs = Filter($featureset, "FacilityID IS NOT NULL") var last_f = First(OrderBy(fs, "OBJECTID DESC")) if(last_f == null) { return $feature.FaciltyID } // get its sequence value var old_seq = Number(Split(last_f.FacilityID, "_")[-1]) // get the new sequence value var new_seq = IIf(old_seq + 1 > 999999, 1, old_seq + 1) // construct and return the new FacilityID var s = Text(new_seq, "000000") var d = Text(Today(), "DDMMY") return `ABC_${d}_${s}`