Skip to main content

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



  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 eLocation log] table add a Lookup or Rollup field that pulls in the {Last Log Date} field from the eInput] table.


  3. In the eLocation 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 eInput] table use one or more Lookup fields to pull in the {Location} (and/or any other relevant fields) from the eLocation log] table and set the Lookup to only pull from records where {Is latest?} equals true.


  • In the eLocation log] table add a filter to show only the records where {Is latest?} equals true





  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 eLocation log] table add a Lookup or Rollup field that pulls in the {Last Log Date} field from the eInput] table.


  3. In the eLocation 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 eInput] table use one or more Lookup fields to pull in the {Location} (and/or any other relevant fields) from the eLocation log] table and set the Lookup to only pull from records where {Is latest?} equals true.


  • In the eLocation log] table add a filter to show only the records where {Is latest?} equals true




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

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


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?

Reply


Terms & Conditions