Loop Dataset Action

Declaration

<AMLOOPDATASET DATASET="text" FROM="number" TO="number" SORT="text [options]" SORTEDCOLUMN="text [columnname]">
  (block of steps to loop)
</AMLOOP>

See Also

Loop, End Loop, Loop Files, Loop Windows, Loop Processes, Loop Expression, Loop List, Break

Description

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

Practical Usage

To loop a block of steps through the records (rows) in a dataset (which is a multicolumn, multirow variable). With each loop the current record (row) in the dataset is incremented, until the last record is reached at which point the loop is ended.

Parameters

General Tab

Dataset Name

Text, Required
MARKUP: DATASET="DATASETNAME"

Specifies the name of a previously created dataset. Example actions that create datasets are SQL Query, POP3 Mail, and Stored Procedure.

Start Row

Number, Optional Default - 0
MARKUP: FROM="20"

Specifies the record (row) to start on when moving through the dataset.

End Row

Number, Optional - Default 0
MARKUP: STEP="2"

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

Advanced Tab

Output List

Text [options], Optional (default "none")
MARKUP: SORT="ascending"

Specifies whether a sort order should be applied to the values before the loop begins. If using Loop Dataset with a SQL Query, 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 specified, a valid column name to sort on must also be specified.

The available options are:

Sort on Column

Text, Required if SORT is ascending or descending
MARKUP: SORTEDCOLUMN="LastName"

Specifies the column to sort on if Output List [SORT=] is set to ascending or descending.  Must specify a valid field/column name.

Notes

Custom Description

This action includes the Description tab for entering a custom step description.

More on setting custom step description

Standard Error Handling Options

This action also includes the standard Error Causes and On Error failure handling options/tabs.

More on Error Handling Options

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.

<!--- Demonstrates using SQL query with Loop Dataset. Gets all customer where city field is San Diego. Then writes names to a file, to make this task work change the SQL Query connection string and query to match your database--->
<AMSQLQUERY CONNECTIONSTRING="19y9I4LjuL6U1z5kWV9qNz7f2GrLdS16HzZ2IABihh5oydIvZvKxirNVsXfr02s5+YSmraH478OnG3fyNDrnr4k2lmJKCFqoQ/poQgCblLO4gyfnpETTF3SS5ARqugxsajhx7hYL0OypO7YoBsI0EMMvOE56AM64O" RESULTDATASET="myresults">SELECT firstname, lastname from customer where city='San Diego';</AMSQLQUERY>
<AMMESSAGEBOX>%myresults.TotalRows% customers found</AMMESSAGEBOX>
<AMLOOPDATASET DATASET="myresults">
<AMFILEWRITE FILE="c:\customerlist.txt">%myresults.firstname% %myresults.lastname%</AMFILEWRITE>
</AMLOOP>

<!--- This example checks the pop3 mailbox and if a message contains the subject 'Get out of Debt' then it is deleted. To make this task work - modify both POP3 steps with the proper server and account information --->
<AMPOP3MAIL RESULTDATASET="themessages" SERVER="mail.server.com" USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
<AMLOOPDATASET DATASET="themessages">
<AMIFCONTAINS TEXT="%themessages.Subject%" SUBSTRING="get out of debt">
   <AMMESSAGEBOX>Deleting message with %themessages.subject%</AMMESSAGEBOX>
   <AMPOP3MAIL ACTION="remove_message" MESSAGE="%themessages.currentrow%" RESULTDATASET="themessages" SERVER="mail.server.com" USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
</AMIF>
</AMLOOP>