- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 04, 2024 11:22 AM - edited ‎Oct 04, 2024 11:25 AM
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?
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 04, 2024 12:44 PM - edited ‎Oct 04, 2024 12:53 PM
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,"[^,]*")
REGEX_EXTRACT(verticals_from_contact,"(?:, )([^,]*)")​
REGEX_EXTRACT(verticals_from_contact,"(?:[^,]*,){2}([^,]*)")​
REGEX_EXTRACT(verticals_from_contact,"[^,]*$")​
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 04, 2024 11:39 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 04, 2024 12:44 PM - edited ‎Oct 04, 2024 12:53 PM
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,"[^,]*")
REGEX_EXTRACT(verticals_from_contact,"(?:, )([^,]*)")​
REGEX_EXTRACT(verticals_from_contact,"(?:[^,]*,){2}([^,]*)")​
REGEX_EXTRACT(verticals_from_contact,"[^,]*$")​
