Can't find correct LOOKUP function for specific action

AppSheet AppSheet Q&A
,
1

Hi, I am looking for an expression to return one value which is used to do a calculation with.

What I try to accomplish here is the following:

I have ‘Table X’ and ‘Table Y’. There is an action that is triggered when a new row is added to ‘Table X’. This actions writes a new row to Table Y based on information of ‘Table X’. This all works fine, however, for one column I need information of ‘Table Y’. Therefore, I need to use an expression like LOOKUP (i think?)…

This expression should return a value in ‘Table Y’ that has a corresponding value (between both tables) of column [Order ID] and which has the highest value of column [OrderMonth ID] (a column in ‘Table Y’. This should always result in one particular value, which I then can use for further calculations…

2

do u want the expression to be added in appsheet or google sheet

3

Appsheet

4

for the expression to work in appsheet. the data row which you want the expression to be updated has to be edited and saved for it to save in appsheet…it will work automatically only if the said column is a virtual column

5

Thanks for this insight and you’re right. However, still figuring out the expression itself…

6

give me the headers name and corresponding table names…i shall help you with expression.

7

So for a virtual column in table ‘Orderbeheer’ in need to extract a value from table ‘OrderMaandbudgetten’.

Context:
[Order ID] is key column of ‘Orderbeheer’.
[OrderMaand ID] is key column of ‘OrderMaand ID’

There are two conditions that need to be met:

  1. [Order ID] is columns of both tables need to be the same (so the current row of the virtual column of ‘Orderbeheer’ need to have the same [Order ID] as ‘OrderMaandbudgetten’.

With only condition 1, there will be multiple matches. Therefore a second condition need to be met

  1. [OrderMaand ID] need the have the highest value.

Important: Right now I have the following expression:
MAXROW(“OrderMaandBudgetten”, “OrderMaand ID”, ([Order ID]=[_THISROW].[Order ID]))

However, I need the value of column [Resterend Order Maand Budget]. Right now it returns the key column [OrderMaand ID]. It is the right row, but not the right column value.

8

Hi @Donny_rep,

Does ‘OrderMaandbudgetten’ table reference ‘Orderbeheer’ table?

9

@Suvrutt_Gurjar @DPM_reports Already got the solution!

Still used MAXROW(“OrderMaandBudgetten”, “OrderMaand ID”, ([Order ID]=[_THISROW].[Order ID])) to get the right row.

In the action I dereferenced this virtual column (with the key value in it) to the value I need to do the calculations with!

2 Likes