Help

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

Treat Arrays/Sets as First Class Data Types with Proper Formulas!

cancel
Showing results for 
Search instead for 
Did you mean: 
dk82734
7 - App Architect
7 - App Architect
Status: New Ideas

I have spent too many hours working around problems like:

  • Converting comma-separated strings to unique elements in an array/set
  • SORTING elements in arrays/sets properly
  • Comparing to different arrays/sets in order to determine intersection/difference

NONE of the built-in Airtable formulas support these use-cases right now.  Could we PLEASE get new Formula functions like:

  • ARRAYINTERSECT(arr1, arr2, ...) - outputs the subset of values in each array that are uniquely present across all input arrays
  • ARRAYDIFF(arr1, arr2) - outputs the subset of unique values that are in arr1 but NOT in arr2
  • ARRAYSORT(arr1, sort_type) - sorts the values in the array by specific sort type (e.g., alphanumeric ascending/descending, chronological ascending/descending, integer ascending/descending)
  • ARRAYINSET(arr1, value) - returns TRUE() if value is present in arr1, FALSE() otherwise
1 Comment
dk82734
7 - App Architect
7 - App Architect

Okay, I'm going to take this lack of acknowledgment by Airtable product teams as a sign that this problem isn't getting fixed anytime soon.  Therefore, I'm posting my exceptionally ugly hack/workaround for ARRAYDIFF() using none other than my favorite hack.. REGEX_REPLACE()

Note: Airtable doesn't recommend using formulas that have many calls to REGEX_REPLACE() as it causes performance problems on their end, server-side when rendering computed content. However, when they fail to provide any alternative solutions, I am left with no other choice than to use this hack. (Maybe if enough people use this hack, it might convince Airtable engineering to actually support a proper ARRAYDIFF() function?)

Assumptions: In order for this hack to work, I assume you have the following setup in your table:

  • {Linked_Records_Field_1}: An array of linked records
  • {Linked_Records_Field_2}: Another array of linked records
  • Both fields are linking records to the same underlying table
  • The primary field for each linked record does not contain the following consecutive characters: ~~ or ,
  • If any of your fields double tildes in them, then you'll have to swap out the formula below with some other unique character sequence that is not present in any of the primary fields
  • If any of your fields contain commas in them, then this formula WILL NOT work

Create a new field using the following Airtable formula:

REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE("~~" & ARRAYJOIN({Linked_Records_Field_1}, "~~,~~") & "~~", "~~" & ARRAYJOIN({Linked_Records_Field_2}, "~~|~~") & "~~", ""), "~~,+~~", ", "), "^[,~]+|[,~]+$", "")

This formula will output the set of values (comma separated) in {Linked_Records_Field_1} that are NOT in {Linked_Records_Field_2}

Enjoy! (sigh) /s