How to get latest entry in a table as a ref in another table

[jrullan]

I have a table called Items with a column Last_Location that's a reference to a Location record of the table Locations. I keep track of the item's location history in another table called Items_Locations. I want Last_Location to be read only and show the last entry for a particular item into the Items_Locations table.  How can I do this?

Items

ID

Item

Last_Location (ref)

Locations

ID

Location

Items_Locations

ID

Item_ID (ref)

Location_ID (ref)

Date

[WillowMobileSys]

In your Items_Locations table there needs to be some data that allows you to identify the latest entry - such as a DateTime column.  I fyou have that, then you can consider using the MAXROW() function.  It allows you specify which column that determines the "max" row AND allows for filtering - e.g. by Item ID.

https://support.google.com/appsheet/answer/10107920?hl=en

[jrullan]

Thanks for the suggestion. I now see that there are two parts to my problem, first one is solved by using MaxRow(), i.e.: getting a reference to the last entry in Items_Locations. However, in order to achieve my goal I need now to fetch the value in the column Location_Id of that record, so when I look up an Item I can see the related Locations record of the last Location entered into Items_Locations. 

[TeeSee1 · Accepted solution]

You can try

INDEX(
 SELECT(
  Items_Locations[Location_ID],
  AND(
   [ID] = MAXROW(....),
   [Item_ID] = [_THISROW].[ID]
  )
 ),
 1
)

[jrullan]

Thank you very much for your example! 🙂

Based on your snippet I ended up using the following, which works:

Index(
  Select( 
    Items_Locations[Location_ID],
    [ID]=MaxRow(
           "Items_Locations",
           "Date",
           [Item_ID] = [_THISROW].[ID]
    )
  ),
  1
)

Also I figured out that the column should be a Virtual Column since the value is calculated based on the query and should not be entered manually. 

Again, thank you both for your help!

[TeeSee1]

Actually yours is the better one. [ID] = [_THISROW].[ID] should be in the MAXROW expression!