Re: Select first and last (or second last) elements in array

Jonas_Tidstrom · ‎Feb 21, 2019

I want to select the first and last values from an array. The array is the results of a “rollup” of certain values in another table. Depending on the value of the last element, I might need to select the second last element.

How can I conveniently select values from an array?

In Python and other languages I would be something like x[0], x[-1] (or x[-2])…

Do I need to do “the painful job of FIND LEFT” etc. Or can it be done in a nicer way?

Thanks!

W_Vann_Hall · ‎Feb 21, 2019

Currently, Airtable provides no support for array manipulation. This leaves you with the ‘painful job’ of extracting values from a string consisting of concatenated array values. As Airtable also currently does not support any looping mechanism, this can be very painful when needing, say, to extract the 20th instance of a variable-length value.

If you have the ability to make your values fixed-length by padding with commas or 0s, do so: That will make extracting the last or next-to-last value much, much simpler. If you’re stuck with variable-length values, you’ll have to create a routine that explicitly retrieves as many values as the expected maximum size of the array, using a nested progressive search as outlined in the earlier-referenced post. To get a quick count of the number of instances in the current array, you can rollup the values using ARRAYJOIN() with a specified separator character — I typically use a character unlikely to be found in most text, such as ‘|’ — and then count the number of times the separator character is found in the resulting rollup, as so:

LEN(
    {RollupField}
    )-LEN(
        SUBSTITUTE(
            {RollupField},
            '|',
            ''
            )
        )

Here’s hoping for a robust suite of array functions in an upcoming release!

Elias_Gomez_Sai · ‎Feb 21, 2019

What about MAX and MIN?

W_Vann_Hall · ‎Feb 21, 2019

MAX() and MIN() will allow the largest or smallest values in the array to be retrieved, but they don’t provide support for retrieval by index, as the OP requires.

(For anyone reading over Elias’s shoulder unclear on what I mean by that, here’s an illustration: Start with a 5-element array with the following values:

array[0] = 12
array[1] =  8
array[2] =  0
array[3] = 17
array[4] =  9

(Airtable would return that array — for instance, if you did a rollup on {array} from a linked record using ARRAYJOIN() — as '12,8,0,17,9'. Performing a rollup using MAX(values) would return 17, while one using MIN(values) would return 0.

(The original poster, on the other hand, wanted the values of the first and last elements of the array — in this case, the values of array[0] and array[4], or 12 and 9, respectively. And that bracketed number following the word ‘array’ is known as the index of that specific array element: array[3] would mean the value of the array element with an index of 3 (in this case the fourth element, since indexing begins with 0), or 17.)

Jonas_Tidstrom · ‎Mar 07, 2019

W_Vann_Hall Thank you for clarifying and also proposing work arounds!

Mike_McLaughlin · ‎Mar 27, 2019

I think I have a work-around for extracting non-numeric/non-date values: I’d welcome feedback on the method.

Cody_Winchester · ‎Jan 29, 2020

@W_Vann_Hall, forgive me if I missed it, but once you have collected the instances of the separator using the formula you provided, how do you use that to collect the value at a specific instance of the separator (i.e. the index)? Thanks.

Clara_Morgan · ‎Mar 29, 2022

Why did Airtable conspicuously leave out this function that everyone using every programming language uses?

Is it hard to implement the very convenient “.first()” and “.last()” functions?