Formulas using Count and Sum related records giving strange outcomes

[scott192]

I am encountering a very strange problem.

Here is a snapshot of a data table.

The column for Submissions is defined with a formula of COUNT([Related Submissions])

The column for SubUnits is defined with a formula of SUM(Related Submissions][WUG]) where [WUG] stands for Work Units Given and is a Decimal value.

I am lead to believe that whenever a record is updated in this table that any formula fields are recalculated and their new values are written to the table.

I am forcing this recalculation by using an Action to write the current date and time, NOW(), to the [ItemLastChange] column.

This Action is called from a Bot that fires whenever a record in the [Related SubmissionsForm] record gets Added or Updated.

Here is the Bot

The part of the Bot that calls the Action to Update the Row with NOW() is the 'Recalc WorkItem Totals' step.  As you can see, this step and the other 2 below it will always run no matter what the Conditional part of the bot does.

I have been testing this for hours and watching the sheet update as the bot performs.  I have noticed that whenever NEW rows are added to the [Related Submissions] table that the formulas to calculate COUNT and SUM update fine.  Howver, when an UPDATE to an existing row in the [Related Submissions] table happens, the formulas update 50 percent of the time with the other 50 percent where they write BLANK values to the columns as you can see in the given snapshot of the table.

I have checked the Bot Monitor, the Performance Analyser and the Audit Logs and there are no errors reported.

Can somebody enlighten me?

[WillowMobileSys]

Is the Bot set to run on BOTH Adds and Updates?

Do your actions have any "Behavior" expressions defined?  These are expressions used to determine if the action physically executes or not.

Can you show your action(s) and the expression for each used for calculation?

[scott192]

Remember, the bot runs on an Add Row to a table.  The screenshot of the bot I showed runs on adds.  The add will  set a column to be either 'NEW' or 'EDIT' and this will trigger a specific action to run.  It is the Actions that run after this, and remember, these actions will still run no matter what the value that is checked in the condition is.  I can constantly make the error condition happen...50 percent of the time.  Accurately!

[WillowMobileSys]

There isn't much about the data structure so we don't know how things are related but you mention the usage of "Related" columns so I assume you have some Parent/Child relationships.  And since your Bot runs on Adds Only, I wonder if you might be tripped up by the order of processing of Parents versus Children.

When a new Parent with Children is added,  the Parent is ALWAYS added first and then any possible Bots are triggered.  When these Bots on the Parent row run, the  "Related" columns will be blank because the Children have not yet been added into the data.

If the processing of your Parent row requires Children rows present,  you would need to delay the Processing of the Parent until all Children have been saved.  This is typically done by using a Flag column that is set by an action attached to the Form Save behavior.  The Bot is set to run when the flag is TRUE.

[scott192]

Hey @WillowMobileSys and thanks for your replies to the question.

I decided to try the same procedure, but without using the Bot and created a sequence of Actions instead.

This set of actions work flawlessly everytime so I can only assume that there is some sort of bug deep in the bot processing that is causing the anomaly I found.

If somebody from Appsheet wants to get in touch I can screenshare the whole thing if it will help in fault finding.

I am going to leave this as unsolved though as I do believe there is a bug that needs to be fixed.

[WillowMobileSys]

Awesome!  Glad you have it working.  

There are a couple of points I want to make sure you do understand in case it helps.

I understand that you have attached the shown action to the Form Save behavior.  Please note that this action is called anytime Save is tapped on the Form - that means for BOTH newly added rows and for Edited rows.

Based on what you explained about the Bot - it was running only on "Adds".  This means when you tap Save on a row that previously existed - Editing the row - IT WOULD NOT BE PICKED UP AND PROCESSED BY THE BOT.  If you expect the Bot to process both newly added rows and edits to previously existing rows, the Bot would need to be set to trigger on "Adds and Updates".

This is a big difference between your two processes.

[scott_robinson]

It's a huge problem to try and explain this though.

I am using a proxy form to add or edit records in the real table.

The proxy table is always empty and the bot always runs when a record is added....the proxy form sets a column to be either NEW or EDIT depending on the Action that was used to trigger the form add.

Once all the processing is completed the record that was added to the proxy form is deleted.

You can see that the sequence of actions that I replaced the bot with is exactly the same except that I have used a conditional run on the EDIT action and the NEW action...those actions are only available if the status column was NEW or EDIT at the time the row was added.

The actions that fill the proxy record are both of the LINKTOFORM variety and they prepopulate the added record appropriately for the action that is being done.

Like I said, difficult to explain this, but the bot works 50 percent of the time and the action sequences work 100 percent of the time.

If you'd like to DM me at some point so I can give you a step by step run through and you can view the sheets as they update I'd be happy to...you'll be as puzzled as I am then at least 😀

[WillowMobileSys]

I could tell this was likely a complicated process requiring a lengthy explanation so I didn't venture in that direction.  I was wondering what you meant by "The add will set a column to be either 'NEW' or 'EDIT' ".  Now, I think I understand.

It would be nice to solve the puzzle so I would like at some time to see if we can work together to figure out what's going on.

By the way, using actions for processing instead of Bots is a better approach WHEN the user needs to see the results right away - for the most part.  The results are IMMEDIATELY available for that user on that device.

[JPAlpano]

I had a similar problem like this and was scratching my head how to resolve it.  Like you, it worked when I used action behaviors on form saved instead of bots.  The issue is on the bot data action on set of rows.  Works well if it's an action behavior, but inconsistent if done via bot.

However, recently, I found a, IMHO, better approach (al least for me).

First, instead of using the action I made in the bot, I turned on "linking" to make the bot step reusable.  When you do that, the bot step/task will change into a dropdown where you can select the prior action you already created.

This will also cause the appsheet to create another action/behavior on top of the existing one you already made. No problem. Just rename the new one and ditch the old one.

Next, for the column you need to recalculate, I need the users to immediately see the effect when they edit the row.  The bot may take a few seconds since the column where the formula is, is a physical column. 

A virtual column updates immediately and is better to use.  But since I need to save the value into the sheet as well, this is what I did:

I added a VC and put the formula there.  Then in the physical column, I changed the formula to simply copy the VC by putting =[VC] in the app formula.  I then hid the physical column and have the VC replace it in the UX views.

This way, when values change, user immediately sees it, while the physical colum recalculates in the background and saves it into the sheet.

I wish I could explain better, but I hope you got the jist of what I'm suggesting.

[scott192]

@JPAlpano  Absolutely you explained that well and what you suggested makes sense.  But I am trying to avoid VC if I can.

The inconsistency in the bot behaviour does suggest there is a bug that needs to be found and fixed though.  I'd rather use a bot if possible because then there is only one sync on the front end and the main processing happens in the backend.  The values do update on screen after a few seconds and this is acceptable to me...4 or 5 client record syncs is not and I'm worried that users will close down their browser window before syncs finish.

At the moment this is a WIP and a rewrite  of an App that is already in production so ideally the Bot behaviour could be investigated and fixed within a few months?  Hope @Steve might be able to get engineering to take a look?

[Steve]

---

@scott192 wrote:

Hope @Steve might be able to get engineering to take a look?

---

At what? Please summarize the problem--I'm not going to wade through the entire thread.

[scott192]

You definitely should have a wade, it's an amazing read 😋

However, when a bot adds child records to a table, the master calculates the COUNT of and a SUM of perfectly fine 100 percent of the time.  But when the bot modifies a child record the master will wipe out those calculated values 50 percent of the time and delete the values...destroying data.

The bot is unlinked and the actions it carries out are bot specific.  However, if you let the bot be reused and create those same actions as behaviours it works as expected.

If instead you use behaviour actions to do the same job, the calculations work 100 percent of the time.

Another person in the thread has witnessed the same behaviour and abandoned doing the task with a bot.

I cannot summarise more, sorry.

[Steve]

I'm continually amazed at how broken Automation is.

I've escalated this issue (thanks for your summary!), but as the escalation process is new, I have no reason to expect it to work. I encourage you to engage Support as well.

[akshaybhasin]

@scott192 Hey Scott, this is Akshay from appsheet automation engg team. I tried to reproduce the issue at my end with all the understanding I could gather from reading the thread and comments, however I am unable to reproduce this behavior, for me both updates and additions work fine. I would like to request you to file this issue with support team and provide us permission to checkout the bot you had created and debug further on the issue.

[scott192]

Hey @akshaybhasin , I am just about to inform support.  Keep an eye out 🙂