Unique values

18 May 2020

Let’s imagine that we have a set of records and we want to get the unique values for a particular field. In this demo, we have a number of “people” records and each person works for a company. We’ll use an Airtable script to get the unique set of companies across all people.

Here’s our initial script:

let peopleTbl = base.getTable('People');
let query = await peopleTbl.selectRecordsAsync();

let unique = [...new Set(query.records.map(person => person.getCellValue('Company')))];

There’s a few things going on here, so we’ll break it down. The first two lines define our “People” table and a query of this table to get all of the people records.

The next line of the script defines a new variable unique. We’re creating an array, which is formed by “spreading” the elements of a Set. The Set is created by mapping the records returned from the table query. Let’s take this step by step (in reverse order) to show what’s happening.

We can start with the map:

let unique = query.records.map(person => person.getCellValue('Company'));
console.log(unique);

We take the query.records array and use the map function for form a new array of the Company names. map iterates over the records (using the iteration placeholder of person to represent each record) and puts the “Company” name into the new array. When we console.log the new array we get this result:

So, our first step gets all of the “Company” names, including duplicates.

Now let’s add in the Set operation:

let unique = new Set(query.records.map(person => person.getCellValue('Company')));
console.log(unique);

From the MDN docs:

“The Set object lets you store unique values of any type”

So when we run the code with the Set operation included, we get this as the output:

As you can see, the 7 company name values have been reduced to the unique set of 3.

For the final part of the script, we use the spread operator to iterate over the Set and create an array from the Set elements. (spread can be used in other scenarios too, but we’ll ignore these for this post). spread is the ellipsis (3 dots) notation.

 
let unique = [...new Set(query.records.map(person => person.getCellValue('Company')))];
console.log(unique);

Now, when we run the script, we’ll get an array of the unique company names:

To finish off, let’s do something Airtable-y with our output. We might have another table, “Companies”, in our base:

Let’s get the unique set of companies from our “People” table and write the output to the “Companies” table. Our script would look something like this:

 
let peopleTbl = base.getTable('People');
let query = await peopleTbl.selectRecordsAsync();

let unique = [...new Set(query.records.map(person => person.getCellValue('Company')))];

let companiesTbl = base.getTable('Companies');

for(let record of unique) {
    output.text(`Adding ${record} to Companies table`);
    await companiesTbl.createRecordAsync({
        "Name": record
    })
    output.text('Record added!');
}

We define a new table for “Companies” and then, for each record in our unique array we create a new record in the Companies table (with some output text to let the user know what is happening).

Recap

• Using map to get an array of the data we want
• Using Set to get a set of the unique values
• Using the spread operator to iterate these into the elements of an array
• Iterating over the array to update a 2nd table with the unique values