Forum Academy Marketplace Showcase Pricing Features

How to achieve Unique, Sequential Numbers Reliably?

For any field that supports sorting having operators “:high water mark” and “:low water mark” would be very useful for maintaining incremental uniqueness. Most traditional RDBMS record this statistic on each sort-able field, as it is very cheap, just a pair of numbers per field in the table (not per record!). The Bubble expression for an increment would be something like:

    Thing's Unique Value :high water mark + 1 

Note: high water and low water marks are statistics over the entire history of the field, not just the records currently in existence. They are the highest and lowest values ever seen in the field. This will ensure the increments will be unique.

1 Like

I checked this solution on 200k+ entries and :make static works only for 50k records, +1 always gives 50,001

Welp, good thing there’s basically no reason to do this and the exercise is academic!


Although: Even though I’m not personally interested in this topic, I revisited the original solution (which is still correct) and got to thinking that really all we need to do is get the count of the older Sequential Orders in a way that doesn’t auto update (which is why we need :make static in that solution).

NOW, this should be possible with an SSA like Flow State Scalar (from List Popper & Friends). EDIT: it is possible and what I had written before (formatted as a quote below) was the result of a weird bug wrt plugin test apps.

But, for some reason that action is not synchronous while OTHER simple SSA’s I’ve built recently ARE synchronous with respect to downstream actions.

This is rather mysterious to me, but the net-net is I’m exploring why this is and hope to have a plugin that can help make this technique work in a much more efficient way. (That is, by just returning and locking the count rather than having Bubble actually make a static list. In case you don’t know, a search’s count can be returned much faster than the contents of the List itself.)

I don’t have this quite sorted yet, as it’s my birthday and I am now out having cocktails, but I’ll have some sort of interesting update on this here soon. (Which will either be, “oh hai, here’s a more efficient and scalable solution” or “oh, hai, here’s why we can’t have nice things”.)

And here’s my update :point_down: that explains how to do this and why it may be better than using :make static in may cases.


Happy freaking birthday @keith

1 Like

Thanks, Troy! Once the day is over, the holiday season can officially begin!

Happy birthday @keith ! You crazy mofo you!

1 Like

Woot! Sorry to hijack this thread, @morgan!

OK… further to my previous musings… While @morgan’s summary of the correct solution (posted June 2020 above) is still correct, there is in fact a slightly different way to do this that:

  1. is likely more much performant for large databases (i.e., when you start to get into huge numbers of orders or whatever it is you are assigning a sequential serial number to) and

  2. gets around the issue pointed out by @krzysztof.adamski (here) that :make static seems to crap out on lists that have more than 50K items and

  3. gets around the issue that very large searches will time out – depending on your plan, this can start happening with Search results in the tens of thousands – but while Search:...count operations, while they consume capacity and may have some upper bound, seem to support counts of over 150,000 even on personal plans.

So what’s the different approach? (Also, I’m not sure why I didn’t think of this when I created my original video, but :man_shrugging:.)

In the database trigger backend workflow, instead of setting the serial number to be Search for Whatevers (with Created Date <= This Whatever's Creation Date) :count plus one (like below):

We can instead use the Flow State Single SSA (server-side action) from my List Popper and Friends plugin to get Search for Whatever's (with Created Date <= This Whatever's Creation Date) :count. Like so:

What this does is evaluates the current count of orders immediately and returns that result to the plugin’s “Item” output. Since this never gets re-evaluated, there’s no way for that count (present at the Item output) to change during the workflow. So this accomplishes exactly the same thing as :make static in the vanilla Bubble solution.

And then our Make changes step looks like this (result of step 1 + 1):

Note that if you do this, the FLOW State Single step should be the very first step in the workflow, regardless of what your workflow needs to do later.

So, we never have to do :make static (which seems to use a lot of resources, etc.) and we’re just using the :count result of a Search… which is a less resource intensive sort of expression (at least as far as I can tell).

Now, the tradeoff here is that we’re using a server-side action plugin and of course there is a couple of seconds cold-start time when we do that (and that code isn’t already spun up). So for tiny numbers of orders, this might seem slower than the version that uses “make static” (that is, it takes a couple seconds before you see the serial number values populate) but I suspect this should be a much better approach once you get to huge numbers of orders (or whatever you’re serializing).

I want to stress again that (1) there’s really no reason to do this (making sequential serial numbers) even though there are databases that support this (and Bubble does not expose such a feature) but (2) if it’s something you want to do, I believe the approach I describe right here is probably the best/most scalable approach.

As for why I didn’t think of this in my initial video where I describe the vanilla Bubble solution, I must just have been excited that there was, in fact, a plugin-free solution to this problem, and I was really just focused on finding a solution that worked, rather than thinking about potential limitations of said solution.

(Also, in my previous musings I had said that I found that Flow State SSA did not work, however, it would seem that was something of a user headspace error. It turns out that while the plugins for List Popper & Friends looked like they were installed in my test project, they were not in fact installed – probably because I had used that project as the testing environment for that plugins at some previous point in time and then changed the test app sometime later. It seems that when one does this, the plugins still show in the editor of the old test app, but they don’t in fact run.)


The numbers start from 2 instead of 1, am I missing something?