Automatically link records to a summary table based on date

This script will automatically pull the year from a date and enter this in the linked record field. It is a common scenario where you have a reports/dashboard table which summarises key data from another table.

To see a video demo of this script click here.

Script Code

							// set the table
let dashboardTbl = base.getTable("Milestones");
// 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("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) {
        //message output - fields that will be updated
        output.text(`Copying date for record ${record.name} ${dashboardyear}`);

        //Linked table
        let otherTable = base.getTable("Reports");

        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("Year") === 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}]
                });

            }
        }
    }
}