Using a virtual column with actions and bots

[artblackcat]

Hello,

I have a 3 table , order , order items , customer,  order items is a child to orders , and in the order dataset i have a ref virtual list called [Running Balance] which uses the expression [Virtual Column] + [ACTUAL COLUMN] .  The virtual column is the sum of all item in which is gotten from order item. 

What i wanted to do is create a bot that when ever the order status is changed , it updates the running balance column in the customer table. To do this I created an action in the customer table with the expression 

lookup(
MAXROW("Orders","_RowNumber" ,[Customer ID] = [_thisrow].[Customer ID]),
"Orders","ID","Cost after balance and payment"
)

and joined it in the table order with another action in the order table

SELECT(Customer[Customer ID],[Customer ID] = [_THISROW].[Customer])

when i run this action with a button  in the order detail view the correct amount, ie [Running Balance] which is a virtual column is updated to the customer table. But if i run it with a bot the same action will one amount value of one of the items from all possible list.

Is this a bug if not what am i doing wrong , any help would be appreciated.

[Marc_Dillon]

---

@artblackcat wrote:

SELECT(Customer[Customer ID],[Customer ID] = [_THISROW].[Customer])

---

This is in an "Execute.." action's "referenced rows" settings, correct? If so, you can easily reduce that formula to LIST([Customer])

---

@artblackcat wrote:

But if i run it with a bot the same action will one amount value of one of the items from all possible list.

---

That bolded part makes no sense, can you re-write it?

[artblackcat]

Ok ,so basically i went another way with the app to update the customer number, but the issue i was facing was basically was that the action with and expression when SUM(related list) it would print out the correct sum  , but if the  action was link to a bot, the when the SUM(related list) it would just choose the first item in the list, and just return that and not complete it. I.e. that  why i am unsure if its a bug or a feature of automation.  I would link my app here but i no longer have it as that was just for practicing

[OptimiX_XcrY · Accepted solution]

My recent similar issue with lookups was adding a [_thisrow] to the first parameter. 

I didn't understand it, but without it I got wrong findings also (in templates in my case, but in VC it worked fine without it). 

Maybe read about it in the lookup formula help page