This automation sends an email out to each board member to request any agenda items they want to discuss at the next meeting.
For each Board Meeting Members where Name=TriggerRecord.NameCreate 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}'`); }