Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

ARRAY_FIRST and ARRAY_LAST

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Regent_Province
5 - Automation Enthusiast
5 - Automation Enthusiast

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

18 Comments
Justin_Barrett
18 - Pluto
18 - Pluto

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
16 - Uranus
16 - Uranus

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
13 - Mars
13 - Mars

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
10 - Mercury
10 - Mercury

Iโ€™d like this tooโ€ฆ

Tuur
10 - Mercury
10 - Mercury

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
18 - Pluto
18 - Pluto

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
7 - App Architect
7 - App Architect

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
4 - Data Explorer
4 - Data Explorer

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)

image

Kit_Weir
4 - Data Explorer
4 - Data Explorer

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.