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