Author: Michael Lazich
Cloud Developer, FSi Strategies

Automate and execute, easier & faster

Since the start of the pandemic, we have had to adapt not only within our personal lives but our professional lives as well. With new guidance, recommendations, and restrictions we all had to adjust to a new way of living. One of the most prominent changes I’ve noticed over the last year is the use of personal protective equipment (PPE). This includes masks, gloves, hand sanitizers, and other items that before this pandemic were not necessarily everyday staples of your typical household or business. In the scenario below we will demonstrate a simple, automated, solution for formalizing an ordering process, approving the order, and lastly logging the order to a spreadsheet so it can be tracked by a member of the finance team.

Contoso Restaurant Group (CRG) is a restaurant group based out of Washington D.C., it manages and operates 5 different restaurant concepts within the city. Due to city restrictions the restaurants staff must wear gloves and masks while interacting with guests and food. The restaurant must also have hand sanitizer stations placed through out the dining space. CRG preformed a cost analysis of obtaining the products and found that through their vendor they can receive a significant discount by ordering these items in bulk at the corporate level vs individually at the restaurant level. Operations needs a low cost, low maintenance solution to standardize the restaurant ordering process, have orders sent to the procurement manager for approval, track the requests in a financial worksheet and notify the restaurant managers when their order is ready.

Step 1: In this first step we need to create the form in which the restaurant managers will fill out to order their PPE items. For this we will utilize Microsoft Forms. Once signed into your portal select the apps icon in the top left corner and select Microsoft Forms. If Microsoft Forms is not listed select the option to show all apps and select it from the list displayed. Once you are on the Forms home page select the option to create a new form. With the new form open you will now give your form a title, description and start adding your questions. For this example, we will give our form the title of “PPE Request Form” and a description of “Use this form to order personal protection equipment for your location”. Since each location requires face masks, gloves, and sanitizer, we will base our questions on those products. The first question the procurement manager needs is the restaurant location. For this we will utilize the choice question type and set it to required. They also need to know the restaurant’s preferred delivery date, or in other words when they need the product. For this we will utilize the date question type and set it to required. The face masks come in one size, they are distributing them per box with a max requested quantity of 15 boxes per order. For this we will utilize the choice question type. Similarly, hand sanitizer also comes in one size and is being distributed by the gallon and has a max requested quantity of 5 gallons. For this we will utilize the choice question type. Lastly the gloves are sourced in 3 sizes: medium, large, and extra-large. Each size option is being distributed by the case with a max request quantity of 2 cases per size per order. For these 3 size choices we will utilize the choice question type.

Below is a quick video to demonstrate the creation of the form.

Step 2: For our second step we will create the financial document for the accounting team to track the cost per location. For our purposes we will be using an excel workbook that is saved to OneDrive, this can be easily shared with whomever needs it. Once signed into your portal select the app’s icon in the top left corner and select Microsoft Excel. If Microsoft Excel is not listed select the option to show all apps and select it from the list displayed. Select the option to create a new workbook. Start by giving your workbook a name, for this demo we will be using the name PPE Cost Tracker. The next step is to start building out your table. The first step to building a table is to define your header, this will consist of all the data points you wish to collect or report on. If we look back in step 1, we have the following data points: Location, Quantity of masks, Quantity of sanitizer, Quantity of medium gloves, Quantity of large gloves and Quantity of extra-large gloves. Other data points we will be capturing include the following: Date ordered, Order number, Company code, Account code, Cost of masks, Cost of sanitizer, Cost of medium gloves, Cost of large gloves and Cost of extra-large gloves. We will discuss how we are going to collect this information in step 3. Once you have your header row completed, highlight the items in the row and select the “Format as Table” button under Tables. Select a table style and select the option for “My table has headers” check box in the pop-up window. Now that you have a table select the Table Design tab and give your table an easily identifiable name, for this demo we are going to use “PPE_Table”.

Below is a quick video to demonstrate the creation of the Excel Workbook.

Step 3: In the final step we will be creating the automated process of retrieving the submitted form requests and creating the approvals workflow. For our purposes this is going to act as confirmation that the order has been fulfilled and is pending delivery and posting to the financial document for cost allocation. To accomplish this, we will be utilizing Microsoft Power Automate. Once signed into your portal select the app’s icon in the top left corner and select Microsoft Power Automate. If Microsoft Power Automate is not listed select the option to show all apps and select it from the list displayed. Once you are on the Power Automate home page select the option to create a new flow. You will see a variety of options to start your flow, from the type, from a template and even by connector. For this demonstration we will choose the option to start an automated cloud flow from a blank template. For our flow we will give it the name “PPE Request Form” and choose the “When a new form response is submitted” trigger. With our template up for the first part of the flow, we will need to select the form we want our flow to trigger on. Next, we need to gather the form responses, for this we will use the action “Get response details”. You will select the form used in the trigger and the “Response ID” from the trigger. Our next step to the flow is to initialize some variables to capture the form responses. We will need variables for: Location, Delivery Date, Qty of masks, Qty of sanitizer, Qty of medium -sized gloves, Qty of large -sized gloves, and Qty of extra-large sized gloves. To accomplish this, we will utilize the initialize variable operation, and set the variable type to string. With our form variables initialized let’s take a moment to think about what data is needed for the financial reporting document. We will use the Response ID from the trigger as the order number, which leaves us with Date Ordered, Company Code, Account Code and the Cost for the PPE items ordered. Since the Date Ordered, Company Code and Cost of Items ordered are all the same across the 5 locations we can go ahead and initialize and set the values to those variables now.

The last piece of information we need to capture is the location specific accounting code. To do this we will select the Control Operation and choose the Switch Action. To initiate the switch, we need to provide what we want it to validate, for this demo we will be validating the location name, so we will select the location response from submitted form in the get response details action. Now, for each case set the equals parameter to the name(s) set in the form, for this demo we will utilize Location A – Location E. One thing to note is that you cannot initialize a variable inside a switch case, as a result we will need to first initialize the account code variable outside of the switch and then use the set variable action to apply the location-specific value to the variable. With all our data points initialized, let’s go back and set values to the form variables. With the variables set we can now build out the approval workflow. To achieve this, we will utilize the Approvals operation and since we do not want the restraint location to have the PPE requested charged to their account code until it has been fulfilled, we will select the Start and wait for an approval action. For our scenario only the procurement manager is approving PPE requests however, depending on your own scenario you may have more than one person able to approve a request or even a multi-layered approval process. Depending on your scenario will dictate what approval type you choose. For this scenario we will choose the Approve/ Reject – First to respond approval type. You will need to fill out the title and to whom the approval should be assigned to. We will also include the details from the request in the details section of the approval action. For the details we will include the location and delivery date as well as a table to show the items being requested. It is important to note the Approvals operation supports markdown annotation. You can find more info and examples here.

For the final piece of this automation, we need to capture the approval response and if approved add the appropriate account information into the financial document. To accomplish this, we will be utilizing the Control operation and the Condition action. If the PPE request is rejected, then we want to send an email to the person who submitted the form to let them know their order is unable to be fulfilled. We will utilize the Outlook control and send email action to facilitate this. Since our form collects the submitted users’ email, we will set the “To” field to the dynamic value. Since it is possible for a location to submit multiple orders it’s important for us to let the requester know what order we are referencing. For this we will put the order date in the subject line of the email. For the body of the email, we will simply put that the order could not be completed as 1 or more items are not available. And then we can terminate the automation. If the request is approved, we need to add a row to the financial document with the order information as well as send an email to the location notifying them of the status of their request. To accomplish this, we will be utilizing the excel and outlook operations. Fill out the required information to connect to your excel table. Then begin entering the information into the table. For the cost of the items ordered you will want to enter in the excel formula that takes the qty ordered * the item cost. For example, “= {Qty of masks} * {Cost of Masks}, where {‘Name”} indicated the dynamic content value. Next, we need to let the requester know the order has been approved. Lastly, we can terminate the automation and send a test submission to verify the functionality.

Below is a video to demonstrate the creation of this automated process.

Why Automate?

We help businesses streamline processes through automation. Automating business processes can be done for many reasons. Helping processes stay consistent, removing repetitive tasks, eliminating paper-based file management, and connecting applications across multiple platforms are just a few reasons that people automate business processes. This can reduce cost and overhead by optimizing workflows, improving employee efficiency and decreasing expenditures on costly errors.

FSi Strategies is a recognized Microsoft Gold-Certified Partner , since 2003 we’ve helped our clients successfully leverage the strategic value of technology to achieve their mission.

Contact us today to learn more about optimizing and automating your business processes.