Help with automation script

Michael_Andrew · ‎Aug 08, 2020

I am working on an automation. Is there a way to have the script select only the record that triggered the automation. I am trying to create a ‘Project Number’. When a new record is added I want to run some script to give it a project number based on some actions (including grabbing some info from another table). Once I have that data I want to use to update the Project Number field, how do I get the script to select the record that started the automation?

I see now that I can include a variable from the trigger step. what is the syntax to use this variable to update a field in that record?

gwynn_kruger · ‎Aug 10, 2020

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:

Justin_Barrett · ‎Aug 10, 2020

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.

Michael_Andrew · ‎Aug 10, 2020

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!

Justin_Barrett · ‎Aug 10, 2020

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:

Michael_Andrew · ‎Aug 10, 2020

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.

gwynn_kruger · ‎Aug 11, 2020

lol.

I was off in the weeds! :laughing:

Justin_Barrett · ‎Aug 11, 2020

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.

Michael_Andrew · ‎Aug 11, 2020

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!

Justin_Barrett · ‎Aug 24, 2020

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