I need to create unique numbers for database records.
Think about invoices. They have something like “Invoice #281”. The 281 is generated automatically when the invoice is created.
I need this number to be guaranteed unique and it needs to be set on a per customer level.
So customer A, Bob’s Widgets, can get Invoice #282 and customer B, “Mary’s Cogs” can also get Invoice #282, even though they are different invoices.
The way I’ve tried is:
Create a Sequence Number table.
Create an entry in the sequence table and set the slug to “bob” (for Bob’s Widgets).
When I need a new unique and sequential invoice number for Bob’s Widgets:
Create a new Invoice record in the Invoices table. The Invoice table has a field called “Unique Sequence Number”.
Create a new record in the Sequence Number table.
Set the slug of the result of step 2 to “bob”. It generates a slug of “bob-1”.
Set the “Unique Sequence Number” from step 2 to it’s slug, split by “-” and last item. That captures the “1” from “bob-1”.
It seems to work, even though I can’t reference the “Set a thing’s slug” step. That is, “Unique Sequence Number” is accessing the slug value even though it’s not referencing the step that generates it.
Since slugs are guaranteed unique and Bubble already generates a sequential number to slugs when you try to set a duplicate value, this should work.
But it’s slow and seems hacky.
Is there a better way?
To be double clear: the sequential number needs to be guaranteed unique.
@georgecollier, you must have come across this at some point.
You could have two workflows running at the same time creating a record. Depending on when they execute their steps, they could set the number on their created invoice to the same number.
E.g.:
The latest invoice for Bob’s Widgets is #282.
Two workflows fire to create two new invoices for Bob’s Widgets. Before workflow #1 finishes creating a new invoice, workflow #2 starts. Both workflows look at the last invoice (#282) so that it has a number to +1 from.
Both workflows then set the sequence number to 283 on the thing they create.
The core of generating unique sequence numbers is usually centralizing the processing. As long as you allow multiple processes generate numbers, you can’t guarantee it as far as I see.
To centralize, you can have some kind of a Task Queue kind of data structure for invoice creation. Whenever an invoice needs to be created, you add a task to this data type with the necessary data (maybe a list of invoice items or whatever) and a recursive API process this Task Queue elements one by one (which assigns the IDs incrementally one by one as well).
How many people are using your app? Don’t solve for problems that will only become an issue with tens of thousands of simultaneous users. Do a search for Invoices (customer = X):count + 1 will do you just fine.
If you want to be pedantic about it you can Set slug to Arbitrary text:
Thing's unique ID:append Do a search for Invoices (customer = X):count + 1
or something like that… but again, this isn’t something worth thinking about unless your user volume is extremely large, which I doubt it is right now.
I used this approach in one project and started facing it with only 100 users. Though the probability is low, something like once a year, but you need to be ready to clean all the shit up. In my case the system generated a duplicated order number and charged wrong account, so that was critical and I finally moved to AWS functions for id generation.
There’s also another approach I used in different project which might have better fit.
You still generate invoice number as @georgecollier suggests, but this number is just for user and does not play a crucial role in the system. Throughout the system use unique_id as an id for invoice. The caveat of this approach is that identification of an actual invoice whenever you have an invoice number would require additional information (such as user’s e-mail) and corresponding steps (such as filtering by the user’s e-mail).
I’m going to see if the slug thing works from my original.
If Bubble support tells me that slugs are guaranteed to be unique, then my approach will work perfectly. I think it’s the least hacky and most reliable way of doing it. I mean, that’s the point of slugs: generate a unique, human readable identifier for a specific record.
Thanks for sharing your story earlier though. It confirmed why I’m putting in a little effort now. Hopefully someone else in the future finds this in forum!
