Table of Contents

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.

Trigger

Flow

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