First of all, I know about the auto-increment function of a number field. But it’s not what I am looking for. Why? Because the field continues to count from the last position even if old records are deleted.
This means:
I need a script to fill in the gaps (freed up inventory slots) in the database when records are deleted and after that, created.
I want to use the inventory number as just it, a fixed number for a record (readable) and I don’t want gaps.
Can somebody help me with it? I think it’s understandable that you need something like this for managing collections of items (i am a collector).
Thanks!
I think a script would be a lot of overhead for this just because as the number of records increases, you’d have to scan through them to find where the holes are and allocate a new ID. It’s possibly but it’d get slower over time.
What I think might make more sense is a script you call to empty the record out and then a script field (that you can hide) that detects if some key fields are empty which you then key into a saved search for “available” records. Then you can repurpose those records as you need them.
I personally don’t think I’d ever want to reuse ID’s in a collection and would probably always keep them as is with a flag that says I don’t have the item any more but that’s perhaps just me.
Possibly one way that would be relatively quick, although as Sam said, would get slower over time too, but would be easy to implement, would be a script that simply looped through all the records and re-numbered them all from 0…N, for whatever numbering scheme you are using. You wouldn’t need to look for holes as this would effectively eliminate holes and your new records would just go to the end. But then as Sam suggested, reusing IDs or in this technique, changing IDs of an item isn’t always a good idea. But it really depends on what your workflow is.
.
