Share via

Working with Queries in C/AL

  • Article

After you create a query, you can use C/AL code to run the query and retrieve data from the resulting dataset. There are several functions that you can call on the query to filter the dataset, read rows, access columns, and save the dataset to a file in XML or CSV format.

Query Functions

The following table contains the C/AL functions available for running queries and accessing the resulting dataset.

Function Description

SETFILTER Function (Query)

Sets a filter on a field of a specified data column or filter row in the query.

SETRANGE Function (Query)

Sets a simple range filter on a field of a specified data column or filter row in the query.

TOPNUMBEROFROWS Function

Sets the maximum number of rows to include in the resulting dataset.

OPEN Function (Query)

Runs a query object and generates a dataset so that it can be read.

READ Function (Query)

Retrieves a row from the resulting dataset of a query.

QueryVariable.ColumnName

Retrieves the value of a column in the dataset row that is currently being read. QueryVariable is a variable of the Query data type that specifies the query object. ColumnName is the name of the column in the query.

For more information, see Accessing Columns of a Query Dataset.

GETFILTER Function (Query)

Returns the filters on a specified column in the query.

GETFILTERS Function (Query)

Returns the filters on all columns in the query.

CLEAR Function

Clears the instance of the query, including all filters.

CLOSE Function (Query)

Closes a query dataset and returns it to the initialized state. Any filters that are set by the SETFILTER or SETRANGE functions are still valid.

SAVEASCSV Function

Saves a query data set as a comma separated values file.

SAVEASXML Function

Saves a query dataset as an .xml file.

Query Triggers

Queries include an OnBeforeOpen trigger in C/AL that executes before the query is opened and the dataset is generated. For more information, see OnBeforeOpen Trigger.

Query State Diagram

The following figure illustrates the state of a query object for each of the query function calls in C/AL.

State diagram for query function calls

Note

  • The Read function can only be called when the query is in the opened state after a call to the OPEN function.

  • query. ColumnName can only be called when the query is in reading state after a call to the READ function.

  • The SAVEASCSV and SAVEASXML functions can be called from any state. If the dataset is currently open, then it will be closed. These functions implicitly call the OPEN, READ, and CLOSE functions, which pass the query through the Opened, Reading, and End Of Result Set states.