Access queries from AL
Queries can be an effective replacement for iterative access to data in Business Central, especially when you use multiple nested data iterations in AL code. A Query object includes a set of AL functions that you can use to access data, filter the resulting dataset of a query, or export the resulting dataset to the CSV or XML format by using streams.
You can run a query AL to iterate through the resulting dataset programmatically. Though the principles of accessing a query resemble those of accessing tables, a different set of functions exists on a query object.
Open function
The Open function runs a query object and generates a dataset that you can read. It also puts the query in the reading state.
The Open function returns a Boolean value, which indicates whether the query opened successfully or not. If you omit this optional return value, and if the query doesn't open successfully, then a run-time error occurs. If you include a return value, then no run-time error occurs when the function is called, and you'll handle any errors.
If the Open function fails, you can't call other functions or access the data in the query. If you try to complete this action, a run-time error occurs. The Open function only runs the query object and generates a dataset; it doesn't return the first row of the result set. To access any row, you must call the Read function.
Read function
The Read function reads a single row from the resulting dataset of a query. The function returns a Boolean value that indicates if a row was retrieved. When you call the Read function, the next row in the dataset query is retrieved.
When the query is in the reading state, you can access the values of columns in the row in the same manner that you access the fields in a record variable.
You can call the Read function multiple times after the Open function to read consecutive rows in the dataset. The first Read function call retrieves the first row from the resulting dataset. Each successive Read function call retrieves the next row from the dataset.
Close function
The Close function closes a query dataset and returns the query to the initialized state. It lets the Business Central server know that you are finished using this object.
Calling the Close function explicitly is optional. This function is called implicitly in any of the following situations:
When the query variable goes out of scope
If you call the Open function on a query variable that is currently open
If you call the SetFilter or SetRange functions on a query variable that is currently open
The Close function does not clear any filters that you set on the query programmatically. If you want to clear such filters, then you must call the Clear function.
begin
if SimpleItemQuery.Open() then begin
while SimpleItemQuery.Read() do begin
// Enter some logic
end;
SimpleItemQuery.Close();
end;
end;
Column access
On a query variable, you can access the columns of a query in a manner similar to accessing the fields of a table on a record variable. When you read values from columns in AL, you can reference columns exactly as you would reference record fields.
The following example shows how to read a value from the Price column of the Simple Item Query (accessing a column of a query from AL).
begin
if SimpleItemQuery.Open() then begin
while SimpleItemQuery.Read() do begin
ItemPrice := SimpleItemQuery.Price;
// Do some logic
end;
SimpleItemQuery.Close();
end;
end;
Programmatically, you can only access those columns that have been defined in the query but not any other fields that exist in the tables from which the query is constructed.
Filter queries
You can filter the data in queries to narrow the resulting datasets. You can only filter a query on a field that is included as a column or a filter in the query. You can use the SetRange and SetFilter functions to set filters on a query variable.
When you use the SetFilter and SetRange functions to define a filter on the same field that is already filtered through the ColumnFilter property in Query Designer, then the filter that is defined in the ColumnFilter property is replaced by the filter that you set in AL code.
If you use SetFilter or SetRange functions on the same field that is included in the DataItemTableFilter property of a data item, then the function filter and DataItemTableFilter property filter are combined.
The following example shows how to use the SetFilter and SetRange functions to filter the resulting dataset of a query.
PendingProdOrders.SetRange(Status, 1, 3);
PendingProdOrders.SetFilter("Due_Date", '>0D|<%1', WorkDate());
if PendingProdOrders.Open() then
while PendingProdOrders.Read() do begin
Item.Get(PendingProdOrders."Item_No");
Item.TestField(Blocked, false);
end;
PendingProdOrders.Close();
Because no data is retrieved before the query is open, referencing columns in any way, including for specifying values of filtering functions, isn't allowed. Therefore, in this example, the SetRange function is called on the Status column by providing the integer instead of option values. If you specify the range by using option values before the query is open, a run-time error occurs.
Calling SetRange or SetFilter functions on a query that is already open will automatically close the query. To access data from such a query, you must make sure that you call Open before you call Read. The best practice is to set filters before the first call to Open and to close the query by calling Close immediately after all the rows are read.
You can have multiple calls to the SetFilter function. If the SetFilter function calls set filters on different columns, then the filters are combined and applied to the dataset. If consecutive SetFilter function calls set filters on the same column, then the last SetFilter function call is applied to the column.
TopNumberOfRows function
When you design a query, you can set the limit for the number of rows that the query returns by specifying its TopNumberOfRows property in the Query Designer window. At run time, you can check or change the value of this property by using the TopNumberOfRows function. If you limit the number of rows by defining the TopNumberOfRows property, the TopNumberOfRows function overwrites the TopNumberOfRows property.
If the value of the TopNumberOfRows property is undefined, the TopNumberOfRows function returns zero (0). If you programmatically set TopNumberOfRows to zero (0), all rows are returned.
The following code shows how to programmatically limit the number of rows that are returned from a query to 10 rows.
SimpleItemQuery.TopNumberOfRows := 10;
You can call filtering and the TopNumberOfRows functions on the CurrQuery variable from the OnBeforeOpen trigger in the query object. The CurrQuery variable is implicit in the AL code of the query object; you don't have to reference it directly.
SaveAsXml and SaveAsCsv functions
You can save the resulting dataset of the query to an external file from AL. You can use the SaveAsCsv function to save the results to a comma-separated values (CSV) file and use the SaveAsXml function to save the results to an XML file.
You can always call SaveAsCsv and SaveAsXml functions directly without calling the Open, Read, or Close functions first. When SaveAsCsv or SaveAsXml functions are called, the query is implicitly opened, read, and then closed. If you call SaveAsCsv or SaveAsXml functions on a query that is already opened, then the dataset is first retrieved again from the Business Central database. After the dataset is saved to the file, the query is left in the closed state, which makes any later call to the Read function invalid. You must reopen the query to continue reading the data.
A best practice to follow is to always call SaveAsCsv or SaveAsXml functions on separate variables. Never call them on variables that are used for iterating through the result set.
Both SaveAsCsv and SaveAsXml functions return a Boolean value that indicates whether the query was successfully saved or not. If you omit this optional return value and the query wasn't successfully saved, a run-time error will occur.
The following example shows how to export a top number of rows in the resulting dataset of a query to an XML file. You have to use streams to save the file because, in an SaaS environment, you don't have access to the file system.
procedure GetTop10ProdOrdersXml()
var
Top10ProdOrders: Query "Top-10 Prod. Orders - by Cost";
TempBlob: Codeunit "Temp Blob";
FileNotSavedMsg: Label 'The file was not saved. The problem was %1';
OutStr: OutStream;
InStr: InStream;
FileName: Text;
begin
TempBlob.CreateOutStream(OutStr);
Top10ProdOrders.TopNumberOfRows(5);
if not Top10ProdOrders.SaveAsXml(OutStr) then
Error(FileNotSavedMsg, GetLastErrorText());
TempBlob.CreateInStream(InStr);
FileName := 'top_10_prod_orders.xml';
File.DownloadFromStream(InStr, 'Top 10 Prod. Orders XML', '', '', FileName);
end;
Unlike the XML files that are exported from XMLports, the structure of the XML files that are created by the SaveAsXml function always follows the same fixed structure. The data in the resulting XML document doesn't belong to a namespace, the root element is always <DataSet>, and each row is represented as a <Result> element. Columns are represented as child elements of the <Result> element. The name of each element that represents a column is equal to the name of the column as specified in its Name property. The XSD schema that describes the format of the resulting XML file is embedded in the file.
A CSV file stores the data in a plain text format. The files that are created through the SaveAsCsv function resemble the variable text format files that are exported from an XMLport. Each row of data in a CSV file resides in a separate line. The first line of a CSV file that is created through the SaveAsCsv function always contains the column names of the query. The column names are specified in the Name property of each column in the definition of the query.
CSV files follow a fixed set of rules that simplify your ability to import them in other applications such as Microsoft Excel.