Agenda Item Request

This automation sends an email out to each board member to request any agenda items they want to discuss at the next meeting.

• TriggerRecord: Board Meeting
• Field: Actions on Submit
• Is Exactly Request Agenda Items

For each Board Meeting Members where Name=TriggerRecord.Name

Create Record Email
Run Script

// 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 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 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"]  });

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

for (let WorkListrecord of WorkListquery.records) {
    let Assignees = WorkListrecord.getCellValue("Assignee");
    
    if (Assignees) {
        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", "Request Agenda Items");


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