Working with C/AL Queries in Dynamics NAV
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.
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.