Datasets

What are Datasets?

In the simplest terms, an Automate dataset is any named group of records. 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 a collection of data, such as a database or spreadsheet, or retrieving information that describes one or more objects or items. Datasets can hold information such as medical or financial records. Datasets are also used to store information returned by applications or the operating system itself. For example, Automate returns information about task errors (that is, error name, number, description) by way of an AMError dataset which contains a single row of data. Datasets can be cataloged, which permits them to be referred to by name without specifying where they are stored.

The data in a dataset is laid out like a database table, which has a unique name and consists of columns and rows. The columns consist of pre-defined units of data, such as one item in a database or personnel data about one member of a customer list. The rows contain the actual data for the columns. An example of a simple dataset containing customer data is illustrated below. The name of the dataset is Customers. The first row (in bold) contains the unique names of the dataset columns, which in this case, describes the data type. All other rows include the actual data as described by each column.  

Table: Customers

First	Last	Email	Phone
John	Doe	John.Brown@email.com	626 555-0150
Steven	Goldfish	goldfish@fishhere.net	323 555-0123
Paula	Smith	ps@mycompany.org	416 555-0199
James	Jones	jim@supergig.com	416 555-0198

Using Datasets

Datasets are accessed in the same way that one would access information in a database, by specifying the column and row where the data resides. 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 unique name of the dataset must be referenced, followed by the column name enclosed in percentage signs. For instance:

%DatasetName.ColumnName%

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 which 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 the table above as an example, one can retrieve the email address of each customer using the following expression inside a Loop Dataset action:  

%Customers.Email%

Using a Loop Dataset step is not the only way to access dataset rows. 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 data in row 2, simply enter the row number enclosed in parenthesis directly after the dataset name. For example:

%DatasetName(2).ColumnName%

Again, using the table illustrated above, if you want to retrieve the phone number specified in row 3 (in this case, Paula Smith's phone number), the following expression will do the trick:

%Customers(3).Phone%

Common Actions that Use Datasets

Several actions and activities in Automate create and populate datasets. The table below describes some of those actions.

Action Name	Description
SQL Query	Queries a database and populates a dataset with the data retrieved.
Get Email	Retrieves one or more messages from the specified server and populates a dataset with the results.
SNMP Get	Populates a dataset with the data sent by the agent.
Twitter - Get Timeline	Returns the 20 most recent statuses posted and populates a dataset with the results.
Web Browser - Extract Table	Extracts the contents of a given table in an established web browser session and places the results in a dataset.

Pre-Named Datasets

In addition to datasets that are created and populated during the execution of an action or activity, a collection of pre-named (or fixed field) datasets are also available which can provide more insight about the behavior of a running task, determine system states or examine other elements, such as triggers used to fire off a task or errors that were generated by a task. The table below describes some of these types of datasets.

Dataset Name	Description
AMTrigger	Returns information about a task that was started by a trigger. This dataset can get information such as the name of the object that triggered the task, the date and time when the trigger occurred, which machine the trigger occurred on, whether that machine was currently in a locked or logged off state when the task executed, and much more.
AMError	Determine specific characteristics about the task error that occurred, including error code or number, error description, action/activity that generated the error, and other values.

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,

Then the following dataset 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 theLoop Datasetaction 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 + ' ' +

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

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.

Common Dataset Fields

Most of the fields (columns) that are returned in a dataset are dictated by the action of creating and populating the dataset. For example, when using the SQL Query action, the field names are controlled by the columns returned by the query. In the SNMP Get action, the field names are dependant on the SNMP query data being returned by the agent. Nonetheless, a common set of fields are available which can be used to retrieve information about any created dataset. Most of these fields are global, which means they can be used on any dataset, regardless of which action or activity the dataset originated from. These fields can be accessed in the same manner as other fields, though some of their values are read only. Also, the values of these fields are the same regardless of the row being accessed.

The table below describes the common set of fields (columns) that a dataset creates (assuming the dataset name assigned is 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 data source used for the SQL Query, if applicable.
theDataset.ColumnNames	Text	A comma-delimited list of the column names in the dataset