Pull Field from Most Recent Related Record

[Andrew_Ford2]

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

[GreenFlux]

I’m guessing you got an editor error that said ‘can not compare list with date/value’, so you added LIST() around the MAX(). You were close, but the modification should have been done on the other side of the equality.

You need a date on both sides, not a list. [Related siteNotes][noteDate] is a list.

See if this works:

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

You might have to replace the MAX() input with another SELECT(). If that doesn’t work, post a screenshot of your column names and expression.

[Andrew_Ford2]

Thank you! That did not fully work. Putting _THISROW in the expression was giving me an error. I tried the following:

SELECT(siteNotes[schedStatus],
AND(
([noteDate]=MAX([Related siteNotes][noteDate])),
([siteID]=[_THISROW].[UID])
))

This doesn’t return an error but it returns every schedStatus in a list that is related to the siteID.

[Andrew_Ford2 · Accepted solution]

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])
)
)

[GreenFlux]

Glad you got it working. If you need to look anything else up from that row, it might be better to make the virtual column a direct reference to the row. Then you can dereference through that virtual column for any other values that you need.