Embedding other data in 'most-recent' date

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:

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

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:

@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.

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?

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.)

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: )

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

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…