Skip to main content
Solved

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

  • October 4, 2024
  • 2 replies
  • 92 views
A
Forum|alt.badge.img+3

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?

Best answer by automator

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.


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.

2 replies

A
Forum|alt.badge.img+3
  • automator
  • Author
  • Inspiring
  • October 4, 2024

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.

A
Forum|alt.badge.img+3
  • automator
  • Author
  • Inspiring
  • Answer
  • October 4, 2024

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.


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.
Terms & ConditionsAccessibility statement