Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
automation:agenda_item_request [2025/01/22 02:35] – Jeff Johnsonautomation:agenda_item_request [2025/01/22 03:02] (current) – Jeff Johnson
Line 2: Line 2:
 This automation sends an email out to each board member to request any agenda items they want to discuss at the next meeting. This automation sends an email out to each board member to request any agenda items they want to discuss at the next meeting.
 ===== Trigger ===== ===== Trigger =====
-  * Table: "Board Meeting" +  * TriggerRecord:  <color green>**Board Meeting**</color> 
-  * Field: "Actions on Submit" +  * Field: <color darkred>**Actions on Submit**</color> 
-  * Is Exactly "Request Agenda Items"+  * Is Exactly <color darkmagenta>**Request Agenda Items**</color>
 =====Flow===== =====Flow=====
->For each BoardMeetingMembers where "Name" = BoardMeeting "Name" (from trigger): +>For each <color green>**Board Meeting Members**</color> where <color darkred>**Name**</color>=TriggerRecord.<color darkred>**Name**</color>  
->> Create Record "Email"+>> Create Record <color green>**Email**</color>
 >>Run Script >>Run Script
 +>>><code>// 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}'`);
 +}
 +
 +</code>