Retrieve a value from related rows with condition

[eys2024]

I have a table "a" with columns: "matricole" (text) , "last firmware" (text)
I have a table "b" with colums: "matricola" (text), "data intervento" (date), "firmware" (text)
Tables are correlated 
In a row of table "a" I correctly see the related "box" with the rows of table "b" that are in relation
I need to set value "last firmware" of this table as value of "firmware" in table "b" in the row where the "data intervento" is the older value.
I have tried with combinations of MAX() and SELECT() functions but without results.
Thanks in advance

[TeeSee1 · Accepted solution]

LOOKUP(
 MAXROW("b","data intervento",[ref to table a]=[_THISROW])
 "b",
 "key of b",
 "firmware"
)

[TeeSee1 · Accepted solution]

LOOKUP(
 MAXROW("b","data intervento",[ref to table a]=[_THISROW])
 "b",
 "key of b",
 "firmware"
)

[TeeSee1]

The missing comma was a typo.

[_THISROW] by itself gives you the key of each row in table A since this formula is placed withing table A.

In your modified expression you have [ref to table a] as [MATRICOLA] and "key of b" also as "MATRICOLA. Is this correct? If you have a one to many relationship, which I believe you do according to your description, this cannot be so. Please make sure you replace my expression with correct columns.

[TeeSee1]

The above expression assumes the following..

Table a: [key of a], [latest firmware with the above expression]

Table b: [key of b], [ref to table a], [data intervento], [firmware]

You can have many b rows that reference one key in table a. The expression returns [firmware] of the row in b that has the latest/newest [data intervento] among the rows that have [ref to table a] which equals the one key.

[eys2024]

Thanks a lot,
I have tried, 

LOOKUP(
MAXROW("InterventiMisuratori","Data",[MATRICOLA]=[_THISROW])
"InterventiMisuratori",
"MATRICOLA",
"FW"
)
return an arror:
Function 'LOOKUP' should have exactly four parameters: LOOKUP(lookupValue, tableName, lookupColumn, returnColumn)

[lynchk21 · Accepted solution]

looks like you are missing a comma

LOOKUP(
MAXROW("InterventiMisuratori","Data",[MATRICOLA]=[_THISROW]),
"InterventiMisuratori",
"MATRICOLA",
"FW"
)

[eys2024]

Thanks, now the formula is accepted but unfortunately the field "lastfirmware" is  blanck, it's seems to be a correct solution, I'm definitely doing something wrong 

[lynchk21]

Without seeing your tables, it is hard to know, but it seems like the condition at the end of the MAXROW expression is missing a column:

MAXROW("InterventiMisuratori","Data",[MATRICOLA]=[_THISROW].[MATRICOLE])

Not sure what column you were trying to compare [MATRICOLA] to

[eys2024]

I also tried this last change but it still doesn't work, I know it's difficult to understand the problem without seeing the real tables, I will try to create a new test app with only the essential data, to find the solution, if I can't I will ask you again help. Thanks everyone for now

[eys2024]

I finally did it, my errors were in the key field of table b, it is Row ID and not Serial number and also the value [ref to table a] was wrong
LOOKUP(
MAXROW("InterventiMisuratori","Data",[ID MATR]=[_THISROW]),
"InterventiMisuratori",
"Row ID",
"FW"
)
Now it's work fine, thank you all for the support, I would never have succeeded without your help