Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Data Formulas
Solved
Jump to Solution
556 2
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.