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?
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”)