Embedding other data in 'most-recent' date

[W_Vann_Hall]

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

[YYYY-MM-DD]-[$1]-[$2]-$3]-[$4]- etc.

– 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

http://domain.tld/collection/out/14934240008

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!

[Tuur]

W_Vann_Hall:

I ran into the issue of MAX(value) only applying to numeric data…

It works when you use the ‘coders’ / ISO way of displaying dates: 2017-04-26

As for the hack, seems pretty clever to me. :slightly_smiling_face:

[W_Vann_Hall]

It works when you use the ‘coders’ / ISO way of displaying dates: 2017-04-26

Good to know! (Unfortunately, once you concatenate that date with anything else, MAX(values) ceases to work, though…)

As for the hack, seems pretty clever to me.

Thanks! Every now and then, having learned to program back in the days of a shared 64K code and data space actually pays off. :winking_face:

I figured out in this case, as long as I can extract the outType value, I should be able to taylor the rest of my display sufficiently. (Again, while I anticipate using Airtable’s UI for administration, users will access the database through custom screens and forms within WordPress.)

Admittedly, I’ve made things easier on myself by declaring an item can have only a single valid active outRecord, the outDate of which must be greater than the outBack of the previous one, and the current outDue must be either open-ended or greater than the current outDate. This allows me to assume if a item has a non-zero outStatus, the MAX outDate is the current outDate, and if MAX outDue > current outDate, then MAX outDate = current outDate. Without such assumptions, it becomes nearly impossible to identify the current outDate and outDue.

I’ve thought of a few additional tweaks that can make things even more interesting, which you can find in the reply entitled ‘Update,’ following.

Thanks again,
Vann

[W_Vann_Hall]

Here are a few more variations on this hack:

Embedding options with > 10 values; embedding 2 or more options

The (X*10/X MOD 10) method originally described works fine when you have 10 or fewer states to index. An alternative method capable of supporting more states than you’ll feel like building into a multi-branching ‘IF statement’ packs data into the fractional part of a decimal:

1. Most simply, index your options using [0.01, 0.02, 0.03, etc] rather than [1,2,3], and add this index value to the numeric representation of the UNIX timestamp-formatted date. Use (X-(INT(X)) to extract the index value on the other end.

2. MOD 10 and fractional indexing can be combined to embed two values within the date. Alternatively…

3. multiple values may be embedded in the fractional part of the decimal, up to the degree of precision supported by Airtable. For instance, Option_1 might be indexed using [0.1 … 0.9], Option_2 by [0.001 … 0.099], Option_3 by [0.0001 … 0.0009], and so on. Use SUM(Index_1,Index_2,Index_3) to embed these values. To extract, Index_1 = INT(X10); Index_2 = ((X1000)-Index_1); and so on.

4. The number of values that can be embedded depends upon the number of possible values per option and the number of significant digits Airtable supports. (This latter value is no doubt documented somewhere on this site; I’m just not sufficiently motivated to hunt it down. :slightly_smiling_face: )

##Embedding a numeric value

The fractional part of the number can also be used to embed a numeric value by dividing by the appropriate power of 10.

1. For values within a known range, use the power of 10 required to ensure
   1.0 > (MaxPosVal * 1/10^(PwrOfTen)) >= 0.1'. (In other words,PwrOfTen = ROUNDUP(LOG(MaxPosVal),0)`.

2. An arbitrary numeric value X may be embedded by adding the result of X*(0.1/POWER(10,ROUNDUP(LOG(X),0)))+(ROUNDUP(LOG(X),0)/10) to the numeric representation of the Unix timestamp. To extract the value from embedded timestamp T, create three new formula fields (columns) labeled F, P, and X. Define F as (T-INT(T)); define P as INT(F*10), and define X as ((F*10)-P)*(POWER,10,P). Again, the size and precision of X will be dependent upon the number of significant digits Airtable supports.

Alternatively, one may choose to wait for Airtable to implement a MAX(values) function that supports text strings and avoid all of this nonsense. :winking_face:

[Matt_Bush]

@W_Vann_Hall nice hack!

It is in fact possible to set this up using a few intermediate lookup and rollup fields:

1. On the item table, add a Rollup of the date field from the outRecord table (which let’s call “Checkout date”), with the formula MAX(values). Let’s call it “Most Recent Checkout”.
2. On the outRecord table, add a Lookup of the “Most Recent Checkout” field from the item table.
3. On the outRecord table, create a Formula field IF({Checkout date} = {Most Recent Checkout}, Status, ''), call it “Status if most recent”. This should show the record’s status if it’s the most recent record for its item, blank otherwise.
4. On the item table, add a Rollup of the “Status if most recent” field with the formula CONCATENATE(values). Voila - now you can see the item’s most recent status.

You then may want to tweak the conditions to also check that the most recent date is before or after today, etc.

I’ll concede that 3 intermediate fields is far, far too many for this technique to be widely useful. At some point in the longer term we may introduce an ARGMIN/ARGMAX field type which will reduce this process down to 1 or 2 fields.

[shanti_rubens]

Instead of focussing on working out which date entry is most recent perhaps you can try focussing on which auto number entry is most recent. So, give each new entry it’s own line on an Item Table. Then have an auto number column, like this:

Item In | Date | Auto Number

Then you can Rollup each Item in to another Summary Table (into a column with the formula ARRAY JOIN and use the MAX Value to work out which number in the Array is the highest

ARRAY JOIN | Highest number? (using MAX Value to inspect the Array Join)

10,12,13 | 13

So, now you can do a Lookup from the Item Table to the Summary Table. If your Item matches the highest number of 13 it’s the most recent, like this:

Item | Looked Up Number | Is This Most Recent
13 | 13 | Yes

This worked for me. I hope that helps?

[W_Vann_Hall]

Matt_Bush:

On the item table, add a Rollup of the “Status if most recent” field with the formula CONCATENATE(values). Voila - now you can see the item’s most recent status.

Brilliant. Absolutely brilliant. I’d gotten about 2-1/2 steps into this, but couldn’t figure out how to make the most recent status to bubble up to the top. The idea of concatenating an array of mostly empty fields to squeeze out the sole non-empty field hadn’t occurred to me.

It’s also taken me a while to wrap my head around the idea that extravagance is OK. Old habits die hard, but I’ve noticed with each successive iteration my programming technique has [become much sloppier | increasingly come to embrace the new paradigm]. Nowadays, for instance, I don’t even blink at whipping together a formula that freely and indiscriminately intermingles presentation-specific code, static text (headers and labels), and data-driven dynamic content. (Somewhere out there, Don Knuth quietly weeps.)

I’ll concede that 3 intermediate fields is far, far too many for this technique to be widely useful. At some point in the longer term we may introduce an ARGMIN/ARGMAX field type which will reduce this process down to 1 or 2 fields.

What I’d like to see – and I can say this without compunction, as I know absolutely nothing about Airtable’s internals – would be something along the lines of a filtered lookup. I see it as working something like this:

ThisTable: [FilteredLookup][ValueToMatch]
ThatTable: [ValueToReturn][FieldToMatch]

FilteredLookup is a Formula field.
To define it, the user specifies the table from which to pull data (ThatTable), the field in the current table that contains a value to use as a key for the lookup (ValueToMatch), the field in the other table for the key to match (FieldToMatch), and the field in the other table from which to take the value (ValueToReturn).

Again, I can think of a lot of potential problems in implementing something like this, mainly having to do with data types, but it would greatly simplify things. (For me, that is; not necessarily for you. :winking_face: )

[W_Vann_Hall]

I haven’t stepped through your algorithm using my base, but I’m not sure it gets me what I need. Let me know if I’m missing something. (On re-reading my original post, I realize it was written with mud-like clarity, so I apologize for any confusion.)

In brief, I have one or more outRecords associated with a given item. At any given time, either zero or one of these outRecords is active. I need not only to identify which is the current (most recent) outRecord, but I also need to access data stored in that outRecord – the type of outRecord, for instance, or the name of the entity presently in possession of the item – and I need to do so from a view of the item.

Since I’m the sole SME/user for the system, I can make things easier on myself by fiat – for instance, by decreeing the outRecord with the latest date is the current outRecord, thus bypassing many lines of code. This allows me to identify the current outRecord using a simple MAX(values) roll-up. However, I’ve been unable to find a way to reach into the outRecord thus identified and access the data. The solution Matt Bush proposes is almost elegant in its total disregard of elegance. (It’s a veritable steampunk of an algorithm…) I’m revising my base to incorporate his code – but if you have a way to do so with a smaller footprint, I’d be eager to give it a try.

Thanks,
Vann

[Voltaic_Enginee]

This is a great solution to the OP’s problem! Any progress towards new integrated functions that would simplify things?

Specifically it would be nice to be able to differentiate beyond the MAX and MIN records. For my work I need to be able to index the records based on their dates. That might require a different schema, but I can certainly envision some kludgy workarounds similar to the one you proposed…