After deleting the row, the value of the second row's cell should be calculated

[Jani]

Hello all AppSheet Users and Helpers!

I have a challenge that I have not been able to solve.

I will try to explain the nature of the challenge as precisely as possible.

• The table now has several thousand rows, and several different users are entering data into it
• Whenever the user enters data in a row, the user's email address remains as an identifier in one column [Employee]
• User can enter several different lines for the same day, separated by project numbers [Project Number]
• The user also enters in the row the number of hours that belong to the assigned project [Working Hours]
• In the virtual column, the whole day's hours from several different projects are added together
• The normal number of hours in a day is determined to last 7.5 hours
• If the number of hours in the day goes over 7.5, then PLUS hours are counted for the day, and if the number of hours in the day goes below, then MINUS hours are counted, if the number of hours is exactly 7.5, then there are no PLUS or MINUS hours
• The user's cumulative PLUS and MINUS hours for all days are calculated in a separate column [Total PLUS Or MINUS]
• To keep the app reasonably fast, the calculation is limited to only happen when the value of the [Working Hours] column changes in a row per user
• Only the most recent calculation result that has occurred is shown to the user in Virtual Column although other values exist in the same column [Total PLUS Or MINUS] in different rows

This has been working fine, but if the user goes to delete the row that contains the last calculation result, the virtual column formula next retrieves the latest value that was previously calculated in the [Total PLUS Or MINUS] - column, and then it is not valid.

Is there a way to recalculate the sum of the next newest row in the [Total PLUS Or MINUS] column when the user goes to delete the newest row?

Thank you!

[TeeSee1 · Accepted solution]

I am guessing [Total PLUS Or MINUS]  is a regular (non VC) column, which only gets updated/recalc'd when the row is edited.

In order to edit a desired row upon the deletion of another row, you can use a bot that triggers on Data delete.

You can then run a data action - Run action on rows on its own table.

You can specify the row to update by something like 

LIST(
 MAXROW(
  "table"'
  "_RowNumber",              //assuming data is entered 'orderly' by the user
  [emp] = [_THISROW].[emp]   //adjust this filter condition as required
 )
)

You should also add a column (just a number) and an action to change this col that will be called by the bot's step above.

OR 

you could create a virtual [Total PLUS Or MINUS] in your employee table, which should minimize calculation (one vc per emp) and simplify implementation.

[Jani]

Hi Teesee1

Your guess is correct. [Total PLUS Or MINUS] is regular column and it only gets updated when row is edited.

Thanks for the suggestions, I'll try them as soon as possible. It may be that the testing goes to the next week.

Have a nice weekend!

[Jani]

I think I got it working...

I created a column [DateTimeLastCalc], which shows the time since the last calculation. The bot is monitoring this column.

The data is not entered "orderly" so [_Rownumber] is not going to work in my case, but Thanks for guidance in the right direction.

If others have the same type of problem, you should look at the formula created by the TeeSee1, and modify it to suit yourself, Thanks Teesee1!