I have an issue that cannot be as impossible as it feels right now.
Basically, I am trying to create a unique number using a prefix for depending on user input plus a new sequential number, such as 800-001, 800-002, 800-003 or 801-001,801-002 etc
At the time of data entry, the prefix (i.e. 800 or 801) is not set yet, and based on the user picking the prefix, it should then proceed to give you the first available number that’s not been assigned to any record yet.
The records basically are designs, the prefixes represent collection, each are set into a table in the same base and linked to each other.
I could do this via multiple autonumbers, but the database is likely to grow into several hundred prefixes/collections, so a array of autonumbers is not scalable, really.
Would it be possible to roll up / count all the records that belong to a collection, take that number and increment it by one for each new record being added?
I wasted quite a few hours on this already and have to admit i do need some help here.
Thanks!
