Using [_THISROW_BEFORE] & [_THISROW_AFTER] in automation action referenced rows filter expression

[asea_tasew]

Hello,

I have an Inventory table and a bot which triggers on all changes to this table. The purpose of the bot is updating column [Amount] of some rows of  this table it self(Inventory).  So in order to achieve this I have got a step in my bot with a type of Run a data action and a custom action of type Run action on rows. The actions Referenced table is  set to Inventory, Referenced rows is set to the expression:  

SELECT(Inventory[Inventory ID], AND(OR([Item ID] = [_THISROW].[Item ID], [Item ID] = [_THISROW_BEFORE].[Item ID]), [Date] >= [_THISROW].[Date]))

But this only updates rows that have [Item ID] same as [_THISROW].[Item ID] not [_THISROW_BEFORE].[Item ID].

FYI: I don’t know if this has anything to do with the problem. Table Inventory rows are never created, edited or deleted in a form. They are created and updated in a form saved event action of other table. And deleted by a bot.

[SkrOYC]

It's not a typo problem?

[asea_tasew]

No. it is a typo error I made when posting the question 

[Steve]

Yours is a completely new use of [_THISROW_BEFORE]; I've never seen it used that way before. I also do not understand what you're trying to accomplish. Please explain what you're trying to do using plain language, not using any AppSheet terms or expressions.

[asea_tasew]

Thanks for responding Steve. Here is another similar but simpler scenario(to explain) that I'm having same problem with. I have got these tables

• Orders(Order ID, Total) - order list
• Order Details (Order ID(reference to Orders),  Subtotal) - order details list

Orders[Total] is sum of Order Details[Subtotal] where Order Details[Order ID] is Orders[Order ID]. Orders[Total] is calculated using a bot that triggers on all changes to Order Details. Referenced rows is set to the expression LIST([Order ID]). This works perfectly except when the Order Details[Order ID]  is changed to another Orders[Order ID].

The real world scenario would be something like this: Bob orders {Milk,  Apple} and Tom orders {Tomato, Orange}, oh sorry Orange is in Bob's order. Bob's new order {Milk,  Apple, Orange} and  Tom's order {Tomato}. In order to fix this you would go edit Order Detail Orange and change its [Order ID] from Tom to Bob on the app drop down. Because  Order Detail Orange have changed, our bot  will be triggered and which will find the new Orange [Order ID] which will be Bob and recalculate the total.

But Tom's order have changed too, we need to recalculate his total. To achieve this I figured I would change Referenced rows expression to LIST([_THISROW_BEFORE].[Order ID], [Order ID]). That way the bot have both orders reference. But with no luck. It still only updates the recent order. In this case only Bob's. How do I recalculate Tom's [Total] ? 

[asea_tasew · Accepted solution]

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Track-all-changes/m-p/338876