Help

Re: ARRAYSLICE is not working with lookup, linked records, or multi-select fields

Solved
Jump to Solution
385 0
cancel
Showing results for 
Search instead for 
Did you mean: 
automator
5 - Automation Enthusiast
5 - Automation Enthusiast

I have been reading for hours through the help forum and documentation, but ARRAYSLICE doesn't want to work for me.

I want to achieve the following: Show me the first value of an array.

 

My formula is:

 

ARRAYSLICE(verticals_from_contact, 1, 1)

 

but it doesn't show me the first value of the array. Instead, the field shows me the whole array. If I change it to:

 

ARRAYSLICE(verticals_from_contact, 2, 2)

 

nothing is shown anymore. 

 

I tried the formula on lookup fields, linked records, and multi-select fields, and nothing worked (see image). Does anyone have an idea what's wrong?

Screenshot 2024-10-04 at 1.24.39 PM.png

1 Solution

Accepted Solutions
automator
5 - Automation Enthusiast
5 - Automation Enthusiast

I found the solution, and I hope it helps everyone who faces the same issue.

We need to extract the values with REGEX. The formulas to extract the first, second, and last values are as follows:

Extract first value:

 

REGEX_EXTRACT(verticals_from_contact,"[^,]*")

 

 
Extract second value:

 

REGEX_EXTRACT(verticals_from_contact,"(?:, )([^,]*)")​

 

 
Extract the third value:

 

REGEX_EXTRACT(verticals_from_contact,"(?:[^,]*,){2}([^,]*)")​

 

Extract the last value:

 

REGEX_EXTRACT(verticals_from_contact,"[^,]*$")​

 

 
I have no idea how this works, but after endless trial and error, it extracts the correct values for me.

See Solution in Thread

2 Replies 2
automator
5 - Automation Enthusiast
5 - Automation Enthusiast

For anyone who will read this in the future, I just made the following learning:

According to the Airtable documentation "Array functions can only be used in rollup fields or when the input field is a lookup field."

This is misleading and not 100% correct. The lookup field's underlying field is a multi-select type, and ARRAYSLICE doesn't seem to work with this type.

ARRAYSLICE does work with text fields of lookup fields (e.g. name, etc.), and not other field types.

automator
5 - Automation Enthusiast
5 - Automation Enthusiast

I found the solution, and I hope it helps everyone who faces the same issue.

We need to extract the values with REGEX. The formulas to extract the first, second, and last values are as follows:

Extract first value:

 

REGEX_EXTRACT(verticals_from_contact,"[^,]*")

 

 
Extract second value:

 

REGEX_EXTRACT(verticals_from_contact,"(?:, )([^,]*)")​

 

 
Extract the third value:

 

REGEX_EXTRACT(verticals_from_contact,"(?:[^,]*,){2}([^,]*)")​

 

Extract the last value:

 

REGEX_EXTRACT(verticals_from_contact,"[^,]*$")​

 

 
I have no idea how this works, but after endless trial and error, it extracts the correct values for me.