LOOKUP from Related Child Records

[gscriptTR]

Hi folks,

I have two tables. PROJECT and UPDATE. I created virtual column for PROJECTs last related child "status".

Virtual columns code:

LOOKUP(MAX([related UPDATEs][_RowNumber]), "UPDATE", "_RowNumber", "Last Status")

There is no result for this code, but if colored part gets different virtual column it WORKS like a charm!!!!

I am here, End of 2 days😭😭

[lynchk21]

LOOKUP(MAXROW("Update","_RowNumber",[Project ID]=[_thisRow].[Project ID]),"Update","Update ID","Last Status")

Change the "Project ID" and the "Update ID" values to match the appropriate key columns in your data

[gscriptTR]

I tried and i know this solution. Your MAXROW part looks all data in UPDATE table.

My goal is: Find MAX "Related Child"s _RowNumber, and LOOKUP another column.

Here the interesting part, It works in expression assistant.

[lynchk21]

Hmmm..The MAXROW() should only looking at records where [Project ID]=[_thisRow].[Project ID], which would be child records. 

LOOKUP(MAXROW("Update","_RowNumber",[Project ID]=[_thisRow].[Project ID]),"Update","Update ID","Last Status")

These columns should refer to the key column of the project table and the reference column in the child table. If these are entered correctly, the MAXROW() should not be returning values that are not child records for the row the expression is operating on. I used a similar expression previously and it returned the results I expected. 

[lynchk21]

Does 

LOOKUP(MAXROW([related UPDATEs][_RowNumber]),"Update","Update ID","Last Status")

work?

[gscriptTR]

No, MAXROW and MAX parameters is different. 

[Aurelien]

Hi @gscriptTR 

Can you try: 

1) create a virtual colunm only for this part, you can name it [_intermediate]

MAX([related UPDATEs][_RowNumber])

 2) replace your current expression with: 

LOOKUP([_intermediate], "UPDATE", "_RowNumber", "Last Status")

[gscriptTR]

Yes, this approach works but why extra virtual column🤣.

[Aurelien]

The best option is to try breaking down the expression into various columns. 

Sometime the result expected is not thrown out because there is an intermediate type that needs to be calculated.

As far as I know, there is a different calculation engine used in the app editor and in the app used, hence the difference you see.

I faced that exact situation 2 years ago.

Breaking down such expression may help into solving this situation.

Can you try splitting your expression as described above and give it a go?

[gscriptTR · Accepted solution]

End of the 3 days and hours and with AI this code works as i expected:

IF(ISBLANK([related UPDATEs][_RowNumber]),"",
 LOOKUP(MAX([related UPDATEs][_RowNumber]), "UPDATE",  "_RowNumber",  "Last Status")
)

if LOOKUP first parameter is Empty or null for some rows, function returns error for all rows