Use Button Field to Move Record from One Table to Another Table

[Jerry]

Hi all,

Hoping someone could help me correct a script. I have a base with multiple tables in it. There is a table called “Projects” and another table called “Potential Photoshoots”. I’m trying to use a script to move a single record from the “Potential Photoshoots” table to the “Projects” table. It should then delete the record from the “Potential Photoshoots” table. This all should happen automatically when a button is clicked. NOTE: The button field name and the button label are both titled “Move to Projects” and it exists on the “Potential Photoshoots” table.

I’m new to Airtable and not very proficient with scripts, but I’ve been doing research and playing around to try and learn. I was able to work out the following script, but it doesn’t move the specific record whose button was clicked. From what I understand, that is because I need to use an input.recordAsync call. I think I understand where that should go, but it seems to cause other errors in the script when I add it. I also realize that I should use the createRecordAsync call to create a single record in the “Projects” table instead of the createRecordsAsync call, but I’m not sure how to edit the rest of the script to correct the errors that occur when I make these changes. (See screenshot below to see my edited script and where the error are occurring)

//SCRIPT THAT WORKS BUT NOT FOR THE RECORD WHOSE BUTTON WAS CLICKED

let table1 = base.getTable("Potential Photoshoots");
let table2 = base.getTable("Projects");
let result = await table1.selectRecordsAsync({fields:table1.fields});
for (let record of result.records) {
    if (record.getCellValue('Move to Projects')) {
        await table2.createRecordsAsync([
            {
                fields: {
                    'Name': record.getCellValue("Name"),
                    'Category': record.getCellValue("Category"),
                    'Complete': record.getCellValue("Complete"),
                    'Due date': record.getCellValue("Due date"),
                    'Kickoff date': record.getCellValue("Kickoff date"),
                    'Launch Date': record.getCellValue("Launch Date"),
                    'Notes': record.getCellValue("Notes"),
                    'Priority': record.getCellValue("Priority"),
                    'Project Files': record.getCellValue("Project Files"),
                    'Project lead': record.getCellValue("Project lead"),
                    'Project Leaders': record.getCellValue("Project Leaders"),
                    'Project team': record.getCellValue("Project team"),
                    'Status': record.getCellValue("Status"),
                },
            }
        ]);
        await table1.deleteRecordAsync(record.id);        
    }
{break;}}

Screen shot showing errors after input.recordAsync call is added and createRecordsAsync is changed to createRecordAsync:

Any help anyone could provide would be greatly appreciated!

[Justin_Barrett]

The predefined input mechanism behaves differently in automation scripts than it does in the Scripting extension. To clarify what @Hendrik_Yang mentioned, you use the input.config() method to retrieve all of the values set in the action’s input variables, and then you can access individual variables by their names.

Before doing that, though, you need to have a valid input variable name. In your screenshot, you set the name tied to the Airtable record ID to be “Record to move”, which is not valid. It must follow JavaScript variable naming conventions. In this case, I would recommend naming it “recordId”. If you do that, then you’ll use this in your script:

const inputConfig = input.config()
const recordId = inputConfig.recordId

An alternate way to do this that I often prefer is like so:

const { recordId } = input.config()

That uses a technique called destructuring assignment to automatically retrieve properties from an object and assign them to variables of the same name.

Anyway, once that’s done you’ll have the record ID in the recordId variable, but that’s just the ID. To get the record you’ll need to retrieve it from the table using that ID, which you can do like this:

const result = await table1.selectRecordAsync(recordId, {fields: table1.fields})

With that done, the rest of your code should work in its current form, beginning with the if (result) line.

[Jerry · Accepted solution]

Thanks so much for all the help @Justin_Barrett and @Hendrik_Yang !! I thought I was on the right track, but got stuck trying to figure out the execution. I appreciate you not only showing me the solution but taking the time to explain the steps and reasoning behind them. I updated the code in the automation and it worked like a charm. I’ve shared the code below in the hopes that it can help others in the future. Thanks again!!!

//SCRIPT FOR AUTOMATION TO MOVE A RECORD FROM ONE TABLE TO ANOTHER

let table1 = base.getTable("Potential Photoshoots");
let table2 = base.getTable("Projects");
const { recordId } = input.config()
const result = await table1.selectRecordAsync(recordId, {fields: table1.fields});
if (result) {
        await table2.createRecordAsync(
            {'Name': result.getCellValue("Name"),
                    'Category': result.getCellValue("Category"),
                    'Complete': result.getCellValue("Complete"),
                    'Due date': result.getCellValue("Due date"),
                    'Kickoff date': result.getCellValue("Kickoff date"),
                    'Launch Date': result.getCellValue("Launch Date"),
                    'Notes': result.getCellValue("Notes"),
                    'Priority': result.getCellValue("Priority"),
                    'Project Files': result.getCellValue("Project Files"),
                    'Project lead': result.getCellValue("Project lead"),
                    'Project Leaders': result.getCellValue("Project Leaders"),
                    'Project team': result.getCellValue("Project team"),
                    'Status': result.getCellValue("Status"),
                },
            );
        await table1.deleteRecordAsync(result.id);
        console.log("Deleted a record!");        
    }

NOTE: I am unable to mark two posts as solutions on the same thread, but the posts from BOTH @Justin_Barrett and @Alexey_Gusev are both correct depending on the application. Alexey’s solution works as a standalone script within a script extension that can be triggered by a button and Justin’s solution adds a new field to the base and edits that same script to work within an automation.

[Alexey_Gusev]

By way, it may be useful in future, reading again my reply and your answer helped me to locate bugs in my code. I did a similar task - to ‘clone’ a number of records in my base - replace each single record In some view with a two records (for ‘odd’ and ‘even’ years, with a bit different data. It’s 30+ fields, with a different types, so I had to iterate them, excluding non-writable and those I filled by other data.

at first I filtered null values and empty arrays (to prevent warnings like “can’t get ‘name’ property of a null”) in a such way:
…
const nonul=x=> x && x.length
…flds.map(f=>rec.getcellValue(f)).filter(nonul)…

thus, I’ve lost all number values (as they don’t have .lenght property)
then I rewrite some code, leaving only ‘not null’ check (usually i’m using .filter(n=>n). to pass an array cleaned from nulls).
But then I realized that I still lost some number values (=0) and they were important.

so I ended with somethilng like this: (and then realized I could jusy copy-paste these records twice and perform a bulk change of my parameters without any coding, lol )

// a piece of code
const table=base.getTable('Example')
const flds=table.fields.filter(f=>(!['formula','createdBy','multipleLookupValues'].includes(f.type)))
const query=await table.selectRecordsAsync({fields:flds})
const convert=(fieldType,value)=>value===0? 0: value===null? null: value===[]? []: 
fieldType=='singleSelect'? {'name':value.name}: 
fieldType=='multipleSelects'? value.map(v=>({'name':v.name})): 
fieldType=='multipleAttachments'? value.url:  // or value.map(v=>({'url':v.url})), can't check now: 
fieldType=='multipleRecordLinks'? value.map(v=>({'id':v.id})): 
value;  // feels like 'modern' version of switch operator
const newrow=(r)=>({'fields':Object.fromEntries(
    [...flds.map(f=>[f.name,convert(f.type,r.getCellValue(f))])]    )})
const create=query.records.map(newrow)
.....