Filter records with same value on latest entry

[Jeroen_Claes]

Hey!

I have set up an base to keep track of the location of bikes. The bikes are all the same but have different frame numbers. I have made table of different possible locations that is linked to the “input” table. In this input table I gather the next values:

autonumber - Frame number - created time - location (from other table) - status (single select, broken or in use)

Every time a bike is moved, a record is made with a frame number and the new location. In this way, it’s possible to have multiple records with the same framenumber, but with a different created time and location.

If I want to see the current bikes in a specific location I need to filter out unique frame numbers and their status and location based on the latest entry. How do I do this?

Thanks!
Jeroen

[Kamille_Parks]

1. In the [Input] table add a Rollup field called {Last Log Date} that pulls in the [Location log] table’s {Created Time} field and uses the MAX(values) aggregation.
2. In the [Location log] table add a Lookup or Rollup field that pulls in the {Last Log Date} field from the [Input] table.
3. In the [Location log] table add a Formula field called {Is latest?} that uses the formula: IF({Created Time} = {Last Log Date}, true, false)

From there, do one or both of the following:

• In the [Input] table use one or more Lookup fields to pull in the {Location} (and/or any other relevant fields) from the [Location log] table and set the Lookup to only pull from records where {Is latest?} equals true.
• In the [Location log] table add a filter to show only the records where {Is latest?} equals true

[Jeroen_Claes]

Thanks @Kamille_Parks for your super fast reply!

I’m not completely getting the solution and I think that my poor explanation of the case is the cause of that. The locations list is a fixed list that I created just in the beginning. When a bike is moved from 1 (existing) location to another, the frame number is scanned/entered and the location is selected from a drop down in the form.

In the input sheet I now get a long list of movements basically. It is very likely that frame numbers show up multiple times. I want to see a list of all the unique frame numbers and where they are at the moment (and that’s the location matching with the latest entry with that frame number). From there I can derive the number of bikes per location et cetera.

I hope I have made myself more clear now. Sorry!

Jeroen

[Kamille_Parks]

I interpret this new explanation the same as I did the first time around so my solution wouldn’t change. Can you post a screenshot of the tables you’re talking about?