Automate the extraction of Order Details in Order Management Application
- How to build an SAP Intelligent RPA automation to extract data from an SAPUI5 screen.
- How to get the header and line items data of a table.
- How to use sub-automations.
Prerequisites
- You have completed the first exercise: Capture Order Management Application Using the Recorder for SAPUI5
The screens and elements have been defined. You can now create the actual automation.
Think about what your bot should do. On a very high level it must:
- Open Excel and read out some data from there.
- Open a webpage and extract some other data from there, based on the data from Excel.
- Write the found data back into Excel.
- Step 1
NOTE: You can reuse the automation Browse Orders Automation created by the screen recorder and modify it later.
For now, start by creating two additional automations: the main one (named Order Management) and the other one for Excel (named Write Excel).
-
Make sure that the Overview tab is selected and then click the Create button and select the Automation artifact from the artifact menu.
-
Name the Automation: Order Management and click Create.
An empty automation is created.
-
Go back to the Overview tab, create a new Automation and name it: Write Excel.
Now you have three Automations:
You reuse your recorded automation Browse Orders Automation and rename it to Web Portal. You can also use it as a sub-automation to open the web portal and extract the needed data.
-
Activate the Browse Orders Automation tab. If the tab is closed, go to Overview and open it.
-
Go to the Info section of Automation Details.
-
Change the Name of the automation to Web Portal.
-
Click Save.
Generic Guide: Show and Hide Artifacts
To show artifacts click the following highlighted button:
To hide artifacts click the following highlighted button:
Generic Guide: How to Add Automation Steps
You repeat this process multiple times to add new steps to the automation. Refer to the following steps.
-
Make sure that the Order Management automation is selected (check tab).
-
Click on the empty space within the preview to close all other opened options.
-
Search for the activity in the search bar.
-
If you can’t find a step, click clear filter to show all available steps.
-
Select the desired action and drag and drop it into the workflow.
-
- Step 2
Define the general structure of the automation. The following is the data that needs to be retrieved from excel:
-
First, open Excel. Add the Open Excel Instance activity to the automation.
-
You want to open the correct workbook. Add the Open Workbook activity.
In the Input Parameters on the right, fill the
workbookPath
field with the path to the file and select the proposed string.Or you can edit the expression and enter the full path to your file. Make sure the path is within quotation marks “…”. For example:
"C:\Users\Public\saprpa\Demo_Procurement.xlsx"**
-
Now, you must open a specific worksheet. Add the Activate Worksheet activity.
-
Set
worksheetName
field to Overview. -
Finally, you must get a value from a cell.
- Add the Get Values (Cells) activity
- Set
rangeDefinition
field toB1
- Rename Output Parameters to
supplierName
So far you read Excel Cell B1 and saved the result in a variable called
supplierName
. -
Add Web Portal and Write Excel sub-automations. They just act as placeholders.
-
At the end you must make sure that Excel is not used by the SAP Intelligent RPA anymore. Therefore, you add the Release Excel Instance activity to release the instance.
-
Click Save.
Now, you check how the bot works and if the automation has any mistakes.
-
Click the Test button.
- Save all changes when prompted.
- Select your environment and click Test.
It takes a while for the desktop agent to download, process and run the automation. The following messages are displayed:
Once the automation is completed, you can see the debug screen (Tester). Here, you can either check the output of the Test Console or click on single steps in the automation to see the details on the right side.
For instance, click on the Get Values (Cells) step and you can see the correct output value on the right Bottom-Dollar Markets. The automation ran correctly.
NOTE: Please don’t forget to close both excel, as well as the SAPUI5 application each time you test the automation.
-
- Step 3
The Recorder created an automation that you renamed to Web Portal. On a high level, refer to the following steps:
- Enter supplier name in the search box and search.
- Click on the result.
- This will open the details of the order.
Now, you must modify the recorded automation and enhance it. You must make sure that your bot saves all the relevant information which you will later write into Excel.
-
Open the Web Portal automation, either on the left menu or on the tab on the top.
-
Click on the Start Screen step. There is no need to modify anything however if you want, here you can change the default web browser application. Select only chromium web browsers (Chrome or Microsoft Edge).
-
Click on the Browse Orders and Click Define Screen Activities.
-
To make your automation more robust, you must be sure that the screen is fully loaded.
Search for the Wait (Screen) activity, and drag and drop it after the Start Screen activity. The SAP Intelligent RPA will wait until the screen is fully loaded. -
Additionally, The SAP Intelligent RPA must wait to ensure that the search box loads as well. Search for the Wait (Element) activity.
You have two options on how to add it to your automation. Check them and apply the one you prefer.
OPTION 1: Drag and drop the Wait (Element) activity on the search box. Make sure the cursor changes to + before releasing. The step is added as the last one. Move it just after the Wait (Screen) step.
OPTION 2: Drag and drop the Wait (Element) activity after the Wait (Screen) step.
Click on the Target editor.
Select the
SearchField
element and click Confirm.Input Parameter
In the Set Element (SAPUI5 Recorder) step, you search for the supplier name. You recorded a fixed value and now, you must modify the automation in such a way that you search for a value defined in Excel.
But how to get the name of the supplier from Excel? It’s not available in the Web Portal sub-automation yet. That variable is only available in the main Order Management automation. But you can pass the variable to the sub-automation using Parameters.
-
First, rename the automation label from Set Element (SAPUI5 Recorder) to Search String.
-
Click on an empty space to change the settings of the automation. Click the Input/Output tab and then click Add new input parameter.
-
Create an input parameter named
CustomerName
.Make sure that the type is String. Click on an empty space (or press Enter).
-
Go, back to the Search String step, delete the old value and select the
CustomerName
variable, which is the input parameter passed to the sub-automation.Click Save.
However, the value still hasn’t been passed from the main automation. To do that:
-
Go to the main automation either from the left side or from the tabs at the top. Select the Web Portal step and set the
CustomerName
field tosupplierName
. -
Click Save.
- Step 4
Next, make sure that your search results are loaded before you continue with your automation.
-
Add the Wait (Element) activity just after the Search String step.
-
Click Target and select the
OrderFound
element. -
The SAP Intelligent RPA should wait for the details screen to open and load. Add a Wait (Element) step.
-
Click Target and select the Price element.
-
Save the Order Number in a variable. Click on the Get Element step.
-
Set the target to Order Number element.
-
Rename the output parameter field to
orderNumber
. -
You can test now. If you go to the main automation (tab Order Management) and start test, you must select an environment. Don’t forget to close excel.
If you remain in the Web Portal tab, you must enter
CustomerName
because you will only test this automation and value will not be fetched from Excel (enter search value: Bottom-).In both cases, you will see that the received get element value is Order 2686.
-
Next, get the Price element and save it in the price variable.
-
If you run a test again you can check the value.
NOTE: You can also rename these two Get Element activities to more meaningful names (for example, Get Element (price) and Get Element (order number)).
-
- Step 5
-
Use the Get Element activity for the Shipping Address table. Drag and drop the activity on the table.
-
Select ITEM: Shipping Address and INDEX: all.
The shipping address is nested so a “For Each” loop has been automatically created.
-
Click on the Get Element activity and define the Output Parameters field to array.
-
Click Save.
-
Use the Get Element activity for the Line Items fable. Drag and drop the activity on the Table Data.
-
Select ITEM: Table Data and INDEX: all.
The Table Data is Two-Dimensional, hence two “For Each” loop has been automatically created.
-
Click on the Get Element activity and define the Output Parameters field to
TableCellData
. -
Click Save.
Use the Log Message activity to display the values of each element.
-
Search for the Log Message activity and drag and drop into the workflow after the Get Element step of the For Each - 3 loop
-
Click on the Log Message activity and select
TableCellData
in the message field. -
Click Save.
-
Add another Log Message activity to display the value of the row number. Drag and drop it after the For Each - 2 loop.
-
Click on the Log Message activity, and click the expression editor button for the Input Parameters message.
-
Edit the expression to ‘Row no :’ + Step12.index.
NOTE: Step number 12 is the first for loop to display the row count.
-
Click Save and test the automation. As you can see in the Test Console, all the product information, product name, unit price, quantity, and total amount are displayed cell by cell.
Which of the following statement about multi-level collections is true based on the example of a table with two dimensions to retrieve the cell data?
-
- Step 6
The name, address, zip and so on from the shipping address are currently stored in an array. You can add better structure to that by using Data Types from the SAP Intelligent RPA. These data types can store multiple variables and named fields to represent more complex data structures. Create two data types: one for a shipping address and another for a line item.
Save the automation you have so far. Go to Overview tab.
-
Click Create > Data Type.
-
Name the data type Line Item and click Create.
-
Repeat the same steps to create another data type named Shipping Address.
-
Now, go back to the Line Item data type, create a new field by clicking on the New Field
-
Adjust the field properties to name Product and type String.
-
Repeat the steps to create the other fields:
UnitPrice
,Quantity
,Total
. All of these are type String. -
Repeat same steps to create fields inside the Shipping Address datatype: Name, Street, ZIP, Region, Country. All of these are type String.
-
Click Save to save both data types.
-
- Step 7
-
Go back to the Web Portal automation and drag and drop the Shipping Address data type just after the Get Element (Price) step.
-
Define the Output Parameters field to
shippingAddress
. -
Select the Input Parameters value to Create Custom Data.
-
Drag a Condition step and drop it under Get Element step.
-
Select the Condition step and click Edit Formula.
-
In the Expression Editor, type Step11.index === 0 and click Save Expression.
-
Click Add Condition and type the following formula in the Expression Editor:
Step11.index === 1 -
Similarly, add three other Conditions and enter the following formulas in the Expression Editor:
- Step11.index === 2
- Step11.index === 3
- Step11.index === 4
The automation will look like this:
-
Now, drag a Set Variable Value activity and drop it below the first condition.
-
Click on the Set Variable Value activity and change the step name to Set Variable Name.
-
Select the variable field value to
shippingAddress
. -
Select the value field value to Custom Data.
-
Select the Name field value to array.
-
Drag a Set Variable Value activity and drop it under each Condition.
-
Repeat steps 10-13 with the below information :
Field Name Value Step Name Set Variable Street variable shippingAddress
value Street: array Field Name Value Step Name Set Variable Zip variable shippingAddress
value Zip: array Field Name Value Step Name Set Variable Region variable shippingAddress
value Region: array Field Name Value Step Name Set Variable Country variable shippingAddress
value Country: array -
- Step 8
Start by creating a variable which will contain multiple line items.
-
Add the Line Item data type after the For Each loop.
-
Make sure to check the list option and define the Output Parameters field to
lineItems
. -
Now add a Line Item data type inside the For Each - 2 loop.
-
Select the Input Parameters value to Create Custom Data.
-
Set the Output Parameters field to
lineItem
. -
Drag an Add Item (List) activity and drop it as well in the For Each - 2 loop, after Log Message activity.
-
Select the Add Item (List) activity and select the
list
field value tolineItems
anditemToAdd
field value tolineItem
. -
Drag and drop a Condition step in the For Each - 3 loop after Get Element activity.
-
Add four Conditions with the following Condition Expression:
- Step24.index === 0
- Step24.index === 1
- Step24.index === 2
- Step24.index === 3
-
Now, drag and drop a Set Variable Value activity for each Condition.
-
Click on the Set Variable Value activity and change the step name to Set Variable Product.
-
Select the variable field value to
lineItem
. -
Select the value field value to Custom Data value.
-
Select the Product field value to
TableCellData
.
Repeat steps 10-13 with the following information :
Field Name Value Step Name Set Variable Unit Price variable lineItem
value Unit Price: TableCellData
Field Name Value Step Name Set Variable Quantity variable lineItem
value Quantity: TableCellData
Field Name Value Step Name Set Variable Total variable lineItem
value Total: TableCellData
-
- Step 9
Now, you have to pass data between sub-automation. You can do this by using input and output parameters.
The web automation will get the partner name from the main automation and return the order number, price, shipping info and line item details. This data will in turn be passed to the excel automation.
-
Select the Web Portal automation. Click anywhere on the grey area to open the automation parameters.
-
Make sure that an input parameter called
CustomerName
of type string is defined. -
Create output parameters and make sure that the List box is checked for Line Item parameter.
Name Type List orderNumber
String no price
String no shippingAddress
Shipping Address no lineItems
Line Item yes -
Now, you define which variables will be returned. Click on End step, select Parameters and select the respective variables for outputs.
-
Click Save to save the automation.
-
- Step 10
Now, you must automate the Excel part. You have collected the data from the SAPUI5 application. You must insert the data back into Excel. You will do this in the Write Excel sub-automation.
The following is the expected result:
-
Switch to the Write Excel automation. Start by adding input parameters, which you received from the Web Portal sub-automation.
-
Add the same input parameters as the output parameters you added in the web automation.
Name Type List orderNumber
String no price
String no shippingAddress
Shipping Address no lineItems
Line Item yes You can further enhance the bot by setting all the product details in the Excel. Use the Set Values activity of Excel to set the values in the Excel.
-
Use the Set Values (Cells) activity to write data into a specific cell. Drag and drop the activity into the workflow of the automation.
-
Click on the Set Values (Cells) activity, change the name to Set Cell (Order Number) and set the
rangeDefintion
field value toB2
andvalues
field value toorderNumber
. -
Duplicate this step (right click on Set Values (Cells)).
-
Change the name to Set Cell (Price) and set
rangeDefinition
field value toB3
andvalues
field value toprice
. -
Duplicate this step (right click on Set Cell (Price)).
-
Change name to Set Cell (Name) and set
rangeDefinition
field value toB4
. -
Change the values using the expression editor, to get the value inside the data type.
-
Write Step0.shippingAddress.Name in the Expression Editor.
NOTE: Step0 contains the input parameters.
-
Duplicate the steps to save time and adjust the parameters according to the following table:
rangeDefinition
values B2
orderNumber
B3
price B4
Step0.shippingAddress.Name B5
Step0.shippingAddress.Street B6
Step0.shippingAddress.ZIP B7
Step0.shippingAddress.Region B8
Step0.shippingAddress.Country
Which activity is used to write data into a specific cell?
-
- Step 11
Now, you add a second worksheet for the line item details.
Desired result:
-
Drag and drop an Activate Worksheet activity and configure
worksheetName
field value to Details. -
Click Save.
This action will switch to the second worksheet with the order details.
-
Add a For Each control to loop through the order line items. The looping list is
lineItems
. -
Drag and drop a Set Values (Cells) activity inside the loop.
-
The data cells start at row 2, the index starts at 0, so use the following formula in the
rangeDefintion
field to determine the correct cell:“A” + (Step9.index + 2)
For the Values parameter, set the
values
field value to:Step9.currentMember.Product
NOTE: Make sure that you use the Edit Expression to enter formulas in
rangeDefinitio
. The text should be in blue color, not black! -
Duplicate and repeat for Unit Price, with column
B
instead. -
Duplicate and repeat for Quantity, with column
C
instead. -
Duplicate and repeat for Total, with column
D
instead. -
Save all automations.
-
- Step 12
-
To add the headers : drag four Set Values (Cells) activities and drop them into the workflow after Activate Worksheet.
-
Adjust the parameters according to the following table:
rangeDefinition
values A1
Product B1
Price C1
Quantity D1
Total
-
- Step 13
-
Within the main automation Order Management, you pass the outputs from Web Portal into the inputs of Write Excel
-
Make sure that output parameters are defined for both sub-automation as displayed in the following screenshots:
-
Now, you can test the automation. You must see the following results:
Congratulations! You have now completed this tutorial that read complex data from a website and stored it into an Excel file with two Worksheets.
-
- Define the Automations
- Order Management
- Search for Order Number
- Get Order Info
- Get Shipping Address and Line Items Information Using Multi-level Collection
- Create Data Types for Shipping Address and Line Item
- Add Shipping Address Data Type in Web Portal Automation
- Add Line Item Data Type in Web Portal Automation
- Input/Output Parameters
- Write Excel Automation - First Worksheet
- Write Excel Automation - Second Worksheet
- Adding the headers
- End