Advanced looping capability

New features in TRANSACTION version 10.6/10.7 > Advanced mapping > Advanced looping capability

Back to

Advanced looping capability

Handling multiline transactions

Many SAP transactions require multiple rows of data to be processed at the same time. Examples of these transactions include FB50, F-65, ME21, VA01, CS01, CA01, FB70, and others. For many of these transactions, SAP separates the data into a header section and one or more line item or detail sections. The header section usually contains data that remains constant for the entire transaction such as a date. Each line item section typically contains reiterative data that is entered in a grid-like area of the SAP transaction, such as journal entry line items.

Do...While Loop

TRANSACTION handles complex multi-line transactions using the Do…While Loop feature. With Do…While loops you can map multiple rows to one transaction by defining the records in the Excel worksheet or Access database as Header section or Line Item sections.

In this example, the data for one record is divided among several rows. For multiline transactions, by default the first ID column of Excel is reserved for the Excel Column ID. The first ID Column stores the row type, which defaults to a value of either H (Header) or D (Detail/Line Item). When you run the transaction, TRANSACTION uploads all data from row type H to the Header section of the SAP transaction, and all data from row type D to the line items specified in the Mapper.

NOTE: We strongly recommend that you observe the following conditions when you use TRANSACTION to upload data using multiple loops.

If you are using the same identifier row for different loops, ensure that you use different identifier values. You can use the same identifier even if the loops refer to different identifier columns.
Regardless of whether you use the same or different identifier rows for different loops, if the loops are nested, the data for loops should be in the same order as the loops appear in the mapper. In addition, one row must contain data pertaining only to a single line item record set. If there are multiple loops at a single level, no indentation is required in the Excel file.

While recording the transaction, we recommend that you enter more than one line item in the transaction to create a visible pattern of repetition in the Mapper. Notice that the example shows more than one set of journal entry line items.

Mapping nested loops

When you map the details column of nested loop, the details automatically appear in the second and third rows of the Preview. This functionality also applies when you use the Automap or Read from SAP features.

Preparing your workbook for indented data

When you upload nested loops, the data in your Excel workbook must be laid out in a certain format. The header and its appropriate line items can be in the same column. Each loop can have a header.

Loop data for column-based mapping

H - Header row
D - multiple line items for loop 1
D
D
D1- multiple line items for loop 2
D1
D1

D2- multiple line items for loop 3

Simple loops at the same level

The Mapper layout

The corresponding layout for the Excel workbook

Two loops with one nested loop

The Mapper layout

The corresponding layout for the Excel workbook

Easily check loops

In simpler scripts you can easily see where loops are located. In the Basic tab, select a row, and then click the Advanced Options bar.

The Loop Column check box is selected for the rows mapped within the loop that contain data from records identified as Line Items (details). This setting instructs TRANSACTION to pull data from the Line Item (detail) contained in the Do…While Loop.

NOTE: The Loop Column check box must be checked for at least one of the fields. Otherwise, TRANSACTION script generates a syntax error.

Copying and cutting rows with loops and conditions

You can copy or cut rows that contain loops and conditions. You must select all parts of the loop or condition to edit it. You cannot paste a construct into a log column.

Loop data on multiple sheets

To create a clearer presentation of data when multiple loops are present in a script, the loops can reside on multiple data sheets. Multiple loops are available for Excel and Access data.

Parent column: provides the join key for all sheets

Parent sheet: The parent Excel worksheet that contains the join key

Current sheet: the Excel sheet containing the line items included in the loop

Current column: the column of the data sheet that contains the join key

You can assign the join key to any column. Therefore, consider carefully which columns already contain data.

Invalid mapping

You cannot map a loop to a column that is not part of that loop.
You cannot validate when multiple sheets are present. If conditions validated as sheets are processed.
You can use only column based mapping.
No extended logs. Regular logs written only to header sheet.

To map multiple loops

In the Preview pane of the Mapper screen, add as many new sheets as you need.
Click the first sheet. In the Mapper, select the items for the first loop. Click Loop.
The Loop Master Parent sheet is selected by default.
Define all loops that are at the same level before you define any nested loops.
Map the rest of the line items.
To change the Master parent loop, click Delete all to delete all loops.
When a TXR is published, the published sheet must contain the same sheet names that are in the script.
If the data source is changed, the preview will revert to Untitled. Either add the sheets manually or open a sheet that contains the appropriate names.
Ensure that you save the Preview before closing the application or Preview sheets could be deleted.

New sheets and single sheet mapping

To add new sheets, you must use spreadsheet gear for the Excel sheet preview. Only alphanumeric sheet names are applicable.
To switch to single sheet map, click Clear Loop Data, or delete the loops and then save the script file.

Loops and changing from cell-based mapping to column-based mapping

Loops are retrained mapping can be changed from cell-based to column-based mapping. When the mapping type changes, loops become ID based. For example, B,H,D1 becomes B,D1.

If range-based loops are changed to column-based mapping, and then back to cell-based, all loops will be ID based.

Using the Do...While Loop Feature with Access

TRANSACTION handles complex multi-line transactions using the Do...While Loop feature. The basic principles of using the Do...While Loop with Access as the data source include:

All Header data must reside in one (primary) table selected in Data Source Preview (for Excel or Access).
All Line Item data related to this Header must reside in a separate table within the same database. This Line Item table is selected while inserting the Do...While Loop in the Mapper.
The two tables (Header and Line Item) must have a common field for linking the Line Item data record with its associated Header data record. This common field establishes the relationship between the two tables for the Do...While Loop in the Mapper and the values from these fields must match.
An example of the Header table and the Line Item table is shown below.

Both tables contain a field called COMMON_FIELD, as shown below. COMMON_FIELD can contain any values you choose, as long as the values in the common field Header Table records associated to the Line Item Table (details) records match. COMMON_FIELD is just an integer that matches in both tables.

When the items table is identified and a join is made, the Line Item table is made available in the Mapper for mapping purposes. Then you can map the Line Items (details) by dragging the Access database field and dropping it on the SAP field.

While recording the transaction, it is recommended to enter more than one line item in the transaction in order to create a visible pattern of repetition in the Mapper. Notice that the example shows more than one set of journal entry line items.

To use the Do While...Loop feature to handle multiline transactions

Create a Do...While Loop around one set of data only.
Disable the duplicate rows of data by selecting the Disable Row(s) flag in the left-most column of the Expert Tab: Recording Preview.
After you insert the Do...While loop, the Mapper displays the fields contained within the loop by using two (Loop icons) connected by a line.

You can select a row in the Do...While Loop and click the horizontal Properties bar on the far left to display the Properties slide-in. On the Properties slide-in, the Loop Column check box is marked for the rows mapped within the loop that contain data from records identified as Line Items (details). This setting instructs TRANSACTION to pull data from the Line Item (detail) contained in the Do...While Loop.

NOTE: The Loop Column check box must be selected for at least one of the fields. Otherwise, TRANSACTION script generates a syntax error.

Using Multiple and Nested Loops

When your task requires more than one Do...While Loop to fulfill a business process, the Mapper offers two options for using multiple loops to handle these scenarios:

NOTE: Multiple and nested Do...While Loop are not supported when Access is used as the data source.

Multiple Do...While Loop

Multiple loops are useful in transactions that have more than one set of multi-line items (grids). Each of these sets could potentially have either their own set of Header and Line Items, or they could share a common Header section, but have different Line Items, as shown below.

Nested Do...While Loop

Nested loops are useful in transactions that require data to be uploaded into multi-line items, which contain embedded line items for each item in the topmost level/outer grid. In this case, there is an outer Do...While Loop that contains a number of Do...While Loop nested within it. For example, if you are uploading data in grid A, which contains grids B and C, with each having data related to each line of A, then use a nested Do…While Loop to accomplish the upload. TRANSACTION supports nested Do...While Loops up to any level.

In this example, there are two outer level loops, namely +Do-While A, H, D and +Do-While A, H, D1. Apart from these, the first loop has a nested loop, +Do-While A, H, D2.

The format of the Excel worksheet used for uploading data is:

Rows with ID H represent the Header for all of the Do…While Loops.
Rows marked with ID D represent the Line Item data for the first outer Do…While Loop.
Rows marked D1 and D2 are the Line Items for the second outer loop and its nested Do… While Loop, respectively.
In the first H, D, D1, D2 block above, there are two Line items for grid D and two Line Items for grid D1.
There is another grid D2 for each Line Item of grid D1. The first entry of D1 has four rows of data for D2, and second entry of D1 has two rows of data for D2.

Also in this section

Editing in the Mapper

Filtering mapper settings

Limiting input fields

Mapping Google spreadsheets

Setting conditions: IF statements

Validating data

Validate by list

Compensating for missing SAP screens and fields

Undo and Undo All

Mapping long text

Converting data types to strings

Attaching documents

Auto mapping

Downloading from SAP

Editing a TRANSACTION script