Updating dependent rows

[Power_Manage]

Hey

I have a table to follow cash transactions and balances for a few places.
Each row has an open balance ( with a sum( select()) formula) , daily transactions , and closing balance.

Depostis are handled in another table , with a bot ,which triggers an actions to update the relevant row on cash table with the deposit and the rows after the date which balnces should be affected.

However , when runing the bot , open balances , will only get updated for the first row, unless runing the bot more than once. This probably happnes becuase the previous row hasnt been updated on time to affect the open balnce of the next row.

The action im runing is excecute an action on a set of rows , which triggers update coolums in this row , which updates it , and app formulas start to work.
However , if i enter a deposit for an old date , all the newer rows will not get updated , unless i run the bot , as the amount of days that have passed.

anytone has better suggestion for this mechanism ?
Thanks

[WillowMobileSys]

Power_Manage:

However , when runing the bot , open balances , will only get updated for the first row, unless runing the bot more than once. This probably happnes becuase the previous row hasnt been updated on time to affect the open balnce of the next row.

Ok, if I gather correctly, you are expecting cascading updates - a row is updated so all rows after need to be updated as well.

It also sounds as if you are expecting the bot to be retriggered automatically for these updates - row A triggers bot and updates row B - row B update should re-trigger bot again.

Unless something has changed, (there have been a number and hard to keep up), updated rows within a bot do not re-trigger another bot run. I vaguely recall discussion around this so I could be wrong.

If the above is true, then you will want to re-structure your bot’s actions to handle the cascading updates. I believe that it CAN be done.

It would be extremely helpful if we could see your bot and actions - the important parts are the expressions, where they are in your bot structure and what they do. Then the community can analyze and help solve for your need.

[Power_Manage]

Hey there , Thanks for the reply , and sorry for the late response.

My cash table open balance has the following cell formula :
SUM(select(Cash[Current],AND(
[Station]=[_THISROW].[Station],
[Date]=[_THISROW].[Date]-1,
NOT([Status]=“Deleted”)
)))

The action Data: execute an action on a set of rows , has a formula for referenced rows:
SELECT(Cash[ID], AND(
[Station]=[_Thisrow].[Station_UID],
[Date]>=[_Thisrow].[Date Value]
))

And the deposit column on Cash table , has the following app formula :
Index(SELECT(Deposits[Deposit_UID], AND(
[Station_UID]=[_Thisrow].[Station],
[Date Value]=[_Thisrow].[Date])),1)

So generally if a new deposit is added to deposit table , I would like to update the deposit column on Cash table , and all the effected rows and balances.

[WillowMobileSys]

Power_Manage:

The action Data: execute an action on a set of rows , has a formula for referenced rows:

So your expression to select rows SEEMS ok. But what are the actions you perform to do the actual row updates?

Power_Manage:

And the deposit column on Cash table , has the following app formula :

Unfortunately, App Formulas do not automatically fire to update. There has to be a physical update to the rows. It can be as simple as resetting an Update Date column.

[Power_Manage]

Hey
The action it triggers is set the values of some collumns in this row .
And it set up a collumn Status to : CONCATENATE(“Updated”," " ,NOW())
This is for tracking so i know which rows are updated and when .

Yes it is being updated , but since , ending balance has not yet being updated on the database, next row will not know yet about it . So I will need to update it aain , and have the bot run a few times.
This seems odd , as if i will insert a deposit which occured 30 days ago , i will need to run the bot 30 times , to catch each row cange .

[WillowMobileSys]

Power_Manage:

Do you have any idea ?
I’m quite lost in this

Sorry, I didn’t notice your reply. Just a side note…if you use the MAIN Reply button (the one at the very bottom of the post), others in your thread will only see the comment if they had tagged the post to watch. I typically don’t do that.

Power_Manage:

This seems odd , as if i will insert a deposit which occured 30 days ago , i will need to run the bot 30 times , to catch each row cange .

Indeed it is AND AppSheet DOES NOT currently support bots triggered by other bots. Meaning that you would not be able to implement a bot to update a row and then expect another trigger of a bot based on that row update. It won’t work that way.

Instead you will need to to implement an action of type “Data: execute an action on a set of rows”. This action will identify the set of rows to be updated and pass them on to a second action (the “Referenced Action”) to perform the updates.

If the rows are passed to this second action in the proper updating order, I BELIEVE, that you can grab the updated value from the PRIOR row to update the current row. Thos updates will remain in the table so that you can walk down the the list of rows and update each one.

I’ve never processed in this manner but based on my understanding I think it will work this way.

Bottom line - a single trigger of the bot and then a set of actions to perform all of the updates.

[Power_Manage]

Yes this is what I am currently doing , but it bring the balances of each row during the update, prior to the update. So the formulas will not catch the updated values during the update.
Is the another solution , besides using VC that will probably slow down the app ?
Or maybe better to divide for a few tables to simple things ?

[WillowMobileSys]

Power_Manage:

but it bring the balances of each row during the update, prior to the update. So the formulas will not catch the updated values during the update.

I don’t understand what this means. Can you include an image of your update action and show the expressions you are using to update the values?

EDITED: Oh, I think I understand. You are getting the old values not the updated ones. I could have been mistaken thinking you could see the updated values. I know I can see updated values for rows in different tables

In the same table, for rows of the same action, it may be that you don’t because I think AppSheet sends all rows in bulk and therefore that action only knows the row state at the time they were supplied. There are a couple of ideas I’ll try to see if we can get around this.

[WillowMobileSys]

Ok, so I built a test to perform cascading row updates. I am finding that when I attach the Action that kicks off the updates to the Form Save behavior, all the updates through all the rows happen as expected.

BUT, If I run that same action from a bot, only the first row updates. No errors are reported.

I believe this is a bug. I think the action should run in the same way even if run from a bot and happening on the server.

Thoughts?

[Power_Manage]

Interesting , I will check it and see on my side as well this way.
Maybe it will solve the issue.

[Steve]

WillowMobileSystems:

I believe this is a bug. I think the action should run in the same way even if run from a bot and happening on the server.

Attn @Dan_Bahir @prithpal

[Dan_Bahir]

Could you send a note to support@appsheet.com with the information of your test app. I would be happy to look into what you are reporting.

Thanks

[WillowMobileSys]

Dan_Bahir:

Could you send a note to support@appsheet.com with the information of your test app. I would be happy to look into what you are reporting.

Yes, I’ll create a simpler test app (the current one has a bunch of other testing stuff) and get that sent over.

[WillowMobileSys]

Dan_Bahir:

Could you send a note to support@appsheet.com with the information of your test app. I would be happy to look into what you are reporting.

On issue of where set of actions behave differently when run on Form Saved versus run by Bot:

@Dan_Bahir I have finally created an example app and sending it over to support@appsheet.com. I took the time to create a tester app to make it easy to reproduce the issue. Please check your email for the specifics.

[Dan_Bahir]

Thanks, much appreciated.

[Power_Manage]

Ok , so I can confirm , when running the action from the saved form trigger , and not from the bot , it updates all the rows 1 by 1 as predicted , working fine.

Thanks a lot Willow , it helped me to solve the issue on my app.

[WillowMobileSys]

Power_Manage:

when running the action from the saved form trigger , and not from the bot , it updates all the rows 1 by 1 as predicted , working fine.

Awesome! I have opened a bug to AppSheet for the case where the same set of actions are run from the Bot and only the first row updates. I believe it to be a defect.

[Power_Manage]

what do you think ?

[Dan_Bahir]

Hi @WillowMobileSystems,

We released a feature that allows changes in a bot to trigger other bots a few months ago New feature: Trigger other bots.

It does not allow to trigger recursively so the same bot cannot trigger itself.

[WillowMobileSys]

Dan_Bahir:

We released a feature that allows changes in a bot to trigger other bots a few months ago New feature: Trigger other bots .

This is what I remember but I don’t recall an announcement that it was moved into production. I might of missed it?

Dan_Bahir:

It does not allow to trigger recursively so the same bot cannot trigger itself.

Understood. I’m sure the reason for this is to prevent infinite loops but there must be another way to handle that problem that doesn’t limit our ability to take advantage of the recursion. Runaway recursion is dealt with in other systems in some way,

[Power_Manage]

Hey there .
Do you have any idea ?
I’m quite lost in this