Process Deep Dive #3 - Selenity Establishment Control Forms
This process deep dive is a high-level process overview and is intended for experienced automation developers. Object design is not included.
East Suffolk and North Essex NHS Foundation Trust (ESNEFT) use a web-based application called Selenity to manage end-to-end workflow for Establishment Control Forms (ECFs). An automation has been developed to access Selenity, identify which ECFs are awaiting divisional approval and email reminders to pre-defined groups of designated managers.
The third sub-process (003 – HR - Format Output and Send Email) loads the target ECF information, writes the content for each individual email, assembles the group distribution lists and sends the message.
Here is a video demo for this sub-process. Some data has been blurred out for confidentiality. You can see how quick the emails are generated and sent. A sample email is expanded to show the formatting and email content.
1. Pre-Requisites
2. Main Page
The main page steps through a sequence of five subpages to run the process.
Load ECF Chasers – this page loads the list of Establish Control Forms for which chaser emails need to be sent.
Load Contacts – this page loads a lookup table of divisional groups and individual staff members email addresses for use when emailing the reminders.
Prep Data Output – this page sorts and reformats the source data in preparation for inclusion in the body text of the outgoing emails.
Load Queue – this page loads the reformatted data per ECF into a queue to be used for writing the emails.
Send Emails – Using Queue – this builds the email content by accessing the ECF queue, creates the email distribution groups then sends the email.
3. Load ECF Chasers
This page verifies that the source data file downloaded from Selenity is available in the target location. The CSV file is then loaded into a collection called CSV Values.
The [Selenity Chaser File] is checked to see if it exists.
This is the CSV file that contains the collection of CSVs that need to be chased via email.
The content of the [Selenity Chaser File] is loaded into a collection.
The [CSV Values] data collection has the same format as in previous blogs.
4. Load Contacts
So the process can be maintained by the HR team, a contacts CSV file is used to supply the process a list of group names and email addresses.
The format for this file is (Group Name] [email address]
The entries in the contact list can be in any order providing the group names match those in the selenity data extract.
The contacts list is checked to ensure it is available to use. The data item [Selenity Contacts File] defines this.
The contacts list is loaded into a collection called [Contacts list]
5. Prep Data Output
This page builds the data structure to form the body of the emails.
This page uses two collections with the same data schema shown in Fig 5.1.
[Holding Output] – is a single row collection
[Final Output] – is a multi-row collection
The [CSV Values] collection holding all the ECFs that need to be chased is looped and the necessary data set collated. Some reformatting of the data items is necessary as shown below.
ECF Number – Stored in [Holding Output.ECF Number]
Right([CSV Values.ECF Number], 5)
Division – Stored in [Holding Output.Approval Group]
[CSV Values.Approval Group]
Job Title – Stored in [Holding Output.Job TItle]
[CSV Values.Job Title]
Contract Type and Band – Stored in [Holding Output.Contract Type & Grade]
[CSV Values.Contract Type] & " - " & [CSV Values.Grade] The [CSV Values] data collection has the format shown in Fig 3.4. below
Department – Stored in [Holding Output.Department Output.Contract Type & Grade]
[CSV Values.Department Name]
Department – Stored in [Holding Output.Department Output.Contract Type & Grade]
[CSV Values.Department Name]
Finance Approved – Stored in [Holding Output.Finance Approved Date]
Left([CSV Values.Date Finance Approved], 10)
[Date Trim]
Days Outstanding – Stored in [Holding Output.Days Outstanding]
DateDiff(9, [Date Trim], [Today])
At the end of each record the reformatted output is appended to [Final Output]
6. Load Queue
This page loads the reformatted item into a queue for processing. A queue is used as an audit trail and allows multiple bots to work on the output in the future if required. The output is loaded into a queue called [ECF – To Chase].
7. Create Email Distribution List
This sub page is used to create an email distribution list for the input of [Approval Group] and returns the email list to the calling process.
8. Send Emails
This page creates the header, body and footer for each group email and sends the output to the group distribution list.
This process is too complicated to describe in words and so the best way to understand the logic is to step through the process. If any of the email message content needs to be changed then these are the action blocks to amend.
<Email Intro> - HTML code to create the introduction to the email
<b>Please note, the ECF forms below were created after the 10th December when the ECF process and form changed. Your division may still have ECF forms to approve that were created prior to the 10th December - these are not listed below. Please log into Selenity and check both ECF areas.</b><br><br>"
<Email Headers> - HTML code to create the table headers.
[Email Content] & "
<table>
<tbody>
<tr>
<td><b>ECF</b> </td>
<td><b>Division</b> </td>
<td><b>Job Title</b> </td>
<td><b>Contract/Band</b> </td>
<td><b>Department</b> </td>
<td><b>Date approved by Finance</b></td>
<td><b> Days within Division</b></td>
</tr>"
<Email Headers> - HTML code to add each data row to the table
[Email Content] &
"<tr>
<td>"& [Data.ECF Number] & " </td>
<td>"& [Data.Approval Group] & " </td>
<td>"& [Data.Job Title] & " </td>
<td>"& [Data.Contract Type & Grade] & " </td>
<td>"& [Data.Department] & " </td>
<td> "& " " & [Data.Finance Approved Date] & " </td>
<td>"& " " & [Data.Days Outstanding] & " days" & " </td>"
<Email Footer> - HTML code to add a footer
[Email Content] &
"</table><br>If you have a query with regard to this email, ECF's or the Selenity system, please contact the Payroll and Rostering team.<br><br>
Phone: 01473 123456 or internal 9988<br>
Email: email@nhs.net<br>
Intranet: <a href=""""url"""">https://intranet.esneft.nhs.uk/pages/PandR/establishment_changes</a><br>”
9. Performance and results
This process is now in production and is scheduled to run once daily. It takes less than 3 minutes to run the whole process and will save the HR team an hour a day in admin time. More importantly it will encourage managers to approve their ECFs in a timely manner and hopefully speed up recruitment time.
This automation is available via the NHS Marketplace.