How to achieve Unique, Sequential Numbers Reliably?

@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!

See here:

2 Likes

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?

Hi There, I appreciate that but it’s not sequential.
My order numbers need to look like this:

PP-1000001
PP-1000002
PP-1000003
PP-1000004

But they can’t reliably so I am now just reverting to the unique ID of the row which is

1583977548678x916035205996429800

Unfortunately not very customer friendly - can you imagine asking someone to read that out over the phone!

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.

1 Like

Keith maybe you could develop a plugin to do this reliably? I’d buy it!

1 Like

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.

An example of C … New Plugin: UniqueID

I was just looking at this: [New Feature] Scheduling API workflows can now be done recursively

Yes I think option A is the best option for reliability, I’m going to test it.

I wonder if making the sequential number (SEQ in this case) the primary field would force it to be unique and what the consequences of that might be?

This might be over simplifying it, but can’t you just create a large amount of sequential numbers to start, then fill in the next one which hasn’t been used? Then schedule a daily process to add xxx more? You can use the modified date as the invoice date, and you would still retain the proper number list you want.

1 Like

Another disadvantage of A is the additional compute cost of polling for work to do, at times when there isn’t much happening.

Unfortunately, the “primary display fields” are about how the Bubble editor displays the data.

Nice idea :smiley: Does this move the problem from collisions on create to collisions on allocate existing?

I haven’t tested, but i have to assume it works until it’s tested :wink:

I have a fix for this. As you might expect, the solution reminds us of a sad, annoying fact: that Bubble is exceedingly stingy with server compute.

(What I’m saying is that server compute on Bubble is VERY expensive vis-a-vis other available solutions. By “other available”, I mean “outside of the Bubble environment”. I don’t mean to imply that you have other options if you desire to stay within the Bubble ecosysetem. @mishav (welcome back, BTW, @mishav! … glad to see that you weren’t actually eaten by a dingo, as I had guessed) points toward another solution, which in all honesty, I think is NOT correct, but another Bubbler did come up with it and it seems to work, but you’d need to rebuild it for yourself on some Function-as-a-Service platform [of course, since the time of that posting, FaaS services have multiplied like methbunnies and – if you chose to go that route – you’d have a wealth of choices… but I digress].)

OK, back to the matter at hand: You don’t need recursive workflows to do this, but you do need some tools that are not in the vanilla Bubble arsenal. ALSO, my solution (first version) uses the brand-new “Database Trigger Event” AND (more importantly) the very latest version of various SSAs from my “List Popper & Friends” plugin. (So, go get that.)

The core problem comes down to this: Multiple Orders may hit the database at the same (or very close to the same) time. And, at such times, doing something like:

Search for Orders (sort by whatever) or even Search for Orders:count

will return both orders that have BEEN processed, as well as orders that have YET TO BE processed. (The processing I’m referring to is the assignment of an asymptotically increasing serial number.)

SO, you CAN’T (literally, actually can’t) get an accurate answer to the question, “What serial number should this item be?” in vanilla Bubble.

IT DOES NOT MATTER how you try and assess that value. (Whether you increment some global value [a singular database item that tracks highest serial number], getting highest serial number from a search, etc., etc. All answers will – at some times – be incorrect.)

And, yet, WE CAN (sometimes) SEE THE ANSWER. If we haven’t had a workflow in a while and our capacity isn’t maxed out, there will only be 1 unprocessed order when we do our backend/server-side/API workflow. And that item is the item we want to work on (assign a serial number to). And the serial number we should assign it is equal to the total number of Orders in the database at that moment.

So, why is it so hard? Well, we sometimes have OTHER records (orders) that have yet to be assigned serial numbers. Further, those records may be records that were created EARLIER or LATER than the record we are trying to assign a serial number to. There may even be BOTH earlier and later records in the list.

In vanilla Bubble, we CANNOT know for certain where “our” record (the record that “this” workflow is processing) is in the list of all records. We know that it is in the list (duh) and we know that it is toward the end of the list, but we cannot know for sure exactly where it is.

[Aside: Did I lose you yet? Let’s go back to first principles: Let’s step out of time for a moment and think about our problem from a god-like perspective. Let’s stop our app for a moment and freeze it in time.

Time being stopped, we can examine our database. If we then take all of our Orders, and sort them by date created (ascending), and look at them in this order we will see EXACTLY WHAT THE SERIAL NUMBERS SHOULD BE. The first item (the item at index 1) will be the earliest item created – item number 1 and should have serial number 1. The second item (the item at index 2)
– “item number 2” should have serial number 2… The very last item in the list (the item at index n) we would call the nth item and it should have serial number n.

Do you detect a pattern here? Of course you do. The correct serial number for any item should be its position in the list of all items sorted by creation time.]

And now the problem should be very clear to us. All we need to do is – at any point in time – just find the position (the index) of “the item” in “the list of all items sorted by creation time”. Done!

EXCEPT THAT: Bubble does not have that function. It’s stupid, but true. Bubble, though it is a list-oriented language, is missing this feature. (This function is commonly called “index of” – given an array of items and some individual item, tell me the index of where that item appears in the array.)

SO, someone must create that. And so, you’ll find an action called “IndexOf SSA” in the latest version of List Popper & Friends.

And now, in your backend workflow, you can say “here is an item to which I need to assign a serial number” and “here is the list of all items”… and you can then ask “where is this item in that list?”

And the answer to that question (provided by IndexOf SSA) is your serial number.

It’s super late here or else I’d do a quick video about all of this (I’ll get to it tomorrow), but here’s the example page:

https://list-shifter-demo.bubbleapps.io/version-test/order-sequence?debug_mode=true

Go ahead and spam the CREATE ORDER button as much as you like. You’ll note that you can quickly max out capacity… but eventually, the correct values will populate.

If you examine the values in the RG, you’ll see all of the symptoms of what you experience in your app. There are times when the “Naive” approach (assuming the current workflow item is at the end or start of the list, essentially) results in an incorrect, lesser value, and times when it returns null).

The editor view is here: design mode of the page in question | backend workflow I’m using

16 Likes

Thanks for the greets, Keith :smiley: The dingoes had a nibble but I’m rebuilding …

Interesting solution! Using armchair logic rather than experimenting, what happens when two CREATE ORDER actions happen close enough that both workflows pass an equal length list?

“It can’t happen” will be an interesting answer too.

It might be a dumb question as I haven’t examined your implementation yet.

I would only use 1 record for this number, which only gets updated with +1 or -1 each time depending on the situation at hand. You can use one each record for multiple goals:

Example:
Type: Order
Prefix: PP
Code: 100123

Type: Invoice
Prefix: INV
Code: 100456

Answering my own question …

Database trigger sends to the event actions the new thing just created, then we compare it to a search of all things list to see which position it is in.

Yes, Keith’s solution is nice, this should work for parallel events, as long as the returned search list is consistent for collisions (same created timestamp) … for this case a secondary sort on unique_id will fix it. :smiley:

To make it perform better on long lists, put a “peg in the sand” and make the search start from a known offset. For example, every 100th item added (count mod 100 equals 0) update offset value with the count. Then exclude lower counts (that aren’t empty) from the search.


There’s an alternative way to get the position, without needing a plugin …

(search of all things list with created time less than or equal to new thing’s created time) : count the list items

I wonder if the created timestamp will always be different enough for this alternative to work reliably.


Edit - for this solution to work, it relies on the search results including a record added slightly earlier in a parallel workflow, which may or may not be reliable :stuck_out_tongue:

1 Like

Fancy is all good and everything but @kevin12 presented the solution right up front. Grab a count of all orders with a creation date less than this order’s creation date and add 1.

2 Likes

Maybe so, but if this thread had stopped there we would have been deprived of Keith’s fantastically entertaining manuscript.

2 Likes

Lol! So true!

Hey @mishav, “it can’t happen” in this case.

The workflow item (a new order we are processing) always exists and is always part of the set of all orders. And we only need those 2 values to find order’s position in the list.

There is one further thing that must be constrained, however, the list itself (the result of “Search for All Orders“) must stay unchanged during the workflow.

So we do have to put :make static on that search.

1 Like

Kevin’s solution is NOT CORRECT for reasons explained in my message. You cannot be sure that any measure of “the number of items that have already been serialized” is correct with respect to the current workflow item.

1 Like