ARRAY_FIRST and ARRAY_LAST

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.

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.

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.

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

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

Completed,Completed,Completed,In Progress

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.

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.

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

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)

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.

This is great! Way better than the adapted solution I had above (and actually makes sense to me).

Hard to believe Array maniuplation are still not possible in 2022. Such a pain

Maybe it would bring new ideas. Explanation is simple - SUBSTITUTE has 4th parameter - number of the occurence. If omitted, it changes all. That’s why it used often in formulas, where it seems nothing to substitute. Like here, with FIND, to Find last comma.

Now that regular expressions are available, getting the first or last item from an array is a lot easier (at least when the items don’t contain commas):

First item:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*"))

Last item:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$"))

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*"))

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$"))

Clever. Note that you can still handle Rollup arrays with commas by using ARRAYJOIN(values, 'some other deliminator')

Here’s a more general formula to get the nth. field from a Rollup/ Array/ Multi-Select field in Airatble (e.g. first/ second/ third/ …)

To get the 1.

REGEX_EXTRACT(ARRAYJOIN({values}), "^([^,]*)")

To get the 2.

REGEX_EXTRACT(ARRAYJOIN({values}), "(?:[^,]+,){1}([^,]*)")

To get the 3.

REGEX_EXTRACT(ARRAYJOIN({values}), "(?:[^,]+,){2}([^,]*)")

…
etc.
…

To get the last

REGEX_EXTRACT(ARRAYJOIN({values}), "([^,]*)$")

Yeah, it kind of makes sense (from a programmer’s perspective) for the first item to be index 0; then -1 is the one right “before” that (in an imaginary wheel), which of course is the LAST item. But for most folks working in Airtable, your suggestion of the first item being index 1 is, I think, appropriate, even if it is a bit like the Julio-Claudian calendar, which has no year 0. :slightly_smiling_face:

Some time in late 2023 they quietly added the new ARRAYSLICE() function which allows you to do this.

For example, ARRAYSLICE({Your Field}, 1, 1} will return the first value, while ARRAYSLICE({Your Field}, -1} will return the last.