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 nhttps://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: To extract the data thus encoded, I defined an outReasonVal variable and populated it using the following formula 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. Thanks!
(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))))))))
outReasonVal = MOD(currentOut,10)
http://domain.tld/collection/out/14934240008