Help with automation script

Use a Button field and configure it to call your script.

This code will not prompt the user if the Button field is correctly configured:

let projectTable = base.getTable("Project List");
let projectRecord = await input.recordAsync('Pick a record', projectTable);
if (projectRecord) {
    /// do something here
}

Here’s where to find the documentation:

When an automation is triggered by a record, the record’s ID and field details are passed to the automation automatically. In the script action step, you can add one or more variables to pass to the script based on almost anything collected from the triggering record.

Inside the script, you can pass data back out to further action steps using output.set() (details for that can be found in the script action’s API docs). In an “Update a record” action, you can choose the triggering record, and update the {Project Number} field using the output data set by the script.

Justin,
Thanks. Do you think you can provide an example. As noted, I know nothing about coding and am learning through breaking down examples (plus trial and error of course)

Thanks!

I just replied in another thread you’d started with info on how to get the record ID into your script. Getting the new project number back out is similar. Let’s say you’ve done some calculations and assigned the project number to a projecNumber variable. At the bottom of your script, add this line:

output.set("ProjectNumber", projectNumber)

After the script action step, add an “Update a record” action, and set it up kinda-sorta like this:

OK. That works for part of it. I’m still having difficulty to create a script for find/filter of the ‘Year’ table. How can I search for a record in the "Year’ table with ‘Name’ matching ‘Year String’ value from "Project List’ table?

To be clear, I want to get the cell value of ‘Year String’ from ‘Project List’ table, search ‘Year’ table ‘Name’ field for ‘Year String’ value. When found give the record id (to be used to update field back in Project List).

Sorry for my lack of knowledge and understanding. Trying to learn.

lol.

I was off in the weeds! :laughing:

Just to make sure that I understand, do you want to grab the {Year String} value from the record that triggered the automation? That’s what it sounds like, but I want to be sure before building a sample script.

Justin,
I was actually able to find what I needed here:https://airtable.com/shrTq5CSFFqqJdI5q/tblPCdamB8pKpGaAz/viwTTbzCKBngicei5

My final script that appears to work (limited testing):

// set the table
let dashboardTbl = base.getTable(“Project List”);
// get the table records
let dashboarddate = await dashboardTbl.selectRecordsAsync();
// loop through the records
for (let record of dashboarddate.records) {
// get date from field value “Date”
let dashboarddate = record.getCellValue(“Created Date”);
// turn it into a date object
dashboarddate = new Date(dashboarddate);
//get year from date
let dashboardyear = dashboarddate.toLocaleString(‘en-GB’, {year: ‘numeric’});
// only run on records where linked “Year” field is empty
if (record.getCellValue(‘Year’) === null) {

    //Linked table
    let otherTable = base.getTable("Year");
    let otherTableQuery = await otherTable.selectRecordsAsync();
    //Loop through linked table
    for (let otherRecord of otherTableQuery.records) {
        //match linked cell value to string specified
        if (otherRecord.getCellValue("Name") === dashboardyear ) {
            //get ID of linked field
            let idYouNeed = otherRecord.id;

           //Update original table with id value of linked field (must be an array)
            dashboardTbl.updateRecordAsync(record, {
                Year: [{id: idYouNeed}]
            });
        }
    }
}

}

If you have any recommendation for improvement or simplification, let me know.

Thanks!

The only optimization I would suggest is bulk-updating all modified records. Bulk updates can happen 50 records at a time, which takes much less time than updating one-by-one. Somewhere near the top of your script, before the for loop, insert this:

let updates = [];

Next, replace this:

            dashboardTbl.updateRecordAsync(record, {
                Year: [{id: idYouNeed}]
            });

…with this:

            updates.push({
                id: record.id, 
                fields: {"Year": [{id: idYouNeed}]}
            });

Finally, add this to the very end of your code:

while (updates.length > 0) {
    dashboardTbl.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}