Help

Re: How to copy a row with rating

1969 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Rajesh_Narayana
5 - Automation Enthusiast
5 - Automation Enthusiast

I am trying to duplicate a row from one table to another (using scripts). The table I am copying from has a column with “rating” type field in it.

How do I go about duplicating in the createRecordAsync script?

I tried something like

    let table1 = base.getTable("Table1");
    let table2 = base.getTable("Table2");
    let table1Rec = await table1.selectRecordsAsync();
    await table2.createRecordAsync({
      "Memo":[{icon: "star",max:table1Rec.getCellValue("Memo"),color:"yellow"}]
    }

I got an error. Summarizing it:

“(property) “Memo”?: 0 | 1 | 2 | 3 | 4 | 5
Type ‘{ icon: string; max: number; color: string; }’ is not assignable to type ‘0 | 1 | 2 | 3 | 4 | 5’.
Type ‘{ icon: string; max: number; color: string; }’ is not assignable to type ‘5’.(2322)”

14 Replies 14

It looks like you are confusing the field options with the cell value write format for the field.

The write format for a rating field expects just a integer, not an object.

let originalRating = record.getCellValue("rating field name");
await table.createRecordAsync({"rating field name": originalRating});

Thanks. But I ended up with same error. Copying relevant bits of my code:

// Query for every record in "table1"
let table1 = base.getTable("table1");
let originalRating;
let table2 = base.getTable("table2");

let table1Query = await table1.selectRecordsAsync({
    sorts: [
       // sort by "Date" in ascending order
       {field: "Date"},
    ]
});

for(let table1Rec of table1Query.records) {
    originalRating = table1Rec.getCellValue("Memo");
    
        // Insert records in "table2" table
        await table2.createRecordAsync({
            "Memo":originalRating
        });
}

Error info:

(property) “Memo”?: 0 | 1 | 2 | 3 | 4 | 5

Type ‘number’ is not assignable to type ‘0 | 1 | 2 | 3 | 4 | 5’.(2322)

Are you sure that the “Memo” field is a rating field in table2?

Good question! Re-verified… Yes in both tables it is a rating field.

Rajesh_Narayana
5 - Automation Enthusiast
5 - Automation Enthusiast

I got it working. I just opted to ignore the error :man_facepalming: (// @ts-ignore)

Justin_Kosslyn
4 - Data Explorer
4 - Data Explorer

Related question from an Airtable beginner — if I want to copy an entire table into another table with identical columns, appending the copy to what’s already there, why doesn’t this work?

let old_table = base.getTable('Old'), old_records = await old_table.selectRecordsAsync();
let new_table = base.getTable('New');

for (let record of old_records.records) {
    await new_table.createRecordsAsync(record);
}

Thanks!

Rajesh_Narayana
5 - Automation Enthusiast
5 - Automation Enthusiast

Seems like this is similar to when I needed to create an archive of a table by running a script. It looked similar except that in the end I had to assign each field of new table the individual fields from the old table; it was something like this:

> for(let record of old_records.records) {
>       await new_table.createRecordAsync({
>           "Field 1": record.getCellValue("Field 1"),
>            .
>            .
>            .
>           "Field n":record.getCellValue("Field n"),
>       });
> }

In the end, I think your idea is correct except for the fact that you need to explicitly tell the script how the fields of both the tables co-relate; at least this is the main difference I can observe :slightly_smiling_face:

@Justin_Kosslyn To answer your question, the Record objects returned by selectRecordsAsync aren’t exactly the same as the expected input for createRecordAsync, so currently you’ll have to explicitly read each cell value as @Rajesh_Narayanan suggested.

You should also make sure that you only try to copy editable fields – not any computed fields. When you read the records, you will get all records, both computed and editable. If field options have changed for some fields between the two tables, (such as adding an option in a select field) creating records can also fail or have unexpected results.

Also, Justin’s code sample uses createRecordsAsync with a single record. When using createRecordsAsync (with the s) you need to pass in an array of records/field-objects. When using createRecordAsync (without the s) you pass in only a single record/field-objects.

Got it, thanks folks!

Is there a way to use a for loop to iterate through all of the fields instead of having to hardcode each one in?

Thank you!!
Paul Warren

I tried using your suggestion for 2 tables with identical rows and I’m getting this error on a multi-select column called ‘Genres’:

L: Can't create records: invalid cell value for field 'Genres'.
Could not find a choice with that ID or name

The code and tables are really simple:

let table = base.getTable("Source");
let query = await table.selectRecordsAsync();
let targetTable = base.getTable("Target");

for (let record of query.records) {
    await targetTable.createRecordAsync({
        "Book": record.getCellValue("Book"),
        "Genres": record.getCellValue("Genres"),
        "Cover": record.getCellValue("Cover"),
    });
}

Any ideas?

@TWu

Check out the answer in this thread.

You will need to adapt code slightly because you will need to map the arrray. But the idea is the same—the ids are different in the two tables, even though the option names are the same.

TWu
6 - Interface Innovator
6 - Interface Innovator

Thank you, that worked perfectly!