Help me solve this simple problem of calculating a SUM with App Formula.

[WillowMobileSys]

So the problem is simple but the implementation using API has added complexity that I cannot seemed to get around.  I'm looking for a potential resolution.

I wish to state up front:  I know I can solve this using a Virtual Column BUT I am hoping to avoid the Sync cost of needless recalculations on something that will rarely if ever change.

My Use Case

I have a Parent row on which I wish to calculate a simple sum from all of the child rows.

The implementation

When a new Parent row is added, a series of Bots are triggered in succession to add rows to several child tables.  They are added using API Tasks.  Asynchronous is OFF on all API Tasks.

The Issue

The final Bot performs a clean-up of temporary columns used for processing and recalculations, which should also re-trigger any App Formulas.  The issue is that the rows added from the API Task are not present.  I am assuming the results from the API calls are not being merged into the dataset used on this final bot.  This is also indicated by trying to include a Wait condition on "Related" column NOT being empty.  The Wait condition is Pended and the bot never finishes.

The Question

How can I implement my set of automated processes such that the results from the API Tasks are included and considered in subsequent Bot processing?

[James_McFarlane]

@WillowMobileSys 

Could you do it with AppScript instead of the bot?

Could you have a counter field in the table where you add one to it after the first part runs and it triggers an update for the App Formulas that have reset on edit?

[WillowMobileSys]

A good suggestion which I'll use if nothing can be done within AppSheet.

[dbaum]

---

@WillowMobileSys wrote:

This is also indicated by trying to include a Wait condition on "Related" column NOT being empty. 

---

FWIW: I don't use Wait conditions much, but it's plausible to me that the condition can't be based on a virtual column. If that's right, maybe that's a confounding issue in your troubleshooting.

It sounds like you already have the Trigger other bots property enabled. If not, try that.

Maybe try an automation that is triggered by the API row adds--not by direct data change actions in your existing chained automations.

[WillowMobileSys]

The Wait condition was just a test.  I think Virtual Columns can be used it just that we have to be aware that a Sync may not have occurred as expected.  This might make you think that maybe the rows are in the table but the VC just hasn't been updated yet.  Good thought but unfortunately in this use case that is not the case.  The newly added rows just are not there.

Both yourself and Marc have suggestion using API for the final clean-up and re-calc.  I'll give that approach a try.

[Marc_Dillon]

How can I implement my set of automated processes such that the results from the API Tasks are included and considered in subsequent Bot processing?

---

I would say that you can't.

But a couple suggestions to achieve your goal:

1. Clean up the temp columns in your parent table, and thus trigger the app formula re-calculation, with a final API call, instead of doing so with direct Actions. This should allow it to be able to load the previously-API-added child rows to include in the calculation.

2. Add a Bot to the child table that will trigger the parent's auto-calculation, since API calls trigger Bots. Of course doing this would likely make the calculation happen many times, unnecessarily. However this may be beneficial for other things down the road, like if one of those child records is edited, you already have the Bot set up to catch the edit and re-calc the parent.

[WillowMobileSys]

Hi Marc!  Thanks for the valuable input.

1.  Using API to perform clean-up and re-calc is a good approach that I didn't think of.  I'll give it a try.

2.  Another good idea.  In this case I'd rather keep to just one final re-calc if I can.  I'd update to the suggested approach if I find later there is additional benefit.  

[WillowMobileSys]

---

@WillowMobileSys wrote:

Using API to perform clean-up and re-calc is a good approach that I didn't think of.  I'll give it a try.

---

@Marc_Dillon 

Ok, I have this implemented and it seems to work as far as performing final update, reset of columns and a recalc.  I did struggle through several iterations of Bad Requests before realizing that in order to "blank" out columns I needed to use TEXT("") rather than just straight double-quotes.

[dbaum]

---

@WillowMobileSys wrote:

use TEXT("") rather than just straight double-quotes

---

Thanks--good tip to know.