Run action on rows getting old values.

AppSheet AppSheet Q&A
1

I need to maintain the dates of Task records: The end of one needs to be the start of the next:

Screenshot 2023-05-03 at 11.54.10 AM.png
Screenshot 2023-05-03 at 11.54.10 AM.png390×348 13.8 KB

So I created an automation process that is fired when [Start] or [End] changes, so I loop all Task records to review the [Start] and [End] of all and make the necessary changes.

This is done with an automation step set as “Run a data action” → “Run action on rows”. The query is:

ORDERBY( FILTER(tasks, TRUE, [Order])

And call the action to update the [Start] and [End] fields:

[Start] =

IF( [_THISROW].[Order] <> 1,
ANY(SELECT(tasks[End], [Order] = [_THISROW].[Order]-1 )),
[_THISROW].[Start]
)

[End] =

IF( [_THISROW].[Order] <> 1,
ANY(SELECT(tasks[End], [Order] = [_THISROW].[Order]-1 )),
[_THISROW].[Start]
) + [_THISROW].[Duration]

THE PROBLEM
For some reason, only the first Task record updates correctly; the next ignores the previous updated [End] field, keeping the old End value.

For example, consider the row with [Order] = 1. If I change the [End] to 19/05/2023 19:40:00,
the next row with [Oder] = 2 will have [Start] = 19/05/2023 19:40:00 and [End] = 19/05/2023 21:40:00

But the next Task with [Order] = 3, keeps the same value, ignoring the update made in the [Order] = 2.

Is this a bug? A transaction issue?

2

“run an action on a set of rows” runs in parallel, not serially. So the result you’re seeing is expected.

If you search these forums for “looping with actions”, you can see various ways to build an action loop, which you could leverage to perform this action serially across the rows in order.

1 Like