Help

Re: Scripting functions -- Creating/copying records

1640 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Olivia_Ehrenrei
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m trying to use the scripting app to:

  1. Get all of the records from one table (this Table is called “Incoming”)
  2. Copy all the records from the Incoming table to a different table called “Copied”

Here is my code:

let tableIncoming = base.getTable('Incoming');
let tableCopied = base.getTable('Copied');

let query = await tableIncoming.selectRecordsAsync();
let arrayOfIds = query.recordIds;
let records = query.records;
let recordsWithFields = [];

console.log('query:', query);
console.log('arrayOfIds:', arrayOfIds);

for (const record of records) {
  let newRecord = {
    "id": record.id,
    "fields": record,
  };
  recordsWithFields.push(newRecord);
  console.log('recordsWithFields', recordsWithFields);
}
/////

let recordsObject = { records: recordsWithFields };
console.log('recordsObject', recordsObject);

await tableCopied.createRecordsAsync(recordsWithFields);

However, I’m getting the following error: Invalid record format. Please define field mappings using a fields key for each record definition object

I think the data I am passing into createRecordsAsync is formatted incorrectly? I that guess correct? If so, how should it be formatted? If not, what am I doing wrong?

Also, is there any documentation? I got all of this from watching YouTube videos and reading code in other Airtable apps trying to find pieces that are relevant to me which is pretty inefficient. Any help would be greatly appreciated!

3 Replies 3

Looking at this code:

  let newRecord = {
    "id": record.id,
    "fields": record,
  };

There are two main errors that stand out:

  1. You don’t get to set the ID for a new record. Airtable chooses that on its own, so the “id” property is unnecessary and will lead to an error if included.
  2. As the error message indicated, the correct way to specify the fields for a new record is by using an object. Here’s a very rough example:
  let newRecord = {
    fields: {
        "Field Name 1": value1,
        "Field Name 2": value2,
        //etc
    }
  };

There’s no way to bulk-copy all fields and their values with a single reference. Each field must be listed separately in that fields object. That said, you don’t necessarily need to specify all field names manually. You can use the .fields attribute on a table to get all fields, then iterate through them and add them to the new record object, copying the existing value as you do so. As you review the documentation, you’ll find details about the structure of field objects and lots of other things. If you hit another roadblock, feel free to ask more questions.

In the Scripting app itself, the bottom of the app contains detailed documentation, including examples. On this reference page, go to the “Getting started with the scripting app” section, then scroll a little further and you’ll see a screenshot of what the scripting app looks like when first opened: the script editor at the top, and documentation at the bottom. If you don’t see that full documentation section, then it’s just collapsed. Click the triangle icon toward the right of the lower toolbar to expand that section.

Thanks a ton for the detailed response.
I’m trying to implement your loop over table.fields idea and copy the value associated with record[field_name] to a new object. But it seems (forgive my n00b) that record[x] where x is any field that’s not column 0, is undefined.
Is there a work around to get record[x] for any x in [f.name for f in tableIncoming.fields]
Or maybe a standalone method to make a copy of a record’s data entirely?
Thanks again!

Code

let tableIncoming = base.getTable('Incoming');
let tableCopied = base.getTable('Copied');

let query = await tableIncoming.selectRecordsAsync();
let arrayOfIds = query.recordIds;
let records = query.records;
let fieldNames = []
for (const f in tableIncoming.fields) {
  fieldNames.push(f["name"])
}

for (const record of records) {
  let newField = {};

  for(const fieldName in fieldNames){
    console.log('record[fname]', record[fieldName]) # undefined if fieldName not col 0
     newField[fieldName]=record[fieldName]
  }
  console.log('newField:',newField)
  let newRecord = {
    "fields": newField,
  };
  recordsWithFields.push(newRecord);
  console.log('recordsWithFields', recordsWithFields);
}

You need to use record.getCellValue(field) where field is either the name of the field, or the field object.

The scripting documentation covers these concepts.

You also cannot simply copy all of the fields. You should only copy fields that are editable in the target table.

There are also issues with your nested loops and hour you create the fields/newRecord object.