String/Set Membership Check

Hi there,

So I feel like the Airtable formula support is severely lacking in a couple of ways, but here’s a really painfully obvious one:

Let’s say you have a single base, single table, and there are two columns: Current Members and Valid Members.

The Current Members column contains a comma-separated list of current members, like Bob, Sally.

The Valid Members column contains a comma-separated list of ALL allowed members, like Bob, Susan, Tim, Jim

Now, I want to create a third, formula-powered column called Are Current Members Valid? and that will return 1 if all Current Members are present in the Valid Members column; 0 will get set otherwise.

In order to implement this correctly, I sort of need to:

1. Collect each Current Members item as an array – which means I need some sort of SPLIT() formula that will split a string by each comma into a string array

2. Iterate over each item in the string array and check to see if each item is present in the Valid Members column

Because no Airtable functions exist that can do either 1 or 2, I’m forced to have to implement something far more complicated as a script automation, which makes this way more complicated than need be.

This use case doesn’t seem that unique – in fact, this use case seems to be pretty common.

Am I missing something here, though?