Clever people! Help me with this simple expression please (Reference to get an array and then extract the latest date)

AppSheet AppSheet Q&A
1

Hi Guys.

I have a CUSTOMERS table and a REPORTS table.
REPORTS references CUSTOMERS so I can see all the related reports per customer in the customer view.

Now I want to create a virtual column in my CUSTOMERS table which reflects the latest [Date] field between all the REPORTS entries for that Customer.

I suspect you first need use an expression to find the array of REPORTs that have the specific CUSTOMER as a field and then plug another expression in that extracts the latest date from that array.

I’ve been trying these kinds of expressions for some time but have basically resolved to leaving it to the experts. Please give a man some help.

Regards
Gerhard

2

Please try the following expression in the VC
MAX([Related Reports][Date])

3 Likes
3

This is perfect, Suvrutt.

Many thanks.

I realised there is another layer that could be of great help. In the REPORTS table is a field called [Report Type]. If I wanted to do the MAX() together with an IF() that only considers the dates where the [Report Type] = “Scheduled Maintenance Report”, how would I go about that?

Regards
Gerhard

1 Like
4

Please try

MAX(SELECT([Related Reports][Date], [Report Type] = “Scheduled Maintenance Report”))

2 Likes
5

Thanks a mil Suvrutt.

2 Likes