Hobby Plan: How to make changes to a big list of things

[philnauta]

I have a list of 500 records each with a Start and End date.

I’ve added a field Duration and now I want to calculate it for each record.

Here’s what I’ve tried so far:

1. API… Can’t use it on the hobby plan.
2. Export database to do the calculation in Excel… Can’t export on the hobby plan.
3. Workflow to make changes to a list of things… Times out after 10 or so records.
4. 2 custom events that trigger each other back and forth… Doesn’t seem to work, perhaps because it would be an endless loop.

Any ideas? Thanks!

[keith]

Do it in a page. (Client side.). You’ll need Toolbox. Put a JavaScript to Bubble element on the page. Set its type to integer. Set its trigger and return value options to checked.

Set its function name to whatever you want. But I’d recommend index or countdown or something because what we’re doing is making a loop. (You’re going to trigger this thing with bubble_fn_whatever(some_value_to_send); )

Create an element or workflow that downloads all of your objects with the starts and ends to the page. For example, on page load set some custom state equal to do a search for all of those things. You could instead put an RG down that has that same source. Basically, you need a way to refer to the things by item number.

Create a workflow based on “A JavaScript to Bubble’s event”. (The one we just made.) it should run Only When… this JavaScript to Bubble’s value > 0.

The workflow steps will do this:

Step 1:

Make changes to a thing. Which thing? The list of things:item #This JavaScript to Bubble’s value.

Change the “duration” field to what you need. I’d recommend using the “date interval” type for your duration field as that’s exactly what date intervals are: time intervals, aka pure time measures, aka durations. Then the duration is This things end - this things start.

Step 2:

A Run JavaScript action with code:

bubble_fn_whatever(This JavaScript to Bubble element’s Value - 1);

Congratulations, you just made a while loop in Bubble.

That’s it for the workflow.

Now you just need to kick off this countdown.

Make a button on the page. When the button is clicked have it execute a Run JavaScript action with code:

bubble_fn_whatever(Your list of things:count);

So what’s going to happen is you’re setting the JS to Bubble element’s value to the number of the last item in your list.

Doing that kicks off that JS to Bubble element workflow. It will operate on the last item in your list of things. The JS to B’s value is then decremented. That kicks off the workflow again. This time operating on the second to last item in the list.

And so it proceeds until the JS to Bubble’s value is zero and the workflow will not execute.

This is the general approach to iteration I’ve hinted at before but never fully described.

Now, there’s little chance you’ll get this configured exactly right the first time you try. Using additional console.log functions in your Run JavaScript elements will help you see what’s going on. (In the dev console, of course.)

[dan1]

Option one: upgrade to a personal plan.

Option two: batch process based on a repeating group, where the repeating group has a fixed amount of items (~50). Place a constraint so only unprocessed records show up in the repeating group. Have a workflow that does the necessary update. Rinse and repeat until complete.

[mishav]

Which approach you take depends on if its a one-off, if the user is very patient (admin user), etc.

Hobby plan apps can use Data API, so there is the option of 500x Data API modify record calls. Could initiate them from javascript (security issues) or a server script (would likely timeout) or externally (for example webtask). I’d consider Data API solution as nearly impractical.

Hobby plan apps can use API Workflows, if initiating them via API Connector or externally, but without Schedule API Workflow (on a list, or recursive), its a bit impractical to process that many items on one call. Could always make 500x API Workflow calls, although I don’t see any improvement over 500x Data API calls.

@keith suggested page workflow updates initiated by a javascript loop, that should work. Disadvantage is making the user keep the page open for the duration.

@dan1

Option two: batch process based on a repeating group, where the repeating group has a fixed amount of items (~50). Place a constraint so only unprocessed records show up in the repeating group. Have a workflow that does the necessary update.

@dan1 what is your proposed workflow event trigger?

EDIT - forgot to mention the other obvious, alter your app design so that the Duration field is calculated when needed, instead of stored, as it is derived data.

[dan1]

@mishav - I read @philnauta’s request as just a one-time update to a few hundred records to conform to a new model.

So, I’d create a sandbox/admin page for working through the records. For the actual trigger, simple enough to have a button on the page that references the repeating group’s list.

Agreed that if it’s an ongoing/user-facing thing, then that would not be a recommended approach.

[keith]

I read it that way, too. Using iteration on the page does require the page to stay open. But that technique I described can be quite fast for smaller lists. (It’s how I do quote lookups in my booking widget. See https://grupz.com/vacation-rental-booking. That widget needs to loop over selected dates and a property’s fees to compute a rate quote.)

[philnauta]

Big shout out to @keith, @dan1 and @mishav for always being so damn helpful in these forums (oh, and @JohnMark while I’m at it, plus a few other geniuses whose names are escaping me at the moment). I appreciate all of you!

[keith]

@mishav

EDIT - forgot to mention the other obvious, alter your app design so that the Duration field is calculated when needed, instead of stored, as it is derived data.

Also, this! (This actually might be a lot safer as - depending on what happens in your app (e.g., do the start and end things change a lot?) - you may find yourself in a situation where you need to recompute and update the Duration field way more than you anticipated. And as misha points out, this is an implied/derived value.)

[philnauta]

@keith

EDIT - forgot to mention the other obvious, alter your app design so that the Duration field is calculated when needed, instead of stored, as it is derived data.

Ya, I’ve learned the hard way that, in some cases, calculating things on the fly in a large repeating group can be very slow:

That’s why I’ve moved away from using Expressions in this particular context and back to storing the durations in the database.

[keith]

Yeah, got it. A similar situation can arise if you’re needing to construct date ranges on the fly from a pair of dates as well (e.g., start <- range -> end).

However, if you have an RG with things that have a start and end (dates), the duration can be displayed in a text as start - end (which will construct a value of date interval type)… that shouldn’t be enormously slow.