Action: Trying to get the last related record using

[paullynun]

Hi

Is this something wrong with this expression when use in an action to retrieve the last related record?

LOOKUP(MAX(SELECT([Related childs][_RowNumber],AND(some stuff), “childs”,"_RowNumber",“child ID”)

When tested(emulator) in an action this expression returns nothing which is unexpected.

When I test the MAX() expression alone in a virtual field is works as expected returning the last related record.
When I test the LOOKUP() expression replacing the MAX expression by a number I get the related record.

[Steve]

Please post a screenshot of the actual expression.

[paullynun]

Hi @Steve.

Parcours is the dad and Affections is the child table. This expression fills the Affectations ID column on Parcours. On my tests, it appears MAX() returns nothing when tested at the action level (but as said previously MAX() works good in a virtual field on the Parcours table). When MAX() is replaced by a number Lookup behaves as expected.

LOOKUP(MAX(SELECT([Affectations][_RowNumber],AND([Opération statut]=“Confirmée”,[Désigné]=“Oui”))), “Affectations”,"_RowNumber",“Affectation ID”)

[Bahbus]

Use INDEX() combined with COUNT() to grab the last one in the list.

INDEX(<your list>, COUNT(<the same list>))

help.appsheet.com

INDEX()

One specific list item

help.appsheet.com

COUNT()

Number of list items

[paullynun]

Hi @Bahbus.

It appears your suggestion works. Thanks!
Is too much asking, what makes the expression I wrote wrong?

[Bahbus]

Without messing with a version of it directly myself, not entirely sure. I’ve had some mixed results with using MAX() in complex ways like this and only in virtual columns. In fact, in your expression you probably wanted to use MAXROW() instead of regular MAX(). But INDEX() and COUNT() are a little more straight forward to work with, in my opinion.

[paullynun]

Hi @Bahbus.

Thanks for sharing your experience!
You are right. I wanted to use MAXROW but I could not find a way to make it work for related records.
It appears there are some limitations for MAX() and MAXROW().