Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
automation:agenda_item_request [2025/01/22 03:01] – Jeff Johnsonautomation:agenda_item_request [2025/01/22 03:02] (current) – Jeff Johnson
Line 9: Line 9:
 >> Create Record <color green>**Email**</color> >> 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>