I've got a table of client info (Clients) and...

[Nick_Haschka]

I’ve got a table of client info (Clients) and a table of Service info (Service Logs), where [Client ID] is a field, along with the person who did the service [Tech] and the [Timestamp], and a concatenated field that shows who and when [Detailed Description]. I’m trying to create a virtual field in the Clients table that looks up the most recent service log record for that client and brings back the [Detailed Description]. I don’t think the LOOKUP function will work because there are many service logs per client and I need it to pick the most recent one. Can someone advise the best way to do this?

[pravse]

Hi Nick,

I’m assuming ClientId is a Ref column in the Service Info table, right. So the Client table will already have a virtual column of Related Service Info.

Add another virtual column MostRecentServiceTime like this:

MAX(SELECT([Related Service Info][Timestamp], true))

Then one more:

LOOKUP([MostRecentServiceTime], “Service Info”, “Timestamp”, “Detailed Description”)