Loop
- Dataset
Declaration
<AMLOOP ACTIVITY="dataset" DATASET="text" FROM="number" TO="number" SORT="text (options)" SORTEDCOLUMN="number" /><AMLOOP ACTIVITY="end" />
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
| Property | Type | Required | Default | Markup | Description |
|---|---|---|---|---|---|
| Dataset | Text | Yes | (Empty) | DATASET="datasetname" | The name of the dataset in which to loop through. Information about the dataset this activity creates can be found below under Datasets. |
| Start loop on row | Text | No | (Empty) | FROM="20" | If enabled, specifies the record (row) that this activity should start on when looping through the dataset. All rows that exist before the specified row will be skipped. This parameter is disabled by default. |
| End loop on or before row | Text | No | (Empty) | TO="82" | If enabled, specifies the record (row) that this activity should stop on when moving through the dataset. If disabled, the end Row is set to the last row in the dataset. This parameter is disabled by default. |
Advanced
| Property | Type | Required | Default | Markup | Description |
|---|---|---|---|---|---|
| Loop order | Text (options) | No | Unsorted |
| The sort order that should be applied to the dataset values before the loop begins. If using this activity with a SQL Query activity, 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:
|
| Ordering column | Number | No | (Empty) | SORTEDCOLUMN="LastName" | The dataset column to sort on. This parameter is active only if the Loop order parameter is set to Ascending alphabetical order or Descending alphabetical order. |
Additional 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 (that is, 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 Desktop actions create and populate a dataset, such as Database - SQL query, Database - Stored procedure, Email - Get message, Loop - Processes, Processes - Get information and Twitter activities, just 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">
Example
- Copy and paste the sample AML code below directly into the Task Builder Steps Panel.
- To successfully run the sample code, update parameters containing user credentials, files, file paths, or other information specific to the task to match your environment.
Description
This sample task will use the appropriate 'Excel' actions in Automate Desktop 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" VALUE="" />
<AMVARIABLE NAME="theCurrentRow" VALUE="0"></AMVARIABLE>
<AMSHOWDIALOG ACTIVITY="folder" WINDOWTITLE=""Open An Excel Document that includes some data from rows 1 to 5 and Column A to C. "" RESULTVARIABLE="theExcelDoc" />
<AMEXCEL SESSION="ExcelSession1" WORKBOOK="%theExcelDoc%" />
<AMEXCEL ACTIVITY="get_cell" SESSION="ExcelSession1" ACTION="rangebyreference" RESULTDATASET="theDataset" STARTCELLREF="A1" ENDCELLREF="C5" />
<AMLOOP ACTIVITY="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>
<AMVARIABLE ACTIVITY="increment" RESULTVARIABLE="theCurrentRow" />
<AMLOOP ACTIVITY="end" />