I am having a lot of difficulties creating unique, sequential numbers (for orders in my database) reliably in workflows. It’s in the format PP-100001 (100000+X where X is a simple sequential number stored in each row of the database).
I have created workflows that do a search for the highest sequential number using :max and then adding one but it is unreliable, seems like the workflow sometimes skips, doesn’t fire or does the same twice. I simply cannot afford for this to occur.
Is there a reliable way to have each row of data in a thing have a unique sequential number? What is the best practice as it is essential that the number is both unique and sequential as it is sent to a payment platform that requires this?
Is there a way to change a global variable such as an option set to do this so it is quicker and doesn’t rely on a database search as I’ve read that even a database search where one item in a database is still a relatively slow process.
What if there was an option type - a number that I could change from anywhere in the app and it was globally available?
Would you be able to take the length/count of the number of those objects in the database and then add 1 to that number, and store that number as the unique number?
You’re doing these workflows in the page, right? I wouldn’t do that there.
When you create a new order, of course you go ahead and make the call to Create New Thing from the page. But don’t try to create this serial number in the page.
Collisions can happen there as folks might be simultaneously entering orders. Also, when Bubble Creates a New Thing, your page sees it as created instantaneously (as you would want, usually), but that object is shipped to the database some (short-ish) time later.
If me and you are simultaneously on the order create page, we will both get the same answer for the next sequential serial number and this answer (if your programming is correct) will, in fact, be correct. But of course, once those two orders are in the database, now there is a collision.
So, you’ll need to set up some backend workflows to handle this (where we might still get some collisions, but we can attempt to make the process of serial number assignment… well… serial.)
Or you can create a separate data type for this, with fields type, prefix and code:
For order number you can do:
Type: Order
Prefix: PP
Code: 100001
When an order is created, first you do a +1 on this order number. The concatenated fields can be used to set the order number. When an order is deleted and when it’s number equals the current code you do a -1
Hi Keith, I’m doing it in backend workflows not on the page but still getting collisions and duplicates unless I space them out by about 5 seconds which seems ridiculous as it will take a very long time to process thousands of orders. It is frustrating that bubble cant do a simple database task of incrementing a number reliably.
Below is an answer I got back from Bubble support - I’m still not convinced this is reliable.
Hey Morgan,
Thanks for reaching out about using sequential numbers in the app.
This is a great question for our forum, which is a vibrant community of expert Bubble users that are best able to assist when it comes to specific use-cases for Bubble’s functions.
A possible way to accomplish this is by saving data to the database. If a “nonce” is saved to some field in a data type within your app: you can have the workflow do a search for the last used value, ultimately adding one to it, then updating it in the same field. This provides a reference point when iterating on the number. The workflow may be able to search for the item most recently created using the “created date” or “modified date” fields which every data type contain.
Feel free to reach out if you continue to run into issues or are unable to gain any additional information from other users in the forum.
I am doing this, just a field that is nothing but a simple incremental number. However, bubble cannot do this reliably whether back-end or front end workflows. It’s really frustrating as any other database in the world could do this reliably and easily - in a millisecond not 5 seconds per line.
Oh I see you are suggesting a seperate data type to collate the numbers. It will get to hundreds of thousands of lines so I imagine the lookup will be slow?
Hey Kevin, in theory, that would work but in practice, it doesn’t seem to be reliable as while it’s doing a count it’s starting on the next one therefore duplicates or triplicates.
I know this isn’t necessarily a solution to sequential numbers, but what about using Unix time stamps for your order number instead? Those are accurate to the the split second and from what I understand it’s pretty impossible to get the same one even with multiple users on a page. Just a thought.
The idea is likely to have a database write, followed by a database read which depends on the write, causing the workflow to sync with the database.
One alternative is to add all the numbers at once to the API call at or near the time of sending to the payment API, using a server-side script.
Another alternative is to use the Data API to patch the serial numbers in. Either PATCH for single row at a time, or maybe a BULK CREATE to a linking table that links unique id with serial number.
Thanks for your ideas. I think (unfortunately) that I cannot rely on any numbers generated in order and sequential to be unique so I’m just going to use the system unique ID and retrospectively add a user-friendly sequential number 5-10 seconds apart. This is not ideal as it will not be embedded in the Braintree transaction I am creating at the time therefore the customer will not be able to get an invoice generated by braintree.
**
It has to be said that as much as I think Bubble is an amazing platform it has some seriously fundamental flaws as it cant even add 1+1 reliably!!! Really annoying and frustrating.
Yeah, I see the issues when I experiment with this. For example, if you just kick off a workflow using the new database change trigger (watching for new orders that are not serialized yet), you can see that, at workflow runtime, it’s easily possible to have multiple items that are in need of serialization and simple approaches aren’t sufficient.
Surely there’s an algorithm to do this successfully, but it’s tricky.
One thing that’s super helpful in working it out are my plugins in List Popper and Friends (the FLOW State SSA plugins).
@morgan I went through the post quickly so I might have missed some details, but if all you need is an instant unique number (regardless id it increase of 1 or 1000), why not taking a timestamp in milliseconds?
Setting an expression like Current date/time:extract UNIX:formatted as number will return how much milliseconds passed since January 1, 1970. So can’t really get twice the same code… And no need for backend workflows!
What if you had a different type where you created all the numbers beforehand and then have in their a yes/no on next available number. Then instead of creating the number in run time, you rather assign the next available number?
I like your thinking but it would require a lookup of that other table (thing) and if it was being looked up at the same time (because workflows are not sequential) then I would still get the same value copied twice wouldn’t I? That’s the experience I have had in practice.
So the need is for a backend singleton process, i.e. only one of it is running at a time, no matter how many parallel actions are requesting the increment.
A. Self-scheduled workflow (regular batch process) that picks up the next operation to do and does it. Disadvantage: asynchronous to the workflow that needs the operation done, which would need to wait for an arbitrary length of time.
or,
B. Query an external database that increments a count and retrieves it, in the same atomic transaction. Disadvantages: non-Bubble dependency, additional wait for response.
or,
C. Call an external API that does such a database query. Same disadvantages.
