Equipment Inspections Template: add virtual column to show latest value (by date) from another table

[ghm]

Trying to use the "Equipment inspection" Template, and in the "items" I added a Virtual columns to show the last status (by date) from "Inspections".

When I use the below it shows me all the Statuses against the "item id". aka: Pass, Fail, Pass, Pass, PAss. 

SELECT(Inspections[Status], ([Item Id] = [_THISROW].[item Id]))

Not a programmer, maybe somebody can help me?.

[TeeSee1 · Accepted solution]

Welcome to the community.

You can try this

LOOKUP(MAXROW("Inspections","Date",[Item Id] = [_THISROW].[item Id]),"Inspections","Inspection ID","Status")

[WillowMobileSys]

To get the last status assigned, I would first make sure that there is a DateTime column since I presume status could updated more than one time a day.  Then you just need to find the MAXROW by that TheDateTime and Item ID.  The expression would need to be something like this:

ANY(SELECT(Inspections[Status], [Row Key] = MAXROW("Inspections", "TheDateTime", ([Item Id] = [_THISROW].[item Id]))))

NOTE:  Replace the [Row Key] with the name of column that is the key in your Inspections table.  Replace "TheDateTime" with the column that is capturing the DateTime of the Status change.

https://help.appsheet.com/en/articles/2357310-maxrow

https://help.appsheet.com/en/articles/2347632-any