Solved: Pull Field from Most Recent Related Record - Page 2

Andrew_Ford2 · 02-09-2021 03:00 PM

Hello Appsheet Community,

I have table caseload builder which has a one to many relationship with table site notes. I am creating a virtual column in the caseload builder. Site notes has two relevant columns: noteDate and schedStatus. noteDate is a date&time field and schedStatus is just a text.

My goal is to haee the virtual column pull the value of schedStatus from the related site notes with the highest (most recent as they can’t put one in the future) noteDate. I’ve tried constructing the below but it doesn’t quite work. If there are multiple site notes it won’t pull the right sched status.

SELECT([Related siteNotes][schedStatus],
([Related siteNotes][noteDate]=LIST(MAX([Related siteNotes][noteDate])))
)

Any guidance here would be much appreciated! Thank you!

Andrew

Andrew_Ford2

I think I figured out a solution. Although inefficient.

I created another virtual column called Max Note Date that just pulls the max note date from related site notes. Then the following formula seems to be working!

SELECT(siteNotes[schedStatus],
AND(
[noteDate]=[_THISROW].[Max Note Date],
([siteID]=[_THISROW].[UID])
)
)

View solution in original post