As IT professionals, we are often tasked to automate business processes. Many business processes involve forms, email, Office documents, file storage, and notifications. Building custom solutions used to be a costly exercise that only developers could do.
Today, Office 365 provides IT pros with the tools like SharePoint, Microsoft Forms, PowerApps, Flow, and PowerBI. These tools can be used to automated business processes without any programming skills.
In this article, I will be demonstrating how to automate a user on-boarding process. This solution will use:
- SharePoint Online for the back end
- Microsoft Flow as workflow engine
- PowerApps as a form customization tool
The entire solution will be built without writing a single line of code. This puts the power of business process automation in the hands of IT pros. You can become a hero in your organization by taking what you learn from this article and using it to automate other business processes.
Before You Begin
Before we dive into building an automated solution, let me help you understand the process and the approach you need to take:
- Understand the problem – Before jumping in and starting to develop solutions, take some time to break the problem into smaller pieces. This will help you to not miss important details.
- Identify personas – You have to identify the users involved in this business process. Know what their business knowledge, technical skills, and level of access is. You should also identify what Office 365 apps and services they are licensed for. This will help you to build a process that fits with their capabilities.
- Whiteboard – Drawing a visual representation of the workflows will help you understand the business logic and the steps required to solve the problem. You should also involve your users in this step to engage them in the process.
- Testing – Try to break your process and understand the possible failure modes before you release it to your end users. A test environment that the users can also take part in is helpful at this stage. Their unique perspective can help you to discover bugs that you missed in your own testing.
Ok, let’s get started.
The User Onboarding Scenario
The HR department contacts IT and says they would like to automate the onboarding process for new staff. The goal is to make the process more streamlined and easier to control.
The first and most important step is to interview everyone who is involved in the process and gather as much information as possible. Using the information that you gather, draw a business logic diagram.
Looking at the requirements, we need to ask ourselves:
- How is HR user going to submit a request?
- Where are we going to store requests?
- How are we going to trigger emails?
- How are we going to monitor task completion?
Let’s answer all these questions, one at the time. The architecture is pretty simple:
- HR users will submit requests using a SharePoint list form that is customized with PowerApps
- The requests will be stored in a SharePoint Online list
- Microsoft Flow is going to be our workflow engine for processing requests and triggering email notifications for task completion
All the steps in this article assume you have Office 365 licensing that includes SharePoint Online, Microsoft Flow, and PowerApps. You also need permission to create SharePoint lists, Flows and PowerApps.
Creating the SharePoint Online List
SharePoint Online will host the list that is used to store HR user requests. It will also host the PowerApps form that HR users use to submit onboarding requests when a new staff member is hired.
The SharePoint list can be created in an existing site, or you can create a new site to host it. A good option is to create the SharePoint list in a Team Site that is associated with an Office 365 Group. This gives you access to other Group resources for the HR team to use as well. For example, they can use the Group’s OneNote notebook to host their own user documentation for the onboarding process. The Group mailbox can also be used for receiving the email notifications that are triggered by Flow.
Give the new list a meaningful name and description, and allow it to be shown in the site’s navigation as well.
After the SharePoint list is created, we need to build the required fields to store request metadata. For this example, the form will have two sections:
- New hire details
- Associated tasks fields
Every SharePoint list or library comes with a column named Title. This column is mostly used as a link to the details of the item. Rename the Title column to Request ID and set the default value to “Not Assigned”. To rename the field, open the Settings menu in the top right of your browser window, and select List Settings.
In the list columns, click on the Title column. Now you can change the column name and set the default value.
Next, add more columns to the list for storing the rest of the data about the new hire. Each column will become a field in the form that HR users fill out for new hires. In this example, I’m adding the following columns:
- First name (single line of text)
- Last name (single line of text)
- Job title (single line of text)
- Mobile phone number (single line of text)
- Department (choice, configured with a list of your department names)
- Office location (choice, configured with a list of your office locations)
- Supervisor (person or group)
- Start date (date and time, set to date only)
- Status (choice, configured with a list of request statuses)
When the HR user is filling out the form, those fields will look similar to the screenshot below.
Before we go further, I would like to highlight two of the important columns in that list.
The Supervisor field is a person field. When the HR user starts typing a name in this field, SharePoint queries users from your Azure Active Directory and will display suggested results that match.
The Status field is a choice field with four options; Submitted, In Progress, Completed and Rejected. We’ll leave the default value set to “Submitted”.
Ok, now let’s add some more columns that will become fields on the form. These are fields are added as Yes/No check boxes, and each refers to the tasks that a department has to complete. For this example, the following tasks are potentially required for each new hire:
- IT – Create Azure AD account
- IT – Assign Office 365 licenses
- IT – Assign equipment
- HR – Schedule orientation
- HR – Add to the payroll system
- Accounting – Expense form training
- Accounting – Assign debit card
When the HR user is filling out the form, those fields will appear as toggles similar to the below screenshot.
We are done setting up the back end of our system in SharePoint Online.
Creating the Flow
The next step is to implement the automated workflow for the onboarding process. We are going to use Microsoft Flow to achieve this. Based on our process diagram, submission of the form should trigger an approval request email to the hiring manager. In this example, the hiring manager is specified in the Supervisor field of the SharePoint form.
To create this Flow, in your Office 365 portal launch Flow from the waffle menu.
Microsoft Flow provides predefined templates to use as a starting point to build your custom Flows. For this example, I am going to use the “Start approval when a new item is added” template.
When you create a new Flow you will have the opportunity to change the credentials used to connect to services such as Exchange and SharePoint. For this demonstration I’ll just be using my test user account. In real world scenarios I would recommend creating service accounts to use in Flows. For example, use email@example.com or firstname.lastname@example.org. This ensures that Flow actions are not associated with a specific user’s account and can be managed by others in the future.
The Microsoft Flow template consists of four main parts:
- Which SharePoint list should trigger the event
- The process to start an approval by assigning to a user
- Conditions, e.g. approved or rejected
- Condition outcome, e.g. if/then statements for the approved or rejected states
Enter the site address and list name for the SharePoint Online list that was created earlier.
Next, let’s specify the approval path. There are three required fields:
- Approval type
- Assigned to
In our case, we will set the Approval type to “Anyone from the assigned list”. The Title can be whatever is appropriate for your workflow. Approval request will be sent to a new hire’s supervisor. The Supervisor’s name and email address are pulled from the SharePoint list by adding dynamic content to the field. Since Supervisor field is a “person” field type, there are multiple metadata available for this field. For this scenario, the “Supervisor Email” value is used to populate the Assigned to field.
The approval email message that will be generated can contain a URL that links to the request in the SharePoint list. Again, we can use dynamic content to add the “Link to item” value to the Item link field.
The Item link description field is also filled out using dynamic content.
After setting the approval request email message, we need to set the condition for approval and rejection scenarios.
We are going to use the approval email outcome in the condition setting. If the response is “Approve”, then that is a “yes” outcome. If not, then the outcome is “no”.
Let’s review the If Yes outcome and its actions. We can add as many actions in the scope of this outcome as we need to. Based on our requirements, we are going to:
- Update the Request ID SharePoint field from “Not Assigned” to “Unique ID + Date”
- Create the Azure AD account based on data provided in the form
- Update SharePoint List column IT – Create AD User Account field from “No” to “Yes”
- Send an email to the requester notifying them of the approval outcome
- Send emails to IT, HR and Accounting requesting to complete specified tasks
To add actions to the outcome block of the flow, click on the Add an action button.
Then search for the action that you want to add to the Flow.
Let’s review the details for the Azure AD – Create user action. We are going to use dynamic data pulled from the SharePoint list to complete the user creation task.
Now, let’s review If No outcome and its actions. Based on the requirements we are going to use the Update Item action to set the request status to “Rejected”. Then, we’ll send an email to the requester notifying that the request was rejected.
This is the end of the approval Flow.
Monitoring for Onboarding Task Completion
In the previous section of this article, we covered how to set up the automated creation of the Azure AD user account, and notify various departments so that they can complete their onboarding tasks for the new staff member.
The second part of this Flow is to send an email to the hiring manager when all of those onboarding tasks are complete.
I am going to create another Flow to check whether all tasks are completed, and if “Yes” send an email to the requester. The Flow will also set the Status of the request to “Completed” when all tasks are completed.
This Flow will be triggered by edit events on the SharePoint list. Whenever a SharePoint item is edited, Flow will be checking the state of each of the onboarding tasks.
We are going to start with a blank Flow instead of using a template. After starting a blank Flow, add the trigger named “When an item is created or modified”. Enter the same URL and list name used to create the first Flow in this tutorial.
Next, click on Add Step add a new Condition block to the Flow. The tricky part here is to set the condition based on SharePoint fields value. As we can see in the screenshot below, certain fields have to be equal to “Yes” to get logical “true” outcome of the condition.
To achieve this, we need to switch the condition customization block to Edit in advanced mode.
This is what is going to be set in the condition block:
@AND ( equals(triggerBody()?['IT_x0020_Created_x0020_AD_x0020_'], True), equals(triggerBody()?['IT_x0020__x002d__x0020_Assign_x00'], True), equals(triggerBody()?['IT_x0020__x002d__x0020_Assign_x0'], True), equals(triggerBody()?['Accounting_x0020__x002d__x0020_A'], True), equals(triggerBody()?['Accounting_x0020__x002d__x0020_E'], True), equals(triggerBody()?['HR_x0020__x002d__x0020_Orientati'], True), equals(triggerBody()?['HR_x0020__x002d__x0020_Add_x0020'], True) )
It looks confusing but let me break it down for you to have better understanding what all this means.
@AND conditions mean everything inside of the brackets must return true in order to get overall “true” for the entire block.
Each line inside of the brackets is a field in SharePoint. In our case, we are using a “Yes/No” SharePoint field for each task. The “Yes/No” and outcome translates into a logical language where “Yes” = true and “No”= false.
You will notice that the names of the fields are different from the display name in the form. This is because SharePoint creates an internal name which may be different from display name.
To access the internal name each field, navigate to the SharePoint list, open the Settings menu, and choose List Settings. Click on the field that you want to get the internal name for.
After navigating to the column’s details, look at the end of the URL string in your web browser, where you will find a value such as Field=IT_x0020_Created_x0020_AD_x0020_. That is the field name that must be used in the code block shown earlier.
The rest of the Flow is pretty straightforward. If the code block assesses that all of the task fields are equal to “Yes”, then we update Status field to “Complete” and send an email to the original requester.
All required Flows are built, so now we need to fine tune the SharePoint form.
Customizing the SharePoint Form Using PowerApps
The business rule requires that IT team should not be able to see HR fields and vice versa, but recruiter should see it all. To accomplish this requirement, we are going to use Microsoft PowerApps.
From your SharePoint list, click on PowerApps -> Customize forms.
You will be redirected to PowerApps web app page.
In our business rule, we have four team members who control the onboarding process.
- Recruiter: Daler Sayfiddinov
- IT: Bill Blank
- HR: Marry Smith
- Accounting: John Doe
After you have opened the form in PowerApps, set the visibility property of each field.
In the example shown above, the visibility of the Assign O365 Licenses is being restricted to Bill Blank and Daler Sayfiddinov by setting the following value:
if((User().FullName="Bill Blank") Or (User().FullName="Daler Sayfiddinov"),true)
Repeat this step for all fields. For example, the HR fields would be set to the following value:
if((User().FullName="Mary Smith") Or (User().FullName="Daler Sayfiddinov"),true)
For this scenario we also don’t want our end users to be able to modify the Request ID field since this is going to be our main identifier for the requests. Select the Request ID field and set the visibility to “false”.
We will also need to set the Status field to read-only, so that only Flow can change the status of the request. To do that we need to select the Status field, then select Rules -> New Rule. Create a custom condition for the rule.
Each field in the form is referred to as a “datacard” by PowerApps. In this example, the Status datacard is “DataCard1”. The condition for the rule checks whether the datacard is visible (which will always be “true”).
The action for the custom rule sets the Display mode to “View”.
After completing all the steps, click on File -> Save, and then Publish to SharePoint.
We are all done and ready to test our onboarding solution.
Running the Onboarding Process
To see the onboarding process in action, Daler Sayfiddinov as a recruiter is going to submit a request by completing the form in SharePoint.
After submitting the form, the request item will appear in the view. You will notice that Request ID is still set to “Not Assigned”, because the supervisor did not approve the request yet.
Let’s log in as Carla Marks, who is the new employee’s supervisor. An approval request email has been delivered to Carla’s mailbox.
As you can see in the screenshot above, the supervisor does not have to leave Outlook to approve or reject the request. After successful approval, the Azure AD account has been created and emails have been sent to the requester confirming approval. Also HR, IT and Accounting received emails asking to complete additional tasks.
Now, let me log in as Bill Blank to see what he has received. In Bill’s mailbox, we have an email requesting additional tasks. The message of the email can be designed based on your company needs and requirements. It could just as easily be sent to a help desk email address, or you could enhance the Flow with more automated tasks, for example to assign group memberships based on the department attribute of the new user.
Let’s navigate to SharePoint to review the rest of the tasks and as we designed, Bill only has three tasks to complete.
Similar to Bill, other members of this process will receive an email with associated tasks and, thanks to the PowerApps customization we performed, the SharePoint form will display fields related to this team member only.
Let’s wait for all members to complete their tasks. As soon as the last task is completed, the status of the request automatically switches to “Complete” and a confirmation email will be sent to the original requester (recruiter).
In this tutorial I’ve demonstrated the automation of a moderately complex onboarding process for new staff members. The process uses SharePoint Online, Microsoft Flow, and PowerApps to establish a secure account creation process for the organization. This is an example of how you as an IT professional can leverage the tools available in Office 365 to create custom apps and workflows for your organization, all without writing a single line of code.