BYU Student Author: @Andrew_Wilson
Reviewers: @Jack_Thomas
Estimated Time to Solve: 60 Minutes
We provide the solution to this challenge using:
- UIPath
Need a program? Click here.
Overview
You are an accounts payable associate for a large conglomerate company called Little Laboratories LLC. Little Laboratories has 22 subsidiaries, which offer a broad spectrum of services ranging from patent/legal and venture capital work to entrepreneurial R&D and other engineering-related pursuits. Despite the wave of invoices that you receive each week, you are the sole member of the accounts payable team responsible for processing them. After you process the invoices in the payables software, they are sent to the corresponding subsidiary’s executive for approval. Following approval, the system pushes the invoices into QuickBooks and prepares the bills for payment.
Each week, you review the invoices that haven’t been approved as of Friday at 5pm and send reminder emails to those that are responsible for approving them. Some executives are good at clearing out their approval queue before the end of the week and don’t require an email. Others fall behind and accumulate past-due invoices and late fees. Approvers with more than 2 past due invoices must be reported to Little Laboratories’ CFO.
In Passion For Payables you decided to build a workbook that streamlines the process. Now, you will build a bot that will write a letter to each executive informing them of which payables still need approved. Assume that today is 4/14/2023. Your bot should do the following:
You will build a program that will take the payables information and insert it into the template found below. The template should be copied for each of the executives and emailed to them at a later point in time.
Instructions
- Download the data file and letter template and create a new workflow in UIPath. Consider reviewing the letter template to determine which information you will need to send to each executive.
- Both the workflow and the raw data should be saved in the same folder (this will help in steps down the line)
- In the UIPath workflow, create a variable that is the path to the folder containing all of the challenge data.
- Read in the data from the excel spreadsheet using an “Excel Application Scope” tool and save the information as a data table. See hint for more details.
- For each row in the data table, create a copy of the Template excel workbook with the format of “Approver_#.xlsx”
- Create a new variable with your name to be used when filling in the newly created templates
- For each row in the data table, copy the information into the newly created templates
a. Open the desired excel file (Approver_#.xlsx)
b. Write information from column 1 to the corresponding cell
c. Repeat step “b” until each corresponding cell has been filled
d. Insert your name at the bottom of the letter
e. Make sure the file is saving changes and close the file - Congratulations! You can now email the files to the executives to inform them of which invoices they still need to approve.
Data Files
Suggestions and Hints
- Using data tables allows you to temporarily store data as a variable. You can then use tools such as “For each row in datatable” to perform repetitive tasks, such as copying a template or filling in the recently created templates.
- Use a variable to store the file path so you don’t have to recode it each time.
- Using the “Excel Process Scope” and “Use Excel File” will allow you to select a template, and reference a specific excel workbook reading in a range of data, or when writing to a cell.
- Make sure each variable’s “Scope” is set properly, meaning that it is accessible across the whole sequence
- When assigning columns from the data table to specific cells, you can either use the column number (starting at 0) or you can use the column name in parentheses
Solution