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 |
Solved! Go to Solution.
You can try
INDEX(
SELECT(
Items_Locations[Location_ID],
AND(
[ID] = MAXROW(....),
[Item_ID] = [_THISROW].[ID]
)
),
1
)
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
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.
You can try
INDEX(
SELECT(
Items_Locations[Location_ID],
AND(
[ID] = MAXROW(....),
[Item_ID] = [_THISROW].[ID]
)
),
1
)
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!
Actually yours is the better one. [ID] = [_THISROW].[ID] should be in the MAXROW expression!
User | Count |
---|---|
25 | |
25 | |
24 | |
21 | |
20 |