This is either an awfully clever work-around for Airtable’s limitations on data lookup or an incredibly convoluted method to achieve something that can easily be managed within Airtable itself, had I not overlooked something basic. You tell me…
On my current project, my main table tracks items currently within a collection. An item may currently be onsite or in one of several offsite conditions – for instance, on loan, on lease, gifted, sold, lost, out for cleaning, or out for repair. The total number of on- and offsite statuses is fewer than 10. An item may pass through any number of statuses, but it can only be in one status at a time.
As I chewed my way through the base design, I quickly realized there was substantial overlap among data required to track offsite status: Who had the item, the date the item went offsite, the date it was due back (if applicable), the date it actually returned, and so on. I created an outRecord table to log each status change for an item, with a one-to-many link from item to outRecord.
My problem came in determining the most recent offsite status. I could determine the date on which the most-recent outRecord was created (or the latest outDate or dueDate) by performing a roll-up on lookup-ed date fields, but I couldn’t think of a way to use that information to extract any non-date data from that record.
Initially, I thought I could concatenate the handful of variables I needed and use the resulting string as my primary field – something along the lines of
– define a rollup with MAX(values) to identify the greatest (most-recent) entry, and parse out the related variables, but, of course, I ran into the issue of MAX(value) only applying to numeric data. Ultimately, I decided I would have to define placeholder fields within the item table and use an external function to populate them. (I’m building the front-end for this app in WordPress using Chester McLaughlin’s excellent [https://wordpress.org/plugins/airpress/] (Airpress plugin), so some of my workflow may not be typical of most Airtable implementations.)
When I sat down to start coding the auto-populate function, though, I suddenly realized I could flip my initial solution on its head and have it work. I’d narrowed what I needed from the most recent outRecord down to the type of offsite status, outDate, and dueDate. These I wanted to display on a main item screen; anymore-detailed information could be entered or read from a popup outRecord window accessible via a link on the item window. Since only one outRecord can be valid at any time, I could pull outDate and dueDate as individual rollups; if I could find some way to pass offsite status (and, possibly, another variable or two) along with the latest record’s timestamp, I could avoid the clunky triggered data population scheme.
The solution: Rather than concatenate strings, ‘concatenate’ numbers.
Earlier, while stumbling down a few dead ends I’d glanced at DATETIME_FORMAT’s support of Unix timestamp-formatted dates, but couldn’t figure out a way to make it work. However, I now realized I could convert whatever I was using as my ‘most-recent’ date field into a Unix timestamp-formatted string, convert that string to a number, and tack on a value indexing the type of offsite status. (While my first few tests resulted in Unix timestamps with zeroed-out 1s and 10s digits, I wasn’t certain other date/times wouldn’t result in single-digit precision; in the end, I decided to multiply the timestamp by 10 before adding the index value.
For example, here is the formula I’m currently using to calculate the primary field in the outRecord table:
(VALUE(DATETIME_FORMAT(outDue,"X"))*10)+ IF(outType="loan",1, IF(outType="rented",2, IF(outType="gifted",3, IF(outType="sold",4, IF(outType="destroyed",5, IF(outType="lost",6, IF(outType="clean",7, IF(outType="repair",8,0))))))))
To extract the data thus encoded, I defined an outReasonVal variable and populated it using the following formula
outReasonVal = MOD(currentOut,10)
This leaves the status code as remainder, which can then be used to look up the applicable offsite status. Accordingly, I can now confidently say the most-recent outRecord – that is, the one tracking the item’s current or most-recent offsite status – was for an “out for repair” status, with an out date of 24 April and due date of 28 April.
(Thanks to Airpress, drilling down into this record is simple: First I configure a Virtual Post that defines how Wordpress behaves upon receiving a URL along the lines of
where ‘14934240008’ is a number representing the timestamp + index value used as a primary field for the outRecord table. (Essentially, I use that value as a key; Airpress extracts the associated record and maps it to a defined template file.)
So, again – either a clever hack or far too much effort for so little payoff: You decide.