Fill existing Things with sequential values

Need help Database
1

My app is beginning to take shape and it already has a lot of data that I’ll go live with. I am beginning to add drag-drop ordering of items in many data types. In order for this to work, I am adding Position fields to define the order for displaying things in repeating groups. And, of course, values in Position fields will be changed by the drag-drop process.

Here’s my problem:
The Position fields are all empty at this point since there was no need for values before I had drag-drop ordering. How can I rip through lists and update Position with sequential values?

I know something about Make changes to a list. As I study it, I don’t see any way to update anything outside each current thing nor do I see any way to update a field based on a previous thing as the Make changes to a list process does its work one thing at a time.

One way I envision this is if I set a custom state value to 1, then with each thing processed by the Make changes to a list step, update the Position field with the custom state value and increment the custom state value.

Another way I envision this is to first have a step to set the Position value of the first thing in the list to 1. Then in the Make changes to a list step, set the Position value to the previous thing’s Position + 1.

I don’t see any facility in Make changes to a list for doing either of these.

What is a workable solution for this?

2

You’re likely going to need to use the custom event for running an API workflow on a list. In that workflow, you could change the Thing in one step, increment a counter in the next step. Then, on the next list item, it would change the Thing to the value that was incremented to in the previous step.

3

Oh, boy! I don’t know anything about creating custom events. I’ll search the manual and reference but can you share any tips for how to learn what I need to know about this? Thanks.

4

I think the big thing to search for is, “Schedule API Workflow on a list”

I believe @romanmg has some content on this one, maybe even from her newsletter.

2 Likes
5

Thanks, @andrewgassen. I’ll keep searching and check out what @romanmg may have written.

6

Hi @laurence, here’s a video tutorial you can check out about Scheduling API Workflow on a List: https://youtu.be/LLYaNHmt6_Y

I agree that’s the feature you’ll need.

2 Likes
7

Thanks for chiming in!

8

Thank you, @romanmg. I’m stepping through the video and a related one. Things are starting to become a bit clearer.

1 Like
9

Okay, so I’ve created the custom workflow to update all things in a list. I’m still missing one thing, and a new concern has surfaced.

  1. I still don’t see how to store a counter and increment it so it is available to every occurrence of a thing in the list. For now, I’m just updating the Position value to 1 for every record. I’m looking for something equivalent to a custom state that resides with the workflow for all items in the list so it can be incremented and applied to each item in succession.

  2. It can take quite a while for even a short list of 10 items to be updated. What is a graceful way of letting the user know that updates are in process so they won’t do something that gums up the data?

Thanks.

10

Hi Laurence,

You can do the following. I’m going to say you’re working with a list of Tasks:

API Endpoint needs a parameter for the Task. Key = “Task”, Type = Task. The action for this endpoint is Make a Change to “Task”: Position = Search for Task’s Positions :max + 1

So for every Task, the position field will search all Tasks, take the largest Position value and add 1 to it. Since you set all records to Position = 1, when you run this, they’ll start at 2 and go up from there.

Schedule API Workflow on List > Type = Task, List = Search for Tasks, Task Parameter = This Task, Endpoint = the one created in API Workflows (above)

As for letting the user know about the background process… unfortunately there’s no trigger for when an API Workflow that is running on a list has worked through the entire list. One idea is to store the value of the last expected position number somewhere (custom state or data record) and use a conditional loading text (or icon or popup…) that only goes away once a Task in your system has a position with that value. That way you know it has reached the end. Just a thought.

1 Like
11

Thanks, Gaby.

From your suggested approach, I take it that Bubble doesn’t support saving custom states on the server for API workflows.

Since the approach of doing a search and adding 1 to the maximum value is something that could be done in a regular Make changes to a list Action, I gave it a whirl. It resulted in all Position values being set to 2 (using a seed value of 1). Apparently Bubble does a lot or all of the work of an Action before posting any changes to the database. As a result, the maximum value was 1 even though things were being incremented locally.

I tested the approach you offered using Schedule API Workflow on a List and it works most of the time, but it is not reliable. In a few instances of testing (using different seed values to ensure that all things should have their Position values updated), some things were not incremented as they should have been. For instance, in the following example, the “seed” value was 3, so the things should have been numbered 4 - 12. The result is as though the third (value 6) and 5th (value 7) were not posted to the database before the :max value was determined.

image

Is it time to file a bug report?

12

Have you tried setting a hard delay between each item run? There’s a field in the Schedule action that lets you set an interval in seconds, which is how long Bubble will wait between each item. If you leave it blank, Bubble will determine the best interval for you, but sometimes, setting a hard number gets you more reliable results.

Obviously, don’t make it too quick. Try starting at a few seconds and work down from there to see what the most optimal time is.

is-2017-12-14-22-11-57-287

The regular make a change to a list wouldn’t be the solution here because it’s going to perform that max value search once and then update your list with that same value. So, the result of all being set to 2 is expected there. That’s why we want to “loop” this action through each item one at a time as opposed to a bulk edit that applies the same value to all.

(PS Your task list = :+1::christmas_tree:)

2 Likes
13

I might be tempted to use the sequential number plugin from the ToolBox, then schedule the APi workflow on THAT list of sequential numbers and then for each API workflow update a position that has not yet been updated.

1 Like
14

First, I thank you for sticking with me on this and for pointing out the Interval

Ouch!

I’m going to do a little kvetching here. It’s not directed at you but I need to get this out so I can move on and build apps with what Bubble offers.

I must say, coming from experience with SQL Server, Bubble’s facility with data is clumsy. There is a lot of data generation and updating that is natural with SQL. It doesn’t seem to be so with Bubble.

Using the scheduled API workflow, how would I possibly know how long an interval to use? With any value less than 5, Bubble gives the warning, “To maintain healthy system functioning, we may schedule calls further apart.”

At 4 seconds, a 12-item list will take at least 48 seconds to complete. A 100-item list would take nearly 7 minutes. And even at that, the results are not guaranteed.

I tried 3 seconds and the process for 12 items took nearly 40 seconds and the result was wrong. Two items failed to increment properly.

image

Aside from speed, the reliability problem could be eliminated if Bubble allowed storing and updating local variables. Simply providing the ability to hold a value and increment it before each call to the API Workflow would guarantee accurate sequencing. AND it would be faster because you wouldn’t need to search the full dataset to find the maximum value for each API Workflow call through the list. (The search for :max may not be terribly slow but it is currently unreliable, and is more resource consumptive than updating a single variable in memory.)

@Emmanuel, is it not possible to resolve this kind of problem in Bubble? Is this kind of problem inherent in the Bubble foundation/architecture, or AWS, or the asynchronous nature of Web processing?

Okay, that’s my frustration. Now on with solutions I can implement.

For this application, once the database is properly populated and sequence numbers properly set as new items are added, the mass updating I need now may go away. However, there may be some problems with the drag-drop elements themselves that are causing sequence numbers to get gummed up - at least I think I observed that. I need to focus on that and report in another topic when/if I understand the symptoms.

Again, I thank you for your attention and help.

15

Nigel’s suggestion is worth considering as well. The Toolbox plugin offers a “List of Numbers” element that generates a sequential list of numbers - you define the start and the length of the list.

So if you have 50 items, start = 1, length = 50, increment by 1.

You can Schedule API Workflow on that List of Numbers value. For every number, make a change to a “Task” with an empty position (or however you need to identify which Task still needs updating). Position = This Number.

Since each number is guaranteed to run only once, you shouldn’t run into any accidental duplicates. Because of that, you may also be able to run at a faster interval (or leave blank).

1 Like
16

Thanks. I’ll give it a try when I can get to it. And thanks, @NigelG, for the suggestion.

17

Nigel,

This is not working. Apparently there is something about Toolbox List of Numbers I don’t understand.

Here’s how I specified the list:

image

The result is an empty list.

image
image805×100 4.68 KB

Well, that was the breakpoint in the Schedule API Workflow step.

Here’s the actual list as far as I know:

image
image1473×255 7.56 KB

Apparently, there is something fundamental I don’t understand about how this plugin works.