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

Hi Jerry, not exactly sure how you have your tables setup however it seems like you might be better off using your “Projects” table for “potential projects” since much of the data is the same.

You could add a single select field to the projects base and add a selection like “potential” to the records that haven’t been confirmed yet.

Then use create a different view and use groups or filters to show the records you are actually looking for.

Thanks for the response, Zack. Unfortunately, that wouldn’t work for this use case. The Projects table is used by the team to assign and track active projects that have been assigned or approved to move forward. Photoshoots are just one type of “project” and we track several dozen photoshoots at a time. Once we’ve done our homework and determine their viability, then we begin assigning tasks, a project team, and other details, There are also automations that kick once something is added to the “Phootoshoots” category on the project table.

Because the Projects table is already large and has quite a few views configured for each user, we aren’t looking to add another view for them to go through, especially when a majority of the items in it may never come to fruition. We also didn’t go the single select route because doing so would create a field that was only applicable to one category of project (Photoshoots) and there are over a dozen other categories so we’d have to rely on the user to remember only to use it for Photoshoots. I don’t want to depend on that. That’s why I was hoping to use a script. I feel like I’m really close with the one I have, but I just need it to work on the right record.

Welcome to the community, @Jerry! :grinning_face_with_big_eyes: The main issue that I see with your modified script in the screenshot is in line 4. It begins with…

if (record...

…but the problem is that “record” isn’t defined anywhere prior to that line.

The line prior to that is correct in terms of capturing the record where the user clicked in a button field, and the resulting record is set to the variable named “result”. If you use “result” instead of “record” in the line after it—and all other places that currently refer to “record”—that will solve most of your problems.

The only remaining issue that I see is on line 22. You need to add a closing curly brace in front of that closing parenthesis. The closing curly brace will pair with the opening curly brace on line 6, and the closing parenthesis pair with the opening parenthesis on line 5.

Hi,

aside from main problem (trying to use variable name ‘record’ instead of ‘result’), there are other issues.
Difference between createRecordAsync and …recordsAsync - for single operation you don’t need to wrap your data into field object. Just delete lines 7 and 21

You also should not use if (record.getCellValue('Move to Projects')) in your case.
Usually such clause used to check if the cell empty, but it has no sense for buttons. if (something) means check that something is not null, not [] (empty array) and some other ‘falsy’ values.
The place where you define which record to move is the line 3.
When you run script via button, variable result gets record, whose button was pressed.
Also, script engine consider that result may be null, so it will underline all future usages of that variable. You should exclude that case.
The recommended way is to write
if (result) {
…
the rest of your code
}
else
smth like output.text(‘the record is not selected’)
you can omit else part

on the contrary, you can set ‘guard clause’, like ‘if result is null, show error message and end the program’

let result=await inputrecordAsync('select record', table1)
if (!result) throw new Error('Record is not selected');
...the rest of code...

then, you will maybe encounter new problem - some of the field types won’t let you just get cell value from one place and put to other. it works with text fields, but for example, for Single select, it will refuse to get it’s own value. Because read format is different from write format.

if (record...

Thanks, Justin. I really appreciate the explanation. It was clear, concise, and easy to follow. I tried your solution and changing instances of “record” to “result” did solve most of the problems, as did adding the closing curly brace. There were still errors as the record was being created on the Projects table, though. I finally realized that I needed to remove the Fields object. That allowed the script to run but it’s still generating errors so I’ll keep playing with it to try and remove them, but at least the script is working for the right record now.

let result=await inputrecordAsync('select record', table1)
if (!result) throw new Error('Record is not selected');
...the rest of code...

Alexey, thanks so much!!! I wasn’t getting it at first but I read through your thorough explanation again and worked through the issues step-by-step, following your directions carefully. I was then able to get rid of all of the errors (see working code below). I tested the functionality with a few users and the only issue they have with it is that every time a button is clicked and the script runs, the scripting app editor slides open on the right side of the screen causing some confusion and risking the potential that someone may edit the script and break it. Is there a way to get the script to run in the background when a button is clicked, keeping the scripting app closed so the users don’t see it?

//WORKING CODE TO MOVE RECORDS FROM ONE TABLE TO ANOTHER USING A SCRIPT

let table1 = base.getTable("Potential Photoshoots");
let table2 = base.getTable("Projects");
let result = await input.recordAsync('Pick a record', table1);
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);        
    }

//WORKING CODE TO MOVE RECORDS FROM ONE TABLE TO ANOTHER USING A SCRIPT

let table1 = base.getTable("Potential Photoshoots");
let table2 = base.getTable("Projects");
let result = await input.recordAsync('Pick a record', table1);
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);        
    }

Not currently. This is a much-requested feature, but only the devs know for sure if it will ever make it to Airtable.

One workaround is to use something like a checkbox field or a single-select field—basically any field type where you can make a quick change that doesn’t require typing—to trigger an automation that runs a script. It will require some slight changes to your current script, but not many. The other thing is that scripts in automations are only available if your base is in a Pro-plan or higher workspace.

Thanks again, Justin! Sorry to be such a pain with this. I really thought I had this figured out.

I had already been looking into your old post (Manually run a script without opening the Apps sidebar). I see what you’re recommending and how that could work, but am unclear on why the script won’t work the way it’s written for the automation. I’ve tried several iterations, but it just throws errors each time I edit it. Not sure if I’m on the right track, but I’m thinking input.recordAsync needs to change to input.config but I’m not really sure what to do from there. When I do that, it says I can’t use result.getCellValue. Any suggestions?

I am on the Pro plan so that shouldn’t be a problem.

You must input.config(), e.g.

let inputConfig = input.config()
let Status = inputConfig.status
let Document = inputConfig.document

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.

//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!");        
    }

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)
.....