Last Record View

[Scott_Mertens]

Looking for a way to quickly find the last record. I can do this in a view with a filter, just not sure of a formula for the newest record. Really what I am looking for is a way to find a record older than 60 minutes. This is a dispatch log, when there is no communication between a security officer and dispatch within the last hour. Dispatch is required to radio the officer and make contact to be sure everything is alright. Or any idea to show the last record, or only show if it is older than one hour.

There are many cars patrolling, and a ton of communications. This makes it difficult to find the last record for all patrol officers. Looking for any ideas.

[kuovonne]

You can create a ‘created time’ field. Then set the filter to sort by the created time. You may also find a ‘last modified time’ field to be useful.

You can also have a formula field compare the created or last modified time with NOW() using the DATETIME_DIFF() function. Note that NOW() is only updated periodically, from between every 5 minutes to an hour depending on whether or not the base is open and the state of Airtable’s servers. This NOW() is usually several minutes in the past. You can see the formula field reference for more info.

[Scott_Mertens]

Thanks, is there a way to show ONLY the last call for each unit? Grouping by unit I still have a long list to look through.

[kuovonne]

It depends on the setup of your base. Are the units stored in a different table, with linked record fields connecting the [Units] with the [Calls]? If so, yes, you can use a system of rollups and formula fields to identify the last call for each unit. However, if your base has only one table with the unit a single select or text field in the same table as the calls, then there is no way to show only the last call per unit.

If you have a two table system

1. create a rollup {latest call time for unit} in the [Units] table to show the MAX(values) of the time for the calls.

2. pass that {latest call time for unit} from the [Units] table back to the [Calls] table using a rollup or lookup.

3. In the [Calls] table have a formula field that compares the {latest call time for unit} with the call time for that record and indicates if the values are the same. You can then filter based on the value of this formula.

Note that you can combine steps 2 and 3 in a single rollup field, but some people are more comfortable using a lookup field and a rollup field.

[Scott_Mertens]

I guess I don’t understand this. If I split the tables and put a max call time in the units table, then pass that back to the Dispatch table. It is passing the value it just looked up to the same record. I will always be comparing a record to itself, and the times will always match. I’m missing something here?

[kuovonne]

In the scenario I described, you would have multiple calls linked to the same unit. You would pass the max call time for the unit back to all of the calls for that unit. Then each unit would compare its call time for the unit with the max call time for the unit to see if it was the last one.

If your base is not set up for multiple calls linked to each unit, this system will not work. Can you give a bit more insight into your base design?

[Scott_Mertens]

OK I think I got you now, just need to figure out the comparison formula.! I’m attaching a screenshot of the first few records in the bass. At first it was one table functioning much like a spreadsheet. Based on your first response I seperated out the units, made a single select in the dispatch

Screen Shot 2021-03-06 at 11.17.02 AM|570x500