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!

[Zack_S]

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.

[Jerry]

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.

[Justin_Barrett]

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.

[Alexey_Gusev]

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.

[Jerry]

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.

[Jerry]

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);        
    }

[Justin_Barrett]

Jerry:

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?

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.

[Jerry]

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.

[Hendrik_Yang]

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

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