ARRAY_FIRST and ARRAY_LAST

[Regent_Province]

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

[Justin_Barrett]

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.

[Kamille_Parks]

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

[W_Vann_Hall]

Regent_Province_de_F:

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.

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.

[Tuur]

Kamille_Parks:

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

I’d like this too…

[Tuur]

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

[Bob_Ziroll]

Update:

I got something working, based on this post. Here’s the solution that’s working for me:

REPLACE(
    ARRAYJOIN(values),
    1,
    FIND(
        "~",
        SUBSTITUTE(
            ARRAYJOIN(values),
            ",",
            "~",
            LEN(ARRAYJOIN(values)) - 
            LEN(
                SUBSTITUTE(
                    ARRAYJOIN(values),
                    ",",
                    ""
                )
            )
        )
    ),
    ""
)

The part I still don’t understand is how taking the length differences between the string and the string without (e.g. spaces, commas, semicolons) gives you the index of the last occurance of that separator? E.g. for “Joe Bob” and “JoeBob” the length difference is 1, but the index of the space is 4, not 1. I’m sure I’m missing something basic here, but that’s been confusing for me.

My Original Post:

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

@W_Vann_Hall Your formula above is working for me in retrieving the first item in the joined array. What alteration would I need to make in order to get the last item in the joined array? (I am using a Rollup field, and attempting to retrieve the latest “status” value from linked records in another table)

For example, my joined array looks something like this:

Completed,Completed,Completed,In Progress

And I’m attempting to get the “In Progress” part separated from the rest.

[Justin_Barrett]

Here’s a shorter option to extract the last value from an array:

TRIM(RIGHT(ARRAYJOIN(values, REPT(" ", 20)), 20))

The number 20 in both places can be modified based on the longest item you expect to get from the rollup, as long as both numbers are the same.

Here’s how it works. It joins the array items with a block of spaces—in this case, 20 of them—then takes the right-most X characters (also 20) from that string, and finally trims off the unnecessary spaces, leaving only the actual item you want.

Replace RIGHT with LEFT to extract the first item.

[Oli_Steadman]

Yes crucial to have sorting as a parameter; we won’t always be given our arrays in the desired order and may need to flip them before indexing.

[Max_Stuart]

Thanks for that Justin, I had to modify your fix because I ran into an odd airtable TRIM(RIGHT(…)) bug: when there were less than 20 characters (or 255 in my case as its a URL field and ya never know how much is gonna be in there with unique query strings & tracking tokens & what-not) in the resulting joined-array (i.e. the array had only 1 non-empty value) it was returning random characters from the beginning or end of the string, or worse - blank.

Hope this can help others who need to pull the most recent non-empty value from an airtable linked record array :grinning_face_with_smiling_eyes: :

(many edits later…)

SWITCH(COUNTA(values), 0, BLANK(), 1, ARRAYJOIN(values), TRIM(RIGHT(ARRAYJOIN(values, REPT(' ', 255)), 255)))

• I use this in a rollup column on the primary table that filters the target field on the secondary table where it is not empty (pic below)

[Kit_Weir]

Thank you so much for posting this Max! I’ve been banging my head against a wall for the past few hours trying to solve a problem, and this worked great.