About Datasets

Like variables, datasets are used to represent data in a task that may be different each time a task runs. But unlike standard variables, datasets can contain multiple rows and columns. This is useful when retrieving data that describes an object or objects, or that represents a collection of data such as a database or spreadsheet.

Conceptually, 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). Several actions in AutoMate create and populate datasets, including SQL Query, SNMP Get, Get Email actions, and many others.

Using Datasets

Every dataset created and used in AutoMate must have a unique name, much like variables. But because datasets are more like tables (as opposed to variables, which can be thought of more like containers that hold one value), they are referenced differently than other AutoMate objects. When using datasets, the column name must be specified using the format: %datasetname.columnname%. Typically, one references a value of the current row at a given column (i.e. a column named "address") using an AutoMate expression:

%MyDataset.Address%

When a dataset is created, the current row is automatically set to 1 (assuming that the dataset has any data, since it is possible for a dataset to have 0 rows, such as when a SQL Query returns no data). A dataset is of minimal use, however, unless one can access the other rows. Typically this is accomplished by using the Loop Dataset action.

The Loop Dataset action takes a dataset name as a parameter and automatically increments the current row with each iteration. The loop continues until all the rows have been accessed. In this way, one could make an AutoMate task that performs operations on each row of the dataset while using the same expression.

Using a 'Loop Dataset' step is not the only way to access dataset rows, however. It is possible to directly access a particular row of a dataset by supplying the row number within the expression. For example, if the dataset contains five rows and you need to get the address of row 2, this expression would do the trick:

%MyDataset(2).Address%

In fact, AutoMate allows the previous expression (i.e. %MyDataset.Address%) by simply assuming you mean the current row if an index is not specified.

Dataset Fields

Most of the columns that are returned in a dataset are dictated by the action creating and populating the dataset. For example, when using the SQL Query action, the column names are controlled by the columns returned by the query. In the SNMP Get action, the column names are related to the SNMP information being returned. However, all datasets have a common set of columns that can be used to work with the dataset. These columns can be accessed the same way as other columns, though some of their values are read only. Also, the values of these columns are the same regardless of the row being accessed.

Common Name Description

The table below describes the common set of columns that a dataset creates (assuming the dataset name assigned was theDataset).

Name

Data Type

Return Value

theDataset.CurrentRow

Number

The current row that will be accessed in the dataset by an expression that does not contain a specific row index.

theDataset.TotalRows

Number

The total number of rows in the dataset

theDataset.TotalColumns

Number

The total number of columns (not including the static columns) in the dataset.

theDataset.ExecutionDate
 

Date

The date and time the dataset was created and populated

theDataset.RowsAffected
 

Number

The number of rows affected by an update.

theDataset.SQLQuery
 

Text

The SQL Query that was used to generate this dataset (If a SQL Query was not used, this value is empty).

theDataset.Datasource
 

Text

The datasource used for the SQL Query, if applicable.

theDataset.ColumnNames
 

Text

A comma-delimited list of the column names in the dataset

 

Example using SQL Query action

The SQL Query action is an example of an action that creates and populates a dataset. The fields contained within that dataset are determined by the query that was executed. For example if the following query is executed:

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

Then the following data set would be generated:

datasetname
|--firstname
|--lastname
|--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%

Expressions such as these can be used in any parameter in any action. The AML code to display the data in a message box 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.

The percent signs (%) at the beginning and end of the variable name tell AutoMateā„¢ that the text in-between the percent signs is an expression and should not be taken literally. Instead, it is replaced with the current contents of that column in the current row at runtime.

Example

(To use these examples, copy and paste the text below into 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>

See Also

SQL Query

Get Email

Variables

Constants