Loop Dataset Action
DATASET="text" FROM="number" TO="number"
SORT="text [options]" SORTEDCOLUMN="text [columnname]">
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.
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.
Specifies the name of a previously created dataset. Example actions that create datasets are SQL Query, POP3 Mail, and Stored Procedure.
Specifies the record (row) to start on when moving through the dataset.
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.
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:
none: The results are left in their natural order and not sorted.
ascending: The results sorted in ascending alphabetical order.
descending: The results sorted in descending alphabetical order.
Specifies the column to sort on if Output List [SORT=] is set to ascending or descending. Must specify a valid field/column name.
This action includes the Description tab for entering a custom step description.
More on setting custom step description
This action also includes the standard Error Causes and On Error failure handling options/tabs.
More on Error Handling Options
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
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>
<AMFILEWRITE FILE="c:\customerlist.txt">%myresults.firstname% %myresults.lastname%</AMFILEWRITE>
<!--- 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
<AMPOP3MAIL RESULTDATASET="themessages" SERVER="mail.server.com" USERNAME="username" PASSWORD="1Zb00y6hQv2ZqWN7qDphOEw==">
<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==">