Example | Table steering

This example shows how to work with tables in RPA Studio.

Goal

You want to automate the following process:

• Take user data from a table.

• Insert the data into a form in your application as part of a particular transaction.

• Update the table with the transaction ID that your application generates.

• Update the table with information on when the data was processed.

Situation

In this example, you are working with the table below. The first row is the header row.

Since this is a Microsoft Excel table, you will be using the Excel Modules from the RPA subset.

However, this example also applies to tables you have scanned. The Table element in scanned Modules is the same as in the Excel Modules. The main difference is how you access the table.

Steer the table

To achieve your goal, you need to create the following Bot:

The Transaction Bot is a separate Bot. It accesses your application, performs the transaction, generates a transaction ID, and saves it to a Buffer called TransactionID.

This example focuses on table steering.

GROUP: Preparatory Steps

In this Group, you have bundled a few preparatory Steps.

For Excel tables, these are Steps you have created from the Modules Open Excel Workbook and Define Excel Range.

If you work with scanned tables, these are the Steps the Bot needs to do to access the table.

Conditional LOOP

The actual table steering happens in the Conditional LOOP.

You will repeatedly create Steps from the Module Excel Range Manipulation.

If you have a scanned table, use the Module that contains the scanned table.

1. The first Step is Excel Range Manipulation: Count and buffer. In it, you want to find out how many rows exist that have empty cells in the column Transaction ID. The Bot should also save the result count.

   So you use the property ResultCount and limit the search to empty cell values in the Transaction ID column.

   To specify empty cells, click into the Value field. Enter an empty space and then delete it.

In this example, the result count is 6. The Bot saves it to a Buffer named CountEmptyID.

2. Now it's time to define the Control Expression of the Conditional LOOP.

   You want the Bot to check the value of the Buffer CountEmptyID. If that value is higher than 0, the Bot should go through the LOOP.

Enter the Control Expression {B[CountEmptyID]} > 0. In the Details pane, set the DataType to Numeric.

3. If the result of the Control Expression above is TRUE, that means there are empty cells in the Transaction ID column. In this case, the Bot performs the following actions:

   • It searches for the first row that has an empty cell in the Transaction ID column.

   • It saves the content of the cells that you have identified in this row.

In this example, the first row with an empty cell in the Transaction ID column is row number 2.

The Bot saves the first name, last name, and DOB: Jane, Doe, and 24.12.1980.

4. Next, you add the Bot Transaction Bot. It accesses your application, performs the transaction, generates a transaction ID, and saves it to a Buffer called TransactionID.

Back to table steering in your Conditional LOOP:

5. In the next Step, the Bot performs the following actions:

   • It searches for the first row that has an empty cell in the Transaction ID column.

   • It writes the transaction ID you generated with the Transaction Bot into the Transaction ID column.

   • It uses a Date Expression to insert the current date and time into the Processed column at run-time.

In this example, the first row with an empty cell in the Transaction ID column is row number 2. That's the row from which the Bot took the user data in step 3.

The Bot writes the transaction ID and Date Expression into the identified cells in row number 2.

6. Then the Bot goes back to the first Step of the LOOP and counts the rows with an empty cell in the column Transaction ID.

   The result of this count is now 5. This is still higher than 0, so the Control Expression triggers the next iteration.

7. Now, the first row with an empty cell in the Transaction ID column is row number 3. The Bot saves the cell content, and the Transaction Bot uses them to generate another transaction ID.

8. The Bot inserts them in row 3 and goes back to counting rows.

At one point, the Bot will have populated all cells in the column Transaction ID. In this case, the ResultCount is 0.

The Control Expression is now FALSE, so the Bot continues on the No path and leaves the Conditional LOOP.

Save and close

The final Step of the Bot saves and closes the Excel file with the Module Close Excel Workbook:

If you are working with a scanned table, you will have to perform different Steps, for instance logging out of your application and closing the browser.

Your table now looks like this: