In order to make progress, our approach is to identify one set of data elements and automate collecting it while simultaneously researching the next set of data elements. The end goal is to have a collection of sub-processes, each gathering a different set of data, that can be consolidated into an automation that captures all of the data – eventually with a visualization dashboard.
The RPA consists of 3 sub-processes:
- Navigate through ConnectWise and collect relevant data
UiPath has a multitude of activities to facilitate interacting with a user interface, namely the Click and Type activities enable navigating to specific Project pages in ConnectWise. From these pages, we can use the Get Text activity to gather relevant information like the Project Manager, Budgeted Hours, Start Date, and more. When using Get Text activities on numbers, String manipulation is useful to convert text to an Integer or Double value.
UiPath Studio provides a Table Extraction activity, which extracts structured data into a DataTable automatically. This is an effective way to gather large amounts of data, which is used throughout our process to extract Time, Expense, and Product sheets.
- Prepare data for visualization in an Excel Sheet
Extracting data from ConnectWise is a relatively simple task, the greater challenge is to paste the data into a format that enables eloquent visualization. We chose Amazon Quicksight to visualize our data, an AWS software to create custom dashboards and provide insightful data analytics.
Our goal with this sub-process is to translate the various sectors of data (Time table, Product table, Expense table, etc.) into a seamless structure that can be uploaded into a dashboard.
To determine which format works best we’re taking a trial-and-error approach.
Our first attempt was using a separate sheet for each group of data elements: Project details sheet, Time sheet, Product sheet, and Expense sheet. However, when trying to create dashboards using this structure we realized that Quicksight only accepts CSV files. From this, we learned that in order to visualize all data in one dashboard, all of the data must be in one excel sheet, which can then be converted to a CSV file.
After experimenting with Quicksight, we found a structure that works well for visualization. Essentially, we add all of the data into one large sheet with over 50 data elements. UiPath Studio’s variety of available Excel activities are extremely useful in structuring the sheet correctly.
- Upload Excel Sheet into Dashboard
The last step of the robots process is to upload this master data sheet into the Quicksight dashboard. This was a fairly simple process using Studio’s UI Automation activities like Click and Type.
Including Travel Expenses
As mentioned in Part 1: Designing a Custom As-Is Process to be Automated with RPA, one of the challenges to collecting expense data in real time is tracking travel expenses. These expenses generally don’t appear in the system until up to 90 days after the expense occurs.
By investigating the travel booking process, we noticed that employees have the option to forward a summary of their travel. If employees forward their travel booking summary to an email address connected to our robot, we can gather the expense numbers and include them in our dashboard.
We started by creating a new email address to connect to the robot. After much research, we decided to use Gmail instead of Outlook, which has extra security features which require manual steps in the process. Instead, Gmail allows us to maintain an entirely automatic process.
UiPath’s email automation functionality allowed us to create a separate process that is triggered when the address receives a new email – when new travel is booked.
The travel itinerary emails from Concur, our expense management portal, include a text file attachment detailing the expenses of the booking. Using UiPath’s Get IMAP Mail Messages and Matches activities we used regular expressions to extract the expense information from the text file. This data can then be incorporated into the expense tracking workbook. Using a separate process that is triggered by receiving an email whenever travel is booked effectively solves our problem, enabling us to gather travel expenses in real time.
Useful Resources
Much like any realm of programming, encountering complications and errors in building an Automation is inevitable. For me, it’s been important to use obstacles in automating as an opportunity to familiarize myself with the software. Luckily, UiPath has excellent resources that have helped me learn more about automating by helping me solve issues as I experience them. The UiPath Community Forum and UiPath Documentation Portal have been immensely useful resources that I recommend to anyone working on an Automation.