Equipment Check-In / Out - LOOKUP vs Sort

AppSheet AppSheet Q&A
, ,
1

I am very beginner so I apologizes if these are stupid questions and I appreciate your help. I have an action set up with LOOKUP to look thru a sheet where with form submission go and updates another sheet based on the status column with matching AssetID. I have run into the problem that this only finds the first instance of the AssetID and therefore doesn’t change the status on the other sheet after the first submission. (I am working on an equipment check in / out system).

A solution for this would be either:

  • Use an expression that looks for the latest instance of the AssetID

or

  • Autosort the form submission sheet by the time and date so that the newest AssetID was always on top. ( I tried to use StaySorted on Google Sheets but it is not sorting rows added by AppSheet)

Is either of these doable? If so, how and which is easiest?

Thank you!

2

I take it that you have an expression like:

LOOKUP( [_THISROW].[assetID] , form responses , asset id , status)

But that only returns the status of the 1st record matching the asset id, and you want the status of the latest record. You can do this instead:

LOOKUP( MAXROW( form responses , timestamp , [assetID]=[_THISROW].[assetID] ) , form responses, {key-column} , status )