Add a loop in Access - Automate_Studio - Latest

Automate Studio with Studio Administrator User Guide

Product type
Product family
Automate > Automate Studio
Product name
Automate Studio
Automate Studio with Studio Administrator User Guide
Topic type
How Do I
First publish date

When you set up loops for an Access data file, do the following:

  • Place all header data in one (primary) table.
  • Place all line item data related to the header in a separate table within the same database. Select the line item table when you add the loop in the Mapper.
  • Be sure that the header and line-item tables have a common field so that you can link the line items to the header, and that the values for the common field are the same in each table.

After the tables are joined by the common field, you can map the line item fields by dragging the field from the Mapper to the Data Set panel and then changing the mapping direction to Upload to SAP.

Add the loop

Before you start, make sure that Allow Validation is not turned on. If the button background is orange, click the button to turn it off.

  1. Click the Map tab, click the Expert View tab, and in the Data Set panel, click Add Table.

    add table command

  2. Type a name for the table—for example, Table2—and then click OK.

    This table will contain the line-item data. The existing table, Table1, will contain the header data.

    To rename a table, right-click the table, and then click Rename. After you save the database file, you cannot rename the tables.

  3. Click the Table1 tab, and then click Add Column. This will be the join column that associates the data in the two tables.

    add column command

  4. In the Name box, you can type the name that you want for the column. The default name is Field.
  5. Click the arrow in the Map to box, click None, and then click OK.
  6. Click the tab for Table2, click Add Column, give this join column the same name, click None in the Map to box, and then click OK.
  7. In the Mapper, select the screens and fields that include the line-item information, and then click Create Loop.

    create loop command

  8. Click the Master table box, and click Table1.
  9. Click the Loop identifier column box, and click the join column.
  10. Click the Line item table box, and click the name of the second table, then click the Loop identifier column box and click the join column.

    loop dialog box

    The same join column name now appears in the Loop identifier column boxes for the Master table and the Line item table.

  11. Click OK.

Map the fields

  1. On the Table1 tab, drag the first header field to the table, and then change the mapping direction to Upload to SAP. Repeat for any additional header fields.

    Drag the field to the second row of the table.

  2. Drag the RUN LOG field to the table.
  3. Click the Table2.
  4. Drag the field that was mapped to None.
  5. Drag the first line item field to the table, then change the mapping direction to Upload to SAP. Repeat for any additional line item fields.
  6. Click the File tab, click Save, and save both files.

Enter the data

  1. Click the Map tab, and then click Start Access and enter the data in the Access database file.
  2. In the Table1 join column, enter a unique identifier for each header row. For example, type 1 for the first row, and 2 for the second row.
  3. In the Table2 join column, enter the corresponding identifier for each of the line items. For example, type 1 in each line item row that is associated with the first header in Table1.
  4. After you enter the data, save the data file and close it.
  5. In Studio, in the Data Set panel, click the folder icon and open the data file.

Change the loop

  • Click the Loop While or End Loop label to reopen the Loop box.

Cut, copy, and paste the loop

Select and copy the complete loop block, pasting it elsewhere in the Mapper.

Delete the loop

  1. In the Mapper, click the start row or end row of the loop to select the row.
  2. Right-click the row, and then click Delete.