Loop Dataset Action Icon

Loop Dataset

Declaration

<AMLOOP TYPE="DATASET" DATASET="text" FROM="number" TO="number" SORT="text(options)" SORTEDCOLUMN="text">

See Also

Loop | End Loop | Loop Files | Loop Windows | Loop Processes | Loop Expression | Loop Dataset | Loop List | Loop List Control | Loop Processes | Loop Tree Control | Loop Windows | Break

Description

Loops through the rows of the dataset specified. With each successive loop a series of steps are executed and the current row of the dataset is incremented. The loop ends when it reaches the end of the dataset or when a Break is encountered.

Practical Usage

Commonly used to loop a block of steps through the records (rows) in a dataset (which is a multi-column, multi-row variable). With each loop the current record (row) in the dataset is incremented until the last record is reached, at which point the loop ends.

Parameters

General Properties

Property

Type

Required

Default

Markup

Description

Dataset Name

Text

Yes

(Empty)

DATASET="datasetname"

The name of a previously created dataset in which to loop through. Example actions that create a dataset are SQL Query, Get E-mail, Stored Procedure and Excel Cells to Dataset. More on datasets can be found below under Notes.

Start Row

Text

No

(Empty)

FROM="20"

The record (row) to start on when looping through the dataset.

End Row

Text

No

(Empty)

TO="82"

The record (row) to stop on when moving through the dataset. If omitted or set to 0, the End Row is set to the last row in the dataset.

Advanced Properties

Property

Type

Required

Default

Markup

Description

Output List

Text (options)

No

None

DATASET="datasetname"

Specifies whether a sort order should be applied to the dataset values before the loop begins. If using this action with a SQL Query action, in most cases it will be more efficient to perform the sorting as part of the query instead of using this option. If ascending or descending is specified, a valid column name to sort on must also be specified.

The available options are:

  • Do not sort the list (default).

  • Sort the list in ascending alphabetical order (A-Z).

  • Sort the list in ascending alphabetical order (Z-A).

Sort on Column

Text

No

(Empty)

SORTEDCOLUMN="LastName"

The dataset column to sort on. This parameter is active only if the Output List parameter selected is Sort the list in ascending/descending alphabetical order.

 

Description Properties

The Description tab allows you to customize the text description of any step as it appears in the Task Builder's Steps Pane.

More on setting custom step description

Error Causes Properties

The Error Causes tab properties allows you to instruct a task step to react only to specific errors or ignore certain errors that should cause it to fail.

More on Error Causes properties

On Error Properties

The On Error tab properties lets you determine what the task should do if a particular step encounters an error as defined in the Error Causes properties.

More about On Error properties

Notes

Datasets

A dataset is a collection of data, usually structured to include multiple columns and multiple rows. Each column represents a particular variable. Each row corresponds to a given member of the dataset in question. The data in a dataset is laid out like a table and is accessed in much the same way (i.e. by specifying the column and row where the data resides). When using datasets, the column name must be specified using the following format:

 

 

%datasetname.columnname%.

 

 

Many AutoMate actions create and populate a dataset, such as SQL Query, Stored Procedure, Get E-Mail, Loop Process, Get Process and Twitter actions (to name a few). The fields contained within the dataset are determined by the action that was executed.

SQL Query Example

For example if the following query is executed by a SQL Query action.

 

SELECT firstname, lastname, company from customer where city='Los Angeles'

 

 

Then the following dataset would be generated:

 

%datasetname.firstname%

 

%datasetname.lastname%

 

%datasetname.company% 

 

 

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

 

%mydatasetname.firstname%

 

 

Or you could combine two fields together like this:

 

%mydatasetname.firstname + " " + mydatasetname.lastname%

 

 

Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:

 

<AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

 

 

At runtime the text %mydatasetname.firstname% is replaced by the contents of the subject of the current record.

Variables and Expressions

All text fields allow the use of expressions, which can be entered by surrounding the expression in percentage signs (example: %MYVARIABLE%, %Left('Text',2)%). To help construct these expressions, you can open Expression Builder from these fields by pressing F2.

More on variables
More on expressions

More on the expression builder

Examples

NOTE: The code below can be copied and pasted directly into the Steps pane of the Task Builder.

 

Description: This sample task will use the appropriate 'Excel' actions in AutoMate to grab data from the first few rows and columns of an Excel Document (rows 1 to 5 and columns A to C) and populate a dataset with the data. It will then loop through the rows of the dataset using the Loop Dataset action and show the data from each column in a message box. To ensure that this task works, select an Excel Document that includes some data from rows 1 to 5 and Column A to C when prompted.

 

<AMVARIABLE NAME="theExcelDoc"></AMVARIABLE>

<AMVARIABLE NAME="theCurrentRow">0</AMVARIABLE>

<AMSHOWDIALOG ACTIVITY="folder" WINDOWTITLE="&quot;Open An Excel Document that includes some data from rows 1 to 5 and Column A to C. &quot;" RESULTVARIABLE="theExcelDoc" />

<AMEXCELOPENWORKBOOK WORKBOOK="%theExcelDoc%" />

<AMEXCELCELLSTODATASET RESULTDATASET="theDataset" STARTCELLREF="A1" ENDCELLREF="C5" />

<AMLOOP TYPE="DATASET" DATASET="theDataset">

     <AMSHOWDIALOG>The Data in Row %theCurrentRow%, Column A is: %theDataset.A%

The Data in Row %theCurrentRow%, Column B is: %theDataset.B%

The Data in Row %theCurrentRow%, Column C is: %theDataset.C%</AMSHOWDIALOG>

     <AMINCREMENTVARIABLE RESULTVARIABLE="theCurrentRow" />

</AMLOOP>

 

↑ Top of Page