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

[dk82734]

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

[dk82734]

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