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 
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 
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 
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.
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 
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
@mishav: yeah, what you propose will at times NOT work.
For anyone still confused on this:
When we try to measure the number of things that have already been given a serial number, that measure will SOMETIMES be wrong with respect to the current item we are trying to assign a unique serial number to.
This is because, at any moment, the result of Do a search for all orders, sorted by creation date looks like this:
[ {orders that have serial numbers} {orders not yet assigned serial numbers but that are created before our current workflow order} { OUR WORKFLOW ORDER } {orders not yet assigned serial numbers created after our current workflow item} ]
Now, in many cases, {orders not yet assigned serial numbers but created before…} and {orders not yet assigned numbers created after…} are both zero.
BUT, when they are not, our attempts to measure the number of items serialized will fail. Here are the scenarios:
-
Search for all orders looks like: [ {orders with serial numbers} {OUR ORDER} ]
This is what many folks are assuming the list looks like all the time. In this case, yeah, a simple analysis will work. We can do one of two ways: OUR ORDER’s serial number should be set to the last serialized order’s serial number +1, OR we can just say “oh, OUR ORDER is at the end of the list, so it’s serial number should be set to the List’s:count (the length of the list)”. But the list does not look like this all the time. -
Search for all orders looks like: [ {orders with serial numbers} {some number of orders with earlier creation dates but not yet serialized} {OUR ORDER} ]
In this case, if we set OUR ORDER’s serial number to the number of orders with serial numbers +1, we will get the wrong answer. However, if we set OUR ORDER’s serial number to the list’s count, we will be correct. But… -
Search for all orders looks like: [ {orders with serial numbers} {some number of orders with earlier creation dates but not yet serialized} {OUR ORDER} {some number of orders with later creation dates but not yet serialized}]
In this case, BOTH simple methods of assigning the serial number will fail. The number of orders with serial numbers +1 is not the correct serial number for OUR ORDER, and the :count of the list is not the correct serial number.
However, in ALL of the above scenarios, the correct serial number for OUR ORDER is the 1-based index of OUR ORDER in the list.
3 Likes
Hi Keith, you left out the search constraint “created date less than our order’s creation date” for which only scenario 2 applies.
Also, all proposed solutions assume that the search does pick up a slightly earlier creation from a different workflow instance. And that search order is consistent for two entries with similar insertion time. This isn’t guaranteed anywhere, so is determined by experiment.
Fun discussion 
Edit - I think Keith’s solution with index-of is the most robust so far, if a secondary sort on unique id is added.
3 Likes
Hey @mishav: Yeah, your point about that search constraint is a good one and worth testing. (Of course, documenting the issue makes this clear.)
In any case, I’d been meaning to deploy the Index Of SSA and this was a really good excuse to do that.
As for search order consistency if two items have exactly the same created date/time, I’m “very close to certain” that Bubble’s algorithm always returns them in the same order, though it’s true that we could only know for sure if told by Bubble or through a lot of (hard to replicate) testing.
If we don’t trust Bubble on that, we could do some sort of secondary sort (which I think you proposed earlier).
Note that doing the index-of is a one-liner with Toolbox’s server script but it ain’t no-code.
Well done @keith on meeting the additional list processing needs
It will be interesting to see what works with @morgan’s use case!
1 Like
It’s flat-out silly that “:index of” or “:index in” isn’t a built-in operator. e.g.,
some_list :index of some_item
or…
some_item :index in some_list
As there are many many use cases that need this and most workarounds are needlessly compute heavy. (This is why List Shifter has a GET INDEX Action. Also indexOf is an available operator in List Shifter’s PROCESS List action.)
2 Likes
Totally agree. That’s why I made the Index Finder plugin if anyone needs to find the index number of an item without adding a bunch of elements to their design screen.
On getting the count of the list by doing a search all on orders created before this order’s creation date, it makes no sense why that wouldn’t work. (aside from the assumption that a record created a ms before the current one would show in that list as @mishav pointed out)
Why would you filter out records that haven’t be given a serial number yet? Just, ‘hey bubs, how many orders were created before this one. Okay, add 1 to that’ Bam… sequential serials. All vanilla Bubble, no plugins required.
1 Like
It makes no sense why the other vanilla Bubble approach wouldn’t work, either, until you actually build it and experience what happens.
As for Index Finder, that’s very cool: But note that your code will only work for Things and not for primitives. (Check the source for IndexOf SSA for a simple way to handle that.)