Creating an expense tracker using MS Office connectors

5 Tasks

30 mins

Pega Robotic Automation 22.1 Visible to: All users

Beginner Pega Robotic Automation 22.1 Robotic Process Automation English

Scenario

The Robotic Banking company provides an expense tracker functionality that collects all spending for predefined categories, creates an Excel report, formats it, and saves it to a PDF file. 

Complete the following tasks:

• Create an automation that handles a user request for expense tracking.
• Fill in an Excel spreadsheet with data for pre-defined categories.
• Format the spreadsheet using the Excel Connector.
• Save the report as a PDF file.

You must initiate your own Pega instance to complete this Challenge.

Initialization may take up to 5 minutes so please be patient.

• Log In

Challenge Walkthrough

Detailed Tasks

1 Create a RequestExpenseReport automation that handles an operator request for expense tracking

1. Download the Expense Tracker Challenge project: ​
   RoboticBanking-ExpenseTracker-Start.zip (198.95 KB)
2. Save the file to your desktop and unzip the file to extract the project folder to your C:\Users\<username>\Documents\Pega Robot Studio\Projects\.
3. On the Home screen in Pega Robot Studio, click Open Project, and then navigate to the projects folder and open RoboticBanking.pegaproject. 

Note: The starter project includes a configured BankerInsight application. The value of the Path field must match the location of BankerInsight on your local computer for the project to function properly. In the BankerInsight application, change the value of the Path to the location of your BankerInsight application.

4. In the Project Explorer, in the Expense tracker folder, open the RequestExpenseReport automation.
5. On the automation surface, click and drag the gray dot that represents the output automation link to open the Toolbox dialog box. 
6. In the Toolbox dialog box, in the search field, enter InputBox.
   
7. In the Toolbox dialog box, in the Accelerators section, double-click the ShowInputBox method to add it to the automation surface.
   Note:  The ShowInputBox method is also listed in the Utilities section of the toolbox. This method is the same in both locations and is listed in multiple locations for convenience.
8. Configure the InputBox variables with the following values by clicking each parameter name on the design block:

   Variable	Value
   Message	Insert a customer ID
   Title	Expense tracking request
   Default value	1234500078963456

    
   
9. On the Palette, in the Automations section, drag the PrepareAppAndData automation to the design surface to open a Select action dialog box.
10. In the Select action dialog box, select the Run method and then click Add to add a sub-automation to the automation surface. 
    Note: The PrepareAppAndData automation logs into the BankerInsight application, searches for a customer with a given customerId, and prepares default expense categories. Open the automation in Project Explorer to see more details.
11. On the Palette, in the Globals section, drag the Categories Lookup Table to the automation surface to open a Select action dialog box.
12. In the Select action dialog box, select the GetKeys method and then click Add to add it to the automation surface. The GetKeys method returns all tracing categories saved in the Lookup Table.
13. In the Toolbox dialog box, in the search field, enter ListLoop.
14. In the list of filtered results, drag List Loop to the automation surface. 
15. On the automation surface, connect the links as shown in the following example:
    
16.  Save the automation. 

2 Add an Excel Connector to the project.

1. Click the Project tab to open the Project Explorer.
2. In the Project Explorer, click and open the Globals tab.
3. In the Toolbox dialog box, in the search field, enter Excel to find ExcelConnector. 
   
4. Drag ExcelConnector to the Globals tab to add it to the project. 
5. On the Property Grid, change the name of ExcelConnector to ExcelConnector_ExpenseTracker. 
6. On the Property Grid, in the FileName property, click the More icon to select a template Excel file. 
   
7. in the Open dialog box, navigate to: C:\Users\<username>\Desktop\RobotStudioChallengeAssets, and select SpendingsReport file.  
8. On the Property Grid, in the SaveAsName property, click the More icon to select the Excel file that stores the report.
9. In the Open dialog box, navigate to: C:\Users\<username>\Desktop\RobotStudioChallengeAssets, and select SpendingsReport file.
10. On the Property Grid, set the SheetName property to Report template.
    
11. Save the changes on the Globals tab.

3 Update the AddAmountsToExcel automation that finds each category transaction and saves the amount to the excel file.

1. Click the Project tab to open the Project Explorer.
2. In the Project Explorer, click and open the AddAmountsToExcel automation. The AddAmountsToExcel automation loops through transaction amounts for a given category.  
3. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
4. In the Select action dialog box, in the search field, enter SetCellValue.
5. In the list of search results, click the arrow to expand the functions group. 
6. In the Select action dialog box, select the SetCellValue (3 parameters) method and then click Add to add it to the automation surface.
   
7. On the Palette, in the Locals section, drag the itemCount variable to the automation surface to open the Select action dialog box. 
8. In the Select action dialog box, add the Value property to define a row for the amounts in the expense report. 
9. Repeat steps 7-8 to define the Value property for the Amount variable for the expense report.
10. On the automation surface, connect the links as shown in the following example, to fill in the expense report:
    
11. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to pen the Select action dialog box.
12. In the Select action dialog box, select the Save (no parameters) method, and then click Add to add it to the automation surface to save the changes made in the Excel file.
13. On the automation surface, connect the links as shown in the following example:

Note: Keep in mind the dgvAcctTrans design block contains all of a specific customer’s transactions made using Robotic Banking. 

14. Save the automation. 

4 Create the EditTrackingReport automation to format the expense report.

1. Click the Project tab to open the Project Explorer.
2. In the Project Explorer, click and open the EditTrackingReport automation.
3. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
4. In the Select action dialog box, select the SetRowHeight method and then click Add to add it to the automation surface.  
5. Configure the SetRowHeight variables with the following values to create the report header. Click each parameter name on the design block to change its value.

   Variable	Value
   row	1
   height	50

6. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker item to the automation surface to open the Select action dialog box.
7. In the Select action dialog box, select the SetCellBackgroundColor (2 parameters) method and then click Add to add it to the automation surface.  
   
8. Click inside each of the SetCellBackgroundColor variables to set the values with the following to format the report header:

   Variable	Value
   address	A1
   color	255, 128, 0 (select orange)

9. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box.
10. In the Select action dialog box, select the SetCellStringValue (2 parameters) method and click Add to add it to the automation surface.  
11. Configure the SetCellStringValue variables with the following values to format the report header:

    Variable	Value
    address	A1
    value	Expense tracking report

12. On the Palette tab, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box.
13. In the Select action dialog box, select the Calculate (no parameters) method and click Add to add it to the automation surface to save the changes made in the Excel file.
14. Repeat steps: 12-13 to add the Save method to the design surface. 
15. On the automation surface, connect the links as shown in the following example:  
    
16. Save the automation.

5 Update the RequestExpenseReport to use EditTrackingReport automation and to save the expense tracking report as PDF.

1. Click the Project tab to open the Project Explorer.
2. On Project Explorer, click and open the RequestExpenseReport automation.
3. On the Palette, in the Automations section, drag the AddAmountsToExcel application to the automation surface to open the Select action dialog box.
4. In the Select action dialog box, select the Run method and then click Add to add a sub-automation to the automation surface. 
5. Repeat steps 3-4 to add the EditTrackingReport automation to the automation surface. 
6. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
7. In the Select action dialog box, select the SaveToPDF (1 parameter) method and then click Add to add it to the automation surface to save the changes made in the Excel file as a PDF file.
8. In the fileName parameter of the SaveToPDF method, enter C:\Users\<username>\Desktop\RobotStudioChallengeAssets\ExpenseTracking.pdf
9. On the Toolbox, in the search field enter Toast and then add the Toast Notification to the automation surface.
10. In the message parameter of the ToastNotification, enter Report saved to PDF.
11. On the automation surface, connect the links as shown in the following example: 
    
      
12. Save the automation. 

Confirm your work

1. Click the Project tab to open the Project Explorer.
2. In the Project Explorer, in the Expense tracker folder, open the RequestExpenseReport automation.
3. Click the green Test button, and the blue Test button on the window to begin running the automation.
4. On the displayed Design Form, click: Expense Tracker to trigger an automation.
   
5. In the Expense tracking request dialog box, select OK.
6. Wait till the Report saved to PDF toast notification displays.
7. Select the Stop button in the toolbar to stop testing the automation. 
8. Open the created PDF file: C:\Users\<username>\Desktop\RobotStudioChallengeAssets\ExpenseTracking.pdf
9. Ensure the report is filled in and formatted like in the example below:
   

Note:  If you run into issues while executing the project, revert the Excel file to the original version to prevent overwriting issues. 

---

This Challenge is to practice what you learned in the following Module:

• Additional toolbox options for automations v1

---

Available in the following mission:

• Robotics System Architect v3