Corporate Process Improvements
Power Automate & PowerApps
I have worked at Hawaiian Airlines as a Field Services Operations Specialist for 3 years and counting. I come from a software development background, and I was able to utilize this at my current position by creating automated processes. To create these processes, I used Power Automate, PowerApps, MuleSoft, Postman, and REST APIs. If you aren’t familiar with Power Automate, it is a tool from Microsoft that allows you to automate actions and connect to all other Microsoft applications and third parties as well. PowerApps is aimed towards UI development with a JavaScript backend. Throughout my time here, I have improved many processes with these platforms using flows. The major processes I had the most impact on are the Tech Hui Sign-In, Training Tracker Documents, and Heatmaps.
Tech Hui Sign-In Overview
My first project in the corporate world was to facilitate a sign-in process. Hawaiian Airlines has a Tech Hui that’s similar to Apple’s “Genius bar”. We have a dedicated room in our office with the purpose of immediate assistance with our employees’ issues. My manager wanted data that would show foot traffic, how many people visit a day, what issues were they coming in for, and what hours are we busiest. I was given the task of automating the sign-in by automatically creating a ticket into our ServiceNow platform while also making the user experience better. ServiceNow is what our company uses to track incidents, issues, requests, and tasks done on the Internal IT side. We manually created incident tickets for every person we helped in the Tech Hui, but that took up more time than we had and lacked real-time data. This automation gathered accurate data on when people came in for issues since tickets were made immediately after the form was submitted.
Tech Hui Issues
Issue 1) Names were hard to read and nicknames were written instead of the name in AD
Issue 2) No real-time data was collected since we made the tickets at the end of our shift
Issue 3) There were no easily accessible logs of our history since it was all tracked on paper and stored in a file cabinet
Issue 4) Needed to implement a collaboration attribute that would facilitate requesting help from the team
PowerApps UI
In the images below, the most updated UI of the Tech Hui Sign-In Form is shown.
Home Page
This home page was created in case we weren’t able to man the Tech Hui, but also explain to any new users what the purpose was for the sign-in.
Laptop Input
Including the laptop name in our tickets help tremendously when tracking devices in our history searches. We are able to filter out all incidents where the service tag was mentioned and track where the laptop was and what issues it had. This variable will get sent through to the code in the Submit Button to pass along into the Flow.
Issue Input
This is our main page where we gather all the manually inputted data from the user and send it to the Flow. We have a search box that filters out AD names, radio buttons to select issue topics, and lastly an issue description selection. The issue description has pre-built options but users can also write their own response by selecting “other”. Each issue topic has its own set of pre-built descriptions that turns visible when selected.
Tech Hui Flow
Below shows a top-level visual about the steps my Power Automate flow includes.
Briefly, the flow is triggered when a user hits the submit button on my PowerApps form. Then all the variables are parsed and sent through an HTTP request using a REST API, which creates our ticket in ServiceNow.
Once the ticket is created, the flow automatically created a post in Microsoft Teams Channel including all of the data that was just submitted.
Code Snippets
In PowerApps, we customize the backend like any other app. I implemented visibility variables that would display certain options for their appropritate issue topic. I connected the user search with our Active Directory so that it would show the most updated list of employees (eliminates terminated employees and includes new hires). The submit button has the most code behind it because of the error checks and success actions. Below is the code for the submit button.
For the HTTP Request, our company created a REST API through MuleSoft that allows ticket creation. I first populated temporary client/secret keys to access the test environment. I ran my tests through PostMan and grabbed neccessary IDs needed for my flow. Once my flow was successful with every test case, I requested PROD client/secret keys to the production environment. The HTTP request has required and optional fields for creating a ticket shown below.
{
"assignmentGroup": "IT Field Services",
"createdDate": "2022-07-14T15:14:00Z",
"category": "User Device",
"contactType": "tech-hui",
"description": "",
"impact": 3,
"incidentNumber": "",
"location": "KPK",
"requestedBy": "",
"requestedFor": "",
"state": 1,
"subCategory": "Fault",
"title": "",
"urgency": 3,
"vendor": "",
"vendorTicket": "",
"workNotes": "enter additional work notes here for ticket",
"additionalComments": "",
"configurationItem": {
"className": "cmdb_ci",
"name": "Unknown CI"
}
}
This project took many prototypes and failures. I ran many tests and purposely tried to make it fail so that anything would be avoided. Now, the application is self-run and never gives any issues (knock on wood). I feel like this project went beyond what was expected and ended up being a huge improvement for our team. Being able to use your creation is the best feeling in the world, and knowing it also helps others is even better. As time went on, other teams started to notice it and wanted to create a replica, but tailored to their needs.
Training Tracker Automation
Training Tracker is our version of learning and completing our documentated processes. We have to read through the document and pass the quiz at the end to be “certified” for the process. After completing the document and quiz, you request approval from the Subject Material Expert on the criteria. Originally, we would make a post in a channel and the SME would write approval. Once approved, our Seniors would manually update an excel spreadsheet with an x to mark as completed. My task was to eliminate manual entries and create a faster/easier approval process.
Approval Process
The teams channel wasn’t the best process since we had to follow a format and make sure the write in the correct document name. To eliminate manual entry for this, I create a simple Microsoft Form that collected 2 things: the SME, and the documents completed. When a form is submitted, a flow is triggered automatically and creates an approval card. I integrated Microsoft Approvals application with the flow because it lets you create your own UI on the card and waits for the approval to go through. The SME will receive an approval card in Teams, then sends the approval back to the flow once completed.
Here is just an example of what the list of approvals will look like for logging purposes.
Excel Sheet Entries
After the approval goes through, it will trigger an office script in excel. I created an office script that will search in a grid for the document name matches with the person who completed the training. In that cell, it will enter the date he/she got the approval.
See Office Script Here
This project was stimulating because I have never worked with office scripts before. Office scripts use JavaScript so I was grateful for the familiarity. I got to learn new functions and was able to do more than I had expected with the Excel sheet. It was also fun to learn how to use another Microsoft app like approvals.
Heat Maps Automation
In conjunction to the Tech Hui Sign-In Sheet, I was to also create heat maps that would represent the real-time data. My boss loved colors in visuals to help grasp the concepts better. You can create heat maps in Miscrosoft Excel using a table and then apply conditional formatting for color scales. The original process was to export all the incidents from ServiceNow into a CSV file. I would have to manually separate the information and get the counts of each hour in each day for the whole month. This took up a lot of time and redundant work, which is where the automation comes in. There are some manual steps due to needed the export file from SNOW, and setting a custom format on a column that isn’t available through office scripts.
Using what I learned from the Training Tracker project, I was able to implement the same concept to my heat maps. It was a lot of leg work at first because I had to format everything and sum up 24 hours worth of incidents for each day of the month. The flow takes a bit of time to run through, but it eliminated the tedious work that saves me hours.