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

Back to

Advanced mapping

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

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

D2

D2

A

B

C

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 data from loops can reside on multiple Excel data sheets.

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

Invalid mapping

To map multiple loops

  1. In the Preview pane of the Mapper screen, add as many new sheets as you need.
  2. Click the first sheet. In the Mapper, select the items for the first loop. Click the Loop button.

    The Loop Master Parent sheet is selected by default.

  3. Define all loops that are at the same level before you define any nested loops.
  4. Check the Apply Multi Sheet box.

    The Loop Master sheet and Header sheet is selected by default. Select the appropriate sheet for the line items. Set the corresponding join key columns for the header and line item sheets. Map the rest of the line items.

    When a TXR is published, the published sheet must contain the same sheet names that are in the script.

  5. If the data source is changed, the preview reverts to Untitled. Either add the sheets manually or open a sheet that contains the appropriate names.
  6. Ensure that you save the Preview before closing the application or Preview sheets could be deleted.

New sheets and single sheet mapping

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 multiline transactions using the Do...While Loop feature. The basic principles of using the Do...While Loop with Access as the data source include:

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 multi-line transactions

  1. Create a Do...While Loop around one set of data only.
  2. Disable the duplicate rows of data by selecting the Disable Row(s) flag in the left-most column of the Expert Tab: Recording Preview.
  3. 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 checked for at least one of the fields. Otherwise, TRANSACTION script generates a syntax error.

Back to top

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 loops 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 multiline 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 multiline 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:

Also in this section

Editing in the Mapper

Filtering mapper settings

Setting conditions: IF statements

Validating data

Validate by list

Compensating for missing SAP screens and fields

Undo and Undo All

Mapping long text

Attaching documents

Auto mapping

Downloading from SAP

Editing a TRANSACTION script