I am trying to use Shortcuts to get some info on the most recent entry in an Airtable Base using their API.
For a time, before the base had too many entries, the most recent entry was always at index 0 (or 1 as Shortcuts calls it), so this was easy. However, now the most recent changes index. Sometimes it is 1, 2, or 3.
The JSON returned has a created date and time for each entry, so I do have that data to pull, but I’m getting stumped on the logic I need to get shortcuts to figure out which one is most recent.
I’m also open to doing this as JavaScript in Scriptable if need be.
Anyone have any ideas?
Thanks.
If the last modified or originally created timestamps are important, the approach with any typical database should really be to include that in your data captured in the table. The fact that some databases may include hidden date based entries that may be accessible in some way (e.g. Oracle) isn’t generally a consideration as you have no way to maintain the data if you were for example trying to modify data retrospectively, etc. Those internal date/times are generally used for internal processing and maintenance in order to optimise data retrieval efficiency. As such the general advice in database design is not to rely on them and set your own so that you can rely on them; and because not all flavors of database do surface such data.
If you add your own timestamp(s) in your data structure, you can then include that in your query. Having the database operate on and reduce your results set to just what you need is keeping the data filtering in the “right” place as well as simplifying and speeding up your processing on the Shortcuts side.
If you really want to process the JSON, you could potentially do something like this.
- Transform the data into a set of CSV strings with the timestamp at the start of the row. Assuming the time stamps are granular enough to be unique, sort the results and return the first line.
- If you have a unique ID associated with each row of data, enumerate the JSON and compare the size of each timestamp (as a date/time or strip the separators and purely numerically). Based on the timestamp store the timestamp and ID. The ID you have at the end is the one you want so then depending on your data volume either submit a new query to return just that result or reprocess the JSON locally to find the matching timestamp.
- If you don’t have an ID, #2 can be done with timestamp only but you have to do the second processing of the JSON in Shortcuts.
Hope something in there helps.
To follow on from what @sylumer has said, Airtable has a CREATED_TIME() function available to its formulas. My approach (and, disclaimer, my approaches don’t always work because I’m an amateur) would be to create a new formula field with that as the formula (which you can hide in your views). That way you don’t need to manually enter that info, but should hopefully be able to then use it with a ‘sort’ parameter in a ‘List Records’ request (possibly paired with a maxRecords of 1) to get the record you need.
It turns out that instead of getting I sorted JSON and using the created time field for each item, when you make your request to Airtable’s API, you can pass a sort order parameter in your request. So I’ve now gotten it where the most recent entry is always at index 0 in the JSON response. Got it working again. Thanks everyone.
1 Like
Ah, cool, I didn’t realise that would work directly without an additional field! Good to know!