High-Level Overview

The script generates and updates an email body template with dynamic content based on specific Airtable data. It fetches relevant records, processes information (e.g., agenda items, tasks, and member details), replaces placeholders in an email template with real data, and updates an Airtable record with the final email body and status.

• Retrieves input parameters via `input.config()` including IDs (`Email_Rec_ID`, `Member_Rec_ID`), names, and dates.
• Defines constants such as templates for the email body (`Body_Template_Long`), deadlines, HTML styling, and placeholders for dynamic content.

• Uses `BoardMembersTable` (likely linked to the “Worklist” table) to fetch information about a specific member (`Member_Rec_ID`) including email, nickname, and full name.
• Logs details for debugging purposes.

• Queries the “Officers” view of the “Members” table to find the record of the secretary (identified by their role in the “Office” field).
• Extracts the secretary's email and nickname for inclusion in the email template.

• Replaces placeholders in the email body (`Body_Template`) with dynamically fetched data:
  1. `[NICKNAME]`, `[MEETING TITLE]`, `[MEETING DATE]`, `[AGENDA FORM]`, `[AGENDA DEADLINE]`, `[SECRETARY EMAIL]`, `[SECRETARY NAME]`, etc.

• Queries the “Board Meeting Agenda Items” table to find agenda items associated with the specified meeting (`Meeting_Title`).
• Constructs an HTML list of agenda items with links and presenters, replacing the `[AGENDA_LIST]` placeholder in the email body.

• Queries the “Board Meeting Tasks” view of the “Worklist” table to find tasks assigned to the current member (`Member_Full_Name`).
• Constructs an HTML list of tasks, marking overdue tasks with red text (`Overdue_HTML`), and replaces the `[WORK_LIST]` placeholder in the email body.

• Updates the record in the “emails” table (`Email_Rec_ID`) with:
  1. The completed email body (`Email_Body_Field`).
  2. The status “Send” (`Email_Status_field`).

• Provides a helper function `unselectOption` to remove specific options from a multi-select field in a record. This function is defined but commented out in the script.

• Logs various details such as fetched data, email body, and debugging messages for development and troubleshooting.

• `Email_Rec_ID`, `Member_Rec_ID`, `Agenda_Form`, `Meeting_Title`, `Meeting_Date`, etc., are dynamically provided through `input.config()`.

• `[NICKNAME]`, `[MEETING TITLE]`, `[MEETING DATE]`, `[AGENDA FORM]`, `[SECRETARY NAME]`, `[AGENDA_LIST]`, `[WORK_LIST]`.

• `emails` (primary table being updated).
• `Board Meeting Members` or `Worklist` (member data).
• `Members` (officers and roles).
• `Board Meeting Agenda Items` (agenda details).
• `Worklist` (tasks assigned to members).

The script automates the creation of meeting-related emails by:

• Personalizing the email content based on member data and meeting context.
• Dynamically generating agenda and task lists.
• Updating the email record in Airtable for distribution.

• Add error handling for missing data (e.g., null fields, empty records).
• Ensure fallback values for placeholders if data is not found.

• Consolidate repetitive code (e.g., multiple `replace()` calls for the email template).
• Use helper functions for common tasks like placeholder replacement.

• Ensure it works seamlessly with multiple meetings or larger datasets.

// Specify the table and view where you want to apply the autonumbering


let Email_table = base.getTable("emails");
let inputConfig = input.config();

let Email_Rec_ID = inputConfig['Email_Rec_ID'];
let Member_Rec_ID = inputConfig['Member_Rec_ID'];
let Board_Meeting_Rec_ID = inputConfig['inp_Board_Meeting_Rec_ID'];
let Agenda_Form = inputConfig['Agenda_Form'] + "&submitter=" + Member_Rec_ID;
let Member_Name = inputConfig['inp_Member_Name'];
let Member_Full_Name = inputConfig['inpMember_Full_Name'];
let Body_Template_Long = inputConfig['Email Body'];
let Body_Template = Body_Template_Long.join(' ');
//let Body_Template = inputConfig['Email Body'];
let Meeting_Date = inputConfig['inpMeeting_Date'];
let Meeting_Title = inputConfig['inpMeeting_Title'];
let Agenda_Deadline = inputConfig['Deadline'];
let Email_Body_Field = "Body";
let Email_Status_field = "Status";
let displayText = "Agenda Submittal Form";
let currentDate = new Date();
let Overdue_HTML = `<span style="color: rgb(255, 0, 0);"><strong>(Overdue)</strong></span>`;
let email=null;

//let BoardMembersTable = base.getTable("Board Meeting Members");
let BoardMembersTable = base.getTable("Worklist");
let memberRecord = await BoardMembersTable.selectRecordAsync(Member_Rec_ID);
if (memberRecord) {
    email = memberRecord.getCellValue("Email");
    Member_Name = memberRecord.getCellValue("Nickname");
    Member_Full_Name = memberRecord.getCellValue("Full Name");
    console.log(`Email: ${email}`);
    console.log(`Nickname0: ${Member_Name}`);
    console.log(`Full Name: ${Member_Full_Name}`);
}
else {
  console.log("memberRecord is null");
}
let MembersTable = base.getTable("Members");
let officersView = MembersTable.getView("Officers");

// Query the records from the "Officers" view
let query = await officersView.selectRecordsAsync({
    fields: ["Office", "Email", "Nickname"]  });

// Initialize a variable to hold the secretary's email
let secretaryEmail = null;
let secretaryName = null;

for (let record of query.records) {
    let roles = record.getCellValue("Office");

    if (roles && roles.includes("Secretary")) {
        // Get the value of the "Email" field
        secretaryEmail = record.getCellValue("Email");
        secretaryName = record.getCellValue("Nickname");
        console.log(`secretaryEmail: ${secretaryEmail}`);
        console.log(`secretaryName: ${secretaryName}`);
        break; // Stop searching once we've found the record
    }
}
console.log(`Meeting_Date: ${Meeting_Date}`);

Body_Template = Body_Template.replace("[NICKNAME]",Member_Name);
Body_Template = Body_Template.replace("[MEETING TITLE]",Meeting_Title);
Body_Template = Body_Template.replace("[MEETING DATE]",Meeting_Date);
Body_Template = Body_Template.replace("[AGENDA FORM]",Agenda_Form);
Body_Template = Body_Template.replace("[AGENDA DEADLINE]",Agenda_Deadline);
Body_Template = Body_Template.replace(/\[SECRETARY EMAIL\]/g, secretaryEmail);
Body_Template = Body_Template.replace("[SECRETARY NAME]",secretaryName);

// Get a reference to the "Officers" view
let ItemsTable = base.getTable("Board Meeting Agenda Items");
let ItemsView = ItemsTable.getView("Current Items");

let Itemquery = await ItemsView.selectRecordsAsync({
    fields: [ "Meeting", "Name", "Title", "Presenter","Edit Form"]  });

// Initialize a variable to hold the secretary's email
let ItemName = null;
let ItemTitle = null;
let ItemPresenter = null;
let ItemEditForm = null;
let ItemCount=0;
let Agenda_Label = null
let Agenda_List = "<ul>\n";

for (let Itemrecord of Itemquery.records) {
    let Meeting = Itemrecord.getCellValue("Meeting")[0].name;
    console.log(Meeting + " : " + Meeting_Title)
        ItemCount++;

    if (Meeting && Meeting==Meeting_Title) {
        console.log(Agenda_Label);

        ItemTitle = Itemrecord.getCellValue("Title");
        ItemPresenter = Itemrecord.getCellValue("Presenter")[0].name;
        ItemEditForm = Itemrecord.getCellValue("Edit Form");
      Agenda_List = Agenda_List +  `<li><a href="${ItemEditForm}">${ItemTitle}</a> presented by ${ItemPresenter}</li>`;
    }

}
Agenda_List = Agenda_List + "</ul>"
Body_Template = Body_Template.replace("[AGENDA_LIST]",Agenda_List);        
 
// Get Task list
let WorkListTable = base.getTable("Worklist");
let BoardMeetingTasksView = WorkListTable.getView("Board Meeting Tasks");

let WorkListquery = await BoardMeetingTasksView.selectRecordsAsync({    fields: [ "Name","Assignee", "Task Title", "Email","Due Date","Worklist Form","Task Type"]  });

let Work_List = "<ul>\n";0

for (let WorkListrecord of WorkListquery.records) {
    let Assignees = WorkListrecord.getCellValue("Assignee");
    let Task_Type = WorkListrecord.getCellValue("Task Type")[0].name;

    if (Assignees && Task_Type == "Board Meeting Commitment") {
        for (let assignee of Assignees) {
            console.log(assignee.name + "," + Member_Full_Name);
            
            if (assignee.name == Member_Full_Name) {
                let TaskTitle = WorkListrecord.getCellValue("Task Title");
                let WorkListForm = WorkListrecord.getCellValue("Worklist Form");
                let Due_Date = WorkListrecord.getCellValue("Due Date");
                let dueDateObj = new Date(Due_Date);
                let Overdue_CD = "";
                if (dueDateObj < currentDate) {
                    Overdue_CD = Overdue_HTML;
                } 
                Work_List = Work_List + `<li><a href="${WorkListForm}">${TaskTitle}</a> due by ${Due_Date}${Overdue_CD}</li>`;
            }
        }
    }
}


Work_List  += "</ul>";
Body_Template = Body_Template.replace("[WORK_LIST]",Work_List);        


let newstatus="Send";

console.log(Body_Template);

await Email_table.updateRecordAsync(Email_Rec_ID, {
    [Email_Body_Field]: Body_Template,
    [Email_Status_field]: { name: newstatus }
});

//await unselectOption(Board_Meeting_Rec_ID, "Board Meeting", "Actions on Submit", "Action Item Reminders");


async function unselectOption(recordId, tableName, fieldName, optionLabel) {
    // Get the table and record
    let table = base.getTable(tableName);

    // Get the record's multi-select field value
    let record = await table.selectRecordAsync(recordId);
    let multiSelectField = record.getCellValue(fieldName);

    // Filter out the option you want to unselect
    let updatedOptions = multiSelectField.filter(option => option.name !== optionLabel);

    // Update the multi-select field with the filtered options
    await table.updateRecordAsync(recordId, {
        [fieldName]: updatedOptions
    });

    console.log(`Unselected option '${optionLabel}' from the field '${fieldName}'`);
}