Looping over several rows

Hey!
I join this task.
Before automation I had to solve a similar problem with external tools and I thought that automation would do such things by default “in a box”.
In general, such scenarios are one of the most efficient for large amounts of data with a large number of intermediate calculations.
What restrictions can there be or are there now on looped scenarios?
The other day I wanted to solve my problems on the basis of automation.

1 Like

We do plan to add support for looping over a set of rows inside a process in the future.

Currently you can process a set of rows using the for each attribute in “Scheduled” events. In this case the process that is selected in the bot will execute N times, where N = the number of rows in the selected table.

2 Likes

@prithpal A scheduled event does not work for my user case, where as part of a governance function someone is setting controls based on products that match a specific search criteria. Therefore for each matching product I have to create a new Control in the intermediary table Product_Has_Control. Hence the loop element.

I have previously implemented the loop using actions as per the comment from @MultiTech_Visions, which works. However I was hoping to use the new Automation feature as it is a little easier to comprehend what’s going on and possibly maintain going forward.

Is using Automation for loops now viable?

2 Likes

Hi @prithpal as mentioned in my previous post, using the scheduler-event as outlined above just does not work for my use case. Dishearten :slightly_frowning_face: to see the ability to create new Workflows has been disabled; I assume in readiness to switch-over to Bots.

However since the Automation I have seen does NOT support loops, why has the ability to create new workflows been disabled? :angry:

See screen-shot below of the disabled new Workflow rule

1 Like

Automation is a superset of workflow rules. Whatever works in workflow rules is supported in Automation.

Loops “are” supported in Automation just as they were in workflow rules via the “ForEach” option in the Scheduled Event.

What we were discussing earlier in the thread it that we are considering supporting loops “inside” a process → this was never available within workflow rules to begin with.

1 Like

Hi @prithpal do you have an eta on loops inside a process via Automation? I’ve implemented every version of loops using various workarounds posted by members of the community and they are surely less efficient than an official implementation would be.

We are also using webhooks but they have their own problems requiring the app to be online and execute a sync before the changes show up on the client. A loop/ForEach action that could be executed offline like other data change actions that would be the ultimate feature.

Having a proper loop/ForEach feature as an action, that could also be called by a process is my number one ask. It is the main thing that would make our lives easier and make Automation a joy to use. It would also clean up the huge amount of workaround actions and/or workflows that many of us currently have.

**Edited for clarity and to specify loop/ForEach action that can be used offline and instantly client-side.

2 Likes

This true too for any solution that uses Automation.

3 Likes

You’re right. I guess the more specific request would be to have a loop/ForEach function available as an action.

3 Likes

@prithpal as you can see in the below examples an astronomical amount of work has gone into working around the lack of a loop/ForEach action. A proper new action would also lower the barrier for entry in Appsheet. It took me a long time to understand these posts well enough to implement critical features in our apps.

3 Likes

And my every single app have lopping action that works with workflow. Sadly It is very unfortunate right now for someone who is working with developing applications for manufacturing companies.

Sometimes I have 5 rows to 15 rows to be added to another sheet using looping action. When a 3 row is added on an average its 30 rows. If it’s an action it takes atleast 1-2 minutes to complete the sync. The users use the app and they update everything faster. If they update or add like 3 orders that contains 3 products it would be approx. 90+ rows each time to be looped. That is not at all feasible if I use Looping action. Disappointed with the current update !

I agree totally with with the comments from @APiCC_Conor and @Rifadm817. AppSheet is a great platform to work with, but appears to be missing an important construct enabling Loop capability both instantly on the client-side and in the background, server-side.

I have tried 3 of the suggested work-arounds, with limited success. For my use-case: to use the Scheduled Event it would have to poll for updates every few minutes, 24/7, which cannot be an efficient approach. :thinking:

Now attempting a 4th solution iteration removing dereferences for Select statements as suggested by @Steve in an attempt to speed-up the ‘loop’ updates.

3 Likes

Just so I understand:

→ this was never available within workflow rules to begin with.

So a workflow could not call an Action that executed a series of steps Correct?

That is what I was going to attempt before the Workflow got disabled in favour of Bots. :thinking:

Just to add here is a screen-shot of the ‘new’ Bot.

The Repeat Member add does the following:

  1. Repeat member add task

    • calls → Loop (repeater) Action | Create ProductGroup Member
  2. Loop (repeater) Action | Create ProductGroup Member

    • calls → Loop Action | Create ProductGroup Member
  3. Loop Action | Create ProductGroup Member

    1. calls → Action | New 1st group member then
    2. calls → Loop (repeater) Action | Create ProductGroup Member (step 2)

@prithpal Would like to know if it is possible to trigger recursive actions from within a Bot?

Steps 2, 3.1 and 3.2 check there are remaining_members left to process.

Would welcome your thoughts. :face_with_monocle:

1 Like

No news regarding calling a recursive action within a Bot. I also have a call out with the development team. Any ideas? @prithpal

Wendy would you be able to describe what you mean by recursive actions ?

In workflows and bots DataChange actions invoked by Workflow rules do not trigger other workflow rules or bots.

Hi @Dan_Bahir apologies for the delay, was an AL.

A good example is given above in the screen-shot. In this instance products that belong to a group have to meet a specific criteria. Once identified, a record is created in an intermediary table called: ProductGroup_Has_Member

These records need creating to maintain the many-to-many relationship between ProductGroup has many products and a Product can belong to many ProductGroups.

The trigger is saving the details of a ProductGroup which may have amended the criteria. Hence the start condition: Check | Product group members to add

In the detailed outline of this discussion is mentioned looping over a list of ‘something’ until that list has been processed. Since there is no LOOP construct in AppSheet, we have to make-do with Actions calling each other. The recursive actions are outlined again below:

  • Loop Action | Create Product Group Member calls →
    Add | First Product Group Member
    Loop (repeater) Action | Create ProductGroup Member*

The action: Loop (repeater) Action | Create ProductGroup Member calls → Loop Action | Create Product Group Member hence creating the recursive loop.

But, as explained earlier, doing this on the client-side is too slow as it temporarily freezes the SAVE action, meaning the user has to wait for some of the processing to have been completed, before the using the app further.

Hence the question asked here: about placing this in a Bot, so it can be used server-side. Having tried most of the solutions outlined, still not found one that works without issues for maintaining a many-to-many relationship. Never got an answer from AppSheet as to what is the recommended approach for dealing with many-to-many relationships. I am sure there are lots of AppSheet community members who would like an answer to that question. :thinking:

1 Like

Hi Wendy,

We currently do not have a good solution for this use case. In our road map we are planning to introduce a for each step that would allow to provide an expression that would return a list of rows which then you could execute a series of steps or a sub process on.

In the mean time we will expose the “Execute an action on a set of rows” and “Execute a sequence of actions” actions that in combination might allow you to implement the looping you want.

2 Likes

If you already have a set of Actions executing the desire loop you want, you should be able to trigger a Data Change process that executes the same Action loop. That would force those actions to run on the server side within the scope of the process.

1 Like

Just wanted to add that I have the same issue here.

I’m fairly new to Appsheet (just a few months use) and while I was initially impressed with what could be achieved in a short amount of time, I now keep running into roadblocks caused by the lack of very basic functionality, particularly in Appsheet Automation.

I can understand Appsheet wanting to keep things as low-code/no-code as possible, but this shouldn’t mean ditching basic functions such as iteration (“for” and “while” loop equivalents).

EDITED: Moved my list of issues to a new topic here since most of them weren’t related to looping

3 Likes

I am new to AppSheet and am struggling to automate the creation of tasks for a related request upon request submission. Please see the attached flowchart for the desired behavior. I thought this would be an easy ask, however, upon Googling examples of Event triggered loops in AppSheet I was distraught to find this community post about how this functionality appears to be completely lacking.

Can someone assist me with a workaround, please?

Sure! First, a caution, this past week an issue surfaced (I think a bug) where iterative actions from a Bot only processed the first row. The same action set, activated on Form Save behavior, processed all rows as expected. So, I would advise to begin implementing the below on the Form Save behavior when entering a new Request. This will avoid any potential bug while creating the process and there is no lost effort this way.


Here is the looping construct I would create. It takes advantage of a recursive call ability to repeat adding Task rows WHILE there are still Categories that have NOT been added to a Task. The Steps are:

1) Create a looping control action. It basically starts off the recursion but has the check as to WHEN to stop looping. It looks like the below image - wait to add the Reference Action. That’ll be done in a later step

The critical part is the Behavior criteria that allows the action to run and stop. I used this expression:

COUNT(SELECT(Categories[Category ID], NOT(IN([Category ID], Select(Tasks[Category], [Request] = [_THISROW].[Request ID]))))) > 0

2) Create the action that adds the Task Rows. See below.

To assign the Categories, the action simply picks off the first one in the list of Categories that have NOT yet been assigned to a task for this Request. No Behavior criteria on this one.

ANY(SELECT(Categories[Category ID], NOT(IN([Category ID], Select(Tasks[Category], [Request] = [_THISROW].[Request ID])))))

3) Create a Grouped Action that calls the action to add the Task Row and then calls the “top” action to loop again. It looks like the below and no Behavior criteria.

4) Add the Grouped action from 3) to the action in 1) as the Reference Action.

5) Attach the action in 1) to the Form Save behavior and Test!


Here is an example run

Categories are:

No Requests or Tasks:

Add a Request. Immediately switching to the Tasks view shows all rows there while updates are still being made to the server. See image.


If your use case requires the use of a Bot, simply add the “loop control” action to the Process Step and you are good to go!!

I hope this helps!!

6 Likes