Create a Weekly Email Showing Which Applications ACE Processed

Modified on Fri, 31 Mar, 2023 at 5:18 PM

TABLE OF CONTENTS

Overview

If the ACE engine is being used to automatically process Pending applications, it's possible that new applications will appear in the Accepted Applications queue without any manual intervention from administrators. This could raise questions, so it may be useful to have a weekly email sent to the applications team with a summary of which apps have been processed.


Steps

There are three steps required to achieve this:

  1. Create a Data Mining Report (DMR)
  2. Create an Email Template
  3. Create a PowerShell script to send the email weekly

1. Create a Data Mining Report (DMR)

  • Navigate to the Applications Module
  • Click the Datamining Report button in the Ribbon
  • Click Show History Fields in the Ribbon
  • Check both boxes by Rationalisation Status
  • In the Data Source area of the Ribbon
    • Ensure All Applications is selected
    • Click Rationalisation Status -> Accepted, Rejected, Rationalised
  • Click Run Datamining Report
  • In the Build Report area of the Ribbon click Column Layout, Filter, Sort:
    • Rationalisation Status Previous Value, Remove: Checked
    • Rationalisation Status Previous Date, Filter Expression: > '[Today - 7]'
    • Rationalisation Status Previous Date, Data Format: dd/MM/yyyy
    • Rationalisation Status Modified By (2): like '*apiaccount*'

  • Click Apply Changes, the applications processed in the last week by ACE should be displayed in the DMR
  • Save the DMR, give it a meaningful name, and take note of its ID

 

2. Create an Email Template

The next step is to create the email template. Note that this should be created in the ManagementStudio global module rather than in the Applications module.

  • Click Administration -> PS Scripts, Emails, Buttons
  • Uncheck Scripts and Buttons
  • Click on the current Project name (1) under Email Templates then Click here to add new item (2)

  • Give the email template a meaningful name and a subject
  • Using the column picker add the CC field to the grid
  • Add an email address (or a comma separated list of email addresses) to the CC field. Note that this could also be a distribution group email
  • Click the the Edit button (the scroll icon)
  • Add text to appear at the start of the email such as: Here is the report showing the applications processed by the ACE engine in the last seven days.
  • To embed the report content as a table in the email:
    • Add the line [A-DMR-XX-ScopeDMR-AsTable] where XX is the Data Mining Report ID
  • To add the report as a file attachment in the email:
    • Add the line [A-DMR-XX-ScopeDMR-AsAttachment] where XX is the Data Mining Report ID 

  • Click Save Changes
  • Take a note of the email template ID



Optional step: If the report was embedded in the email, it may be useful to add borders to the table. To do this add the text below to the start of the email template:


<style>
table.table {  
     border:solid 1px #c0c0c0;
     border-collapse: collapse;
}
     
tbody th, tbody td, thead th {
     padding: 3px;
     border-width: 1px;
     border-style: solid;
     border-color: #c0c0c0;
     vertical-align: top;
     font-size: 12px;
}
 
thead th {
    text-align: center
}
</style>


3. Create a PowerShell script to send the email weekly

  • Click Administration -> PS Scripts, Emails, Buttons
  • Uncheck Emails and Buttons
  • Click on the current Project name under Email Templates then Click here to add new item
  • Give the script a meaningful name
  • Click the the Edit button (the scroll icon)
  • Add the following command where YY is the Email Template ID
    • Send-MSEmails -Module ManagementStudio -ToIds 1 -EmailTemplateId YY -SpreadOverHours 0 -OverrideSendTo ''
  • Use the column picker to show the Grant Access 2 column
  • Add the Project Admins role to Grant Access 2
  • Click Save Changes
  • Schedule the PowerShell Script to run weekly e.g. every Monday at 12pm

4. Test the Script

  • Click the play button to test the script
  • An email will be sent with the DMR




Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket