Trying to create a slice for my item's current location

[Neal_MF_Harper]

I have a table for scanning ITEMs and LOCATIONs with a TIMESTAMP.

I’m trying to create a slice that lists ITEMs with the most current TIMESTAMP and LOCATION, omitting the duplicates ITEMS in the table with older TIMESTAMPS.

I’ve danced around with MAX() and SELECTS() but the logic of making it work is escaping me.

Thanks for the help everyone!

[Bellave_Jayaram]

You could try this:

[KeyColumn] = INDEX(ORDERBY(SELECT(Table[KeyColumn], LOCATION=[_THISROW].[LOCATION]), [TIMESTAMP], TRUE), 1)

[Neal_MF_Harper]

So I applied the formula below based on your feedback, thank you, I created a slice on the LOCATIONS table and it loads slow with no returned values in the slice:

[Location Key] = INDEX(ORDERBY(SELECT(Locations[Location Key], Full Location=[_THISROW].[Full Location]), [TIME STAMP], TRUE), 1)

I tried creating similar slice formulas on the items table with no luck as well.

On my related table containing the items, called MERCH, I have already setup the most current item location to display as a virtual column called [Current Location] by using this formula:

maxrow(“Locations”, “Time Stamp”, ([Merch ID]=[_THISROW].[Merch ID]))

Essentially, the merch items get scanned multiple times throughout the day at different locations, I need to create a report of the items current locations to update the old database while we transition to Appsheet while maintaining current locations in the legacy database at the end of the day…

Maybe I’m approaching it wrong, thanks again!

[Steve · Accepted solution]

Good for you for implementing Current Location! Without it, using the previously suggested SELECT() expressions would be pretty expensive.

Try this as your filter expression:

AND(
  ISNOTBLANK([Location Key]),
  ([Location Key] = [Merch ID].[Current Location])
)

The ISNOTBLANK() (and consequently AND()) is not required if [Location Key] is guaranteed to be non-blank.

[Neal_MF_Harper]

Worked perfectly, thank you so much!!! Seems I was making things more complicated than they should be, really appreciate your time and expertise!!
Best wishes Sir!
Neal