createRecord vs. createRecords (part 2)

25 May 2020

In this post we looked at how to create records in a base using the createRecord method - one record at a time. Airtable also gives us a way to create multiple records in one go using the createRecords method.

Our method in this scenario is a little different:

define our table and a query on the table
we’ll take the query results and iterate through these one by one to form an array of records
once we’ve formed the array, we will create a set of records in one go from the array we’ve built

As before, we’ve got a set of records with first name and last name for each:

Let’s build up our script step by step. We’ll start with this:

let importTable = base.getTable('Imported table');
let importQuery = await importTable.selectRecordsAsync();
let peopleTable = base.getTable('People');

let createArray = importQuery.records.map(record => record.getCellValue('First Name') + ' ' + record.getCellValue('Last Name'));

console.log(createArray);

Here we:

define our import table, the query of the import table and the target table (People)
we then define a new array and, using the map function, iterate through the records returned by the query
for each iteration we for a new element for our new array from the First Name and Last Name, separated by a space
finally, we console.log the new array to see what it looks like

The result should be something like this:

Looking at the Airtable Scripting documentation, the createRecords method gives us this:

// Create three records in the Imported table table
let table = base.getTable("Imported table");
let recordIds = await table.createRecordsAsync([
    {
        fields: {
            "ID": "Alice",
        },
    },

    {
        fields: {
            "ID": "Bob",
        },
    }
]);

Within the createRecords method we can see that we need to pass an array (denoted by the square brackets) of objects. Each object is of the form:

{
    fields: {
        "ID": "Alice",
    },
},

So, trying to map our data onto this form, we need to form an array of objects where each object is:

{
    fields: {
        'Full Name': record.getCellValue('First Name') + ' ' + record.getCellValue('Last Name')
    }
}

Modifying our script a little, we get to this:

let importTable = base.getTable('Imported table');
let importQuery = await importTable.selectRecordsAsync();

let peopleTable = base.getTable('People');

let createArray = importQuery.records.map(record => (
        {
            fields: {
                'Full Name': record.getCellValue('First Name') + ' ' + record.getCellValue('Last Name')
            }
        }
    )
)
console.log(createArray);

Note the brackets around the object for each iteration:

record => ( // form object here )

This is required so that JavaScript knows that you are returning an object as opposed to defining a block of code.

Take a look at the output and you will see the array of objects we need:

Now, all we need to do it pass this array to the createRecords method.

let importTable = base.getTable('Imported table');
let importQuery = await importTable.selectRecordsAsync();

let peopleTable = base.getTable('People');

let createArray = importQuery.records.map(record => (
        {
            fields: {
                'Full Name': record.getCellValue('First Name') + ' ' + record.getCellValue('Last Name')
            }
        }
    )
)

await peopleTable.createRecordsAsync(createArray);

Job done, right? Well, not quite. The Scripting documentation tell us:

You may only create up to 50 records in one call to createRecordsAsync.

What do we do if we’ve got more than 50 records in our array? We’ll need to use a nice bit of code to work through the array in batches of 50 records. Replace the final createRecords line in our script with this:

while (createArray.length > 0) {
    await peopleTable.createRecordsAsync(createArray.slice(0, 50));
    createArray = createArray.slice(50);
}

Our code then uses a while loop to iterate through our records until they have all been dealt with. The code says:

“while the number of records in the array is greater than zero (createArray.length > 0), get the first 50 elements of the array (createArray.slice(0, 50)), pass these to createRecords, then remove (slice) these 50 records from the array. Go again”

Let’s imagine that our createArray has 100 records in it. On the first iteration of the while loop, the array length is 100 (greater than zero), so we get the first 50 elements, create the records and then remove these 50 elements, leaving 50 in the array. Since the length of the array is now 50, the code will run again - get the next 50, create the record and remove these 50 from the array. Now, our array length is zero, so the length condition is not met and the loop stops.

Recap

we form the array of records we want to create before the call to Airtable createRecords is made
we use map to transform our data from the query into the format required for the create method
when we have more than 50 records, we need to created in batches of 50 and loop through our array until all records have been dealt with