Automate the Extraction of Order Details in Orders Management Application

NOTE: You can reuse the automation Orders Management 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 an other one for Excel (named Write Excel).

1. Make sure that the Overview tab is selected and then click the Create button and select the Automation artifact from the artifact menu.

   

2. Name the Automation: Order Management and click Create.

   

   An empty automation is created.

3. Go back to the Overview tab, create a new Automation and name it: Write Excel.

   Now you have three Automations:

   

   Let’s reuse the Recorder generated automation Orders Management Automation. Rename it to Web Portal. You can also use it as a sub-automation to open the web (Orders Management Application) and extract the needed data.

4. Activate the Orders Management Automation tab. If the tab is closed, go to Overview and open it.

5. Go to the Info section of Automation Details.

6. Change the Name of the automation to Web Portal.

7. Click Save.

   

In this automation, you will retrieve the data from this excel.

Define the general structure of the automation. The following is the data that needs to be retrieved from excel:

1. Add the Open Excel Instance activity to the automation to open an Excel.

   

2. 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\sbpa\Demo_Procurement.xlsx"

   

3. Now, you must open a specific worksheet. Add the Activate Worksheet activity.

   

4. Set worksheetName field to Overview.

   

5. Finally, you must get a value from a cell.

   • Add the Get Values (Cells) activity
   • Set rangeDefinition field to B1
   • Rename Output Parameters to supplierName
   

   So far, you read Excel Cell B1 and saved the result in a variable called supplierName.

   

6. Add Web Portal and Write Excel sub-automations. They just act as placeholders.

   

7. At the end you must make sure that Excel is not used by SAP Build Process Automation anymore. Therefore, you add the Release Excel Instance activity to release the instance.

   

8. Click Save.

   Now, you check how the bot works and if the automation has any mistakes.

9. Click the Test button.

   

10. Click Test.

    

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.

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.

1. Open the Web Portal automation, either on the left menu or on the tab on the top.

   

2. Click on the Start Web Page 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).

   

3. Click on the Browse Orders and choose Define Screen Activities.

   

4. 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 Web Page activity. SAP Build Process Automation will wait until the screen is fully loaded.

   
   

5. Additionally, SAP Build Process Automation 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 Search Field element and click Confirm.

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.

1. First, rename the automation label from Set Element (SAPUI5 Recorder) to Search String.

   

2. Click on an empty space to change the settings of the automation. Click the Input/Output tab and then click Add new input parameter.

   

3. Create an input parameter named CustomerName.

   Make sure that the type is String. Click on an empty space (or press Enter).

   

4. 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:

5. 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 to supplierName.

   

6. Click Save.

Next, make sure that your search results are loaded before you continue with your automation.

1. Add the Wait (Element) activity just after the Search String step.

   

2. Click Target, select the OrderFound element and choose Confirm.

   

3. SAP Build Process Automation should wait for the details screen to open and load. Add a Wait (Element) step.

   

4. Click Target, select the Price element and choose Confirm.

   

5. Save the Order Number in a variable. Click on the Get Element step.

   

6. Set the target to Order Number element and choose Confirm.

   

7. Rename the output parameter field to orderNumber.

   

8. 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.

   

9. Next, get the Price element and save it in the price variable.

   

10. 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)).

1. Use the Get Element activity for the Shipping Address table. Drag and drop the activity on the table.

2. Select ITEM: Shipping Address and INDEX: all.

   

   The shipping address is nested so a “For Each” loop has been automatically created.

   

3. Click on the Get Element activity and define the Output Parameters field to array.

4. Click Save.

   

5. Use the Get Element activity for the Line Items table. Drag and drop the activity on the Table Data.

6. Select ITEM: Table Data and INDEX: all.

   

   The Table Data is Two-Dimensional, hence two “For Each” loop have been automatically created.

   

7. Click on the Get Element activity and define the Output Parameters field to TableCellData.

8. Click Save.

   

   Use the Log Message activity to display the values of each element.

9. Search for the Log Message activity and drag and drop into the workflow after the Get Element step of the For Each - 3 loop

   

10. Click on the Log Message activity and select TableCellData in the message field.

11. Click Save.

    

12. Add another Log Message activity to display the value of the row number. Drag and drop it after the For Each - 2 loop.

    

13. Click on the Log Message activity, and click the expression editor button for the Input Parameters message.

14. Edit the expression to ‘Row no :’ + Step13.index.

    NOTE: Step number 13 is the first for loop to display the row count.

    

15. 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.

    

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 SAP Build Process Automation. 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.

1. Click Create > Data Type.

   

2. Name the data type Line Item and click Create.

   

3. Repeat the same steps to create another data type named Shipping Address.

   

4. Now, go back to the Line Item data type, create a new field by clicking on New Field.

   

5. Adjust the field properties to name Product and type String.

   

6. Repeat the steps to create the other fields: UnitPrice, Quantity, Total. All of these are type String.

   

7. Repeat same steps to create fields inside the Shipping Address datatype: Name, Street, ZIP, Region, Country. All of these are type String.

   

8. Click Save to save both data types.

1. Go back to the Web Portal automation and drag and drop the Shipping Address data type just after the Get Element (Price) step.

   

2. Define the Output Parameters field to shippingAddress.

   

3. Select the Input Parameters value to Create Custom Data.

   
   

4. Drag a Condition step and drop it under Get Element step.

   

5. Select the Condition step and click Edit Formula.

   

6. In the Expression Editor, type Step11.index === 0 and click Save Expression.

   

7. Choose Add Condition and type the following formula in the Expression Editor:

   • Step11.index === 1
   

8. 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:

   

9. Now, drag a Set Variable Value activity and drop it below the first condition.

   

10. Click on the Set Variable Value activity and change the step name to Set Variable Name.

11. Select the variable field value to shippingAddress.

12. Select the value field value to Custom Data.

13. Select the Name field value to array.

    

14. Drag a Set Variable Value activity and drop it under each Condition.

15. Repeat steps 10-13 with the below information :

Start by creating a variable which will contain multiple line items.

1. Add the Line Item data type after the For Each loop.

2. Make sure to check the list option and define the Output Parameters field to lineItems.

   

3. Now add a Line Item data type inside the For Each - 2 loop.

4. Select the Input Parameters value to Create Custom Data.

5. Set the Output Parameters field to lineItem.

   

6. Drag an Add Item (List) activity and drop it as well in the For Each - 2 loop, after Log Message activity.

   

7. Select the Add Item (List) activity and set the list field value to lineItems and itemToAdd field value to lineItem.

   

8. Drag and drop a Condition step in the For Each - 3 loop after Get Element activity.

   

9. Add four Conditions with the following Condition Expression:

   • Step24.index === 0
   • Step24.index === 1
   • Step24.index === 2
   • Step24.index === 3
   

10. Now, drag and drop a Set Variable Value activity for each Condition.

    

11. Click on the Set Variable Value activity and change the step name to Set Variable Product.

12. Select the variable field value to lineItem.

13. Select the value field value to Custom Data value.

14. Select the Product field value to TableCellData.

    

Repeat steps 11-14 with the following information :

Now, you have to pass data between sub-automations. 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.

1. Select the Web Portal automation. Click anywhere on the grey area to open the automation parameters.

2. Make sure that an input parameter called CustomerName of type string is defined.

   

3. 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

   

4. Now, you define which variables will be returned. Click on End step, select Parameters and select the respective variables for outputs.

   

5. Click Save to save the automation.

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:

1. Switch to the Write Excel automation. Start by adding input parameters, which you received from the Web Portal sub-automation.

   

2. 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.

3. Use the Set Values (Cells) activity to write data into a specific cell. Drag and drop the activity into the workflow of the automation.

   

4. Click on the Set Values (Cells) activity, change the name to Set Cell (Order Number) and set the rangeDefintion field value to B2 and values field value to orderNumber.

   

5. Duplicate this step (right click on Set Values (Cells)).

   

6. Change the name to Set Cell (Price) and set rangeDefinition field value to B3 and values field value to price.

   

7. Duplicate this step (right click on Set Cell (Price)).

8. Change name to Set Cell (Name) and set rangeDefinition field value to B4.

9. Change the values using the expression editor, to get the value inside the data type.

   

10. Write Step0.shippingAddress.Name in the Expression Editor.

    NOTE: Step0 contains the input parameters.

    

11. 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

    

Now, you will add a second worksheet for the line item details.

Desired result:

1. Drag and drop an Activate Worksheet activity and configure worksheetName field value to Details.

2. Click Save.

   This action will switch to the second worksheet with the order details.

   

3. Add a For Each control to loop through the order line items. The looping list is lineItems.

   

4. Drag and drop a Set Values (Cells) activity inside the loop.

   

5. The data cells start at row 2, the index starts at 0, so use the following formula in the rangeDefinition 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 rangeDefinition. The text should be in blue color, not black!

   

6. Duplicate and repeat for Unit Price, with column B instead.

   

7. Duplicate and repeat for Quantity, with column C instead.

   

8. Duplicate and repeat for Total, with column D instead.

   

9. Save all automations.

1. To add the headers : drag four Set Values (Cells) activities and drop them into the workflow after Activate Worksheet.

2. Adjust the parameters according to the following table:

   rangeDefinition	values
   A1	Product
   B1	Price
   C1	Quantity
   D1	Total

   

1. Within the main automation Order Management, you pass the outputs from Web Portal into the inputs of Write Excel

   

2. Make sure that output parameters are defined for both sub-automation as displayed in the following screenshots:

   
   

3. 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.