ARRAY_FIRST and ARRAY_LAST

I would like to use a ARRAY_FIRST() and a ARRAY_LAST() function, taking an array as argument, and resulting in the first (or the last) element of this array.

For now, I have to ARRAY_JOIN(my_array, ",") first, and the look after the string before the first “,”… which is a little tricky and need 2 formulas.

ARRAY_JOIN(my_array, ",") → my_joined_array (formula 1)
IF(FIND(',', my_joined_array) = 0, my_joined_array, LEFT(my_joined_array, FIND(',', my_joined_array) - 1)) → the final value (formula 2)

I would like to write ARRAY_FIRST(my_array) as an unique formula…

Thanks,

Rémi

1 Like

While I like this idea, my preference would be some form of array indexing, including support for negative indices to return things from the end of the array, as some languages like Python do. Instead of different array functions for grabbing the beginning and end, there would be one that could grab from anywhere in the array:

First item: ARRAY_INDEX(my_array, 1)
Third item: ARRAY_INDEX(my_array, 3)
Last item: ARRAY_INDEX(my_array, -1)
Third item from the end: ARRAY_INDEX(my_array, -3)

FWIW, the programmer in me actually prefers zero-based indexing, though because Airtable indexes strings with a 1-based index for things like LEFT, MID, etc., I’m using that style in these samples for consistency.

1 Like

I second the need for array indexing. I have had to reference the 3rd, 2nd-to-last, etc. items in an array many many times before.

It would be great if ARRAY_INDEX() had an optional third argument which allowed Airtable to sort the array alphabetically as opposed to taking the array in the order given. example: ARRAY_INDEX({My Array}, 2, 'sorted')

While not to disagree with the bulk of your post — I’ve long wished for more-robust (well, any) functions for array manipulation, though my preferences are more along the lines of @Justin_Barrett’s — you can simplify your formula somewhat:

LEFT(
    ARRAYJOIN(
        {my_array}
        )&',',
    FIND(
        ',',
        {my_array}&','
        )-1
    )

will work as a single formula.

If ‘formula 1’ is part of a rollup field, you can still enter the formula in the space where you would ordinarily enter an aggregation function:

LEFT(
    ARRAYJOIN(
        values
        )&',',
    FIND(
        ',',
        ARRAYJOIN(
            values
            )&','
        )-1
    )

Of course, in the latter case you have to take into consideration Airtable’s LIFO array structure…

Again, though, I still support the feature request.

I’d like this too…

While we’re at the subject of arrays, I’d love to see ARRAYUNIQUE() take multiple arrays and spit out the unique values.

1 Like