In this post you will learn how to build a simple query in Microsoft Dynamics 365 Business Central. How to create advanced queries and how to use them via AL. Also, we will take a look at API queries and how to create them.
- What Is A Query In Business Central?
- How To Create A Query?
- Simple Query Example
- Complex Query Example
- How To Use Queries By Code
- API Query Type
What Is A Query In Business Central?
A query is a specific object type that allows to create a sort of SQL query. It allows to join different tables and perform calculations. Also, they can be exposed as APIs. The main characteristic is that queries have better performance than pages.
How To Create A Query?
The simplest query example can be just a customer list.
As we can see, we have an ‘elements’ section that cotains dataitems’. And inside them there can be ‘columns’, ‘filters’ or other ‘dataitems’.
The ‘QueryCategory’ property allows to show the result of the query in a list page. We just need to write the page name as in the example above.
When we publish the .app a new option ‘Customer List’ appears in the page.
And the result is:
Simple Query Example
We have learned to create a query as if it were a page. However, the real purpose of queries is to get information that a page can´t usally show. Or it could be bad for performance if we get the information with flowfields or with other options.
One of the best ways to learn about Business Central is to look for examples in the base app. We can choose an existing query and look what´s inside.
Let´s have a look at the ‘Top 10 Customer Sales’ query.
In this query we are not using Customer as the dataitem, we are using the customer ledger entries. The properties used are:
OrderBy: it will order the query dataset depending on the sales field, in this case.
TopNumberOfRows: it will filter the rows by this number.
Filter. The filter won´t appear in the query dataset. It´s an option for developers to filter the dataset when using queries via code.
Method. We have a list of available methods for this property. It allows to perform a calculation of the column where it´s contained.
We can see that the result of this query is a customer list ordered by Sales. If not for the ‘Method = Sum’ we would have a plain list of customer ledger entries. So, the method property is the responsible for the totalling of customers.
Complex Query Example
For more complex queries we can have a look at ‘Avail Qty. (Base) In QC Bins’. This query uses four dataitems link together. They are Location, Warehouse Entry, Bin Type and Bin Content. And several properties like DataItemTableFilter and SqlJoinType.
Let´s see each property in detail.
Property for linking between dataitems. It´s the same property as when we develop report dataitems. Get more information about it here:
It filters the whole dataitem. Let´s see for this example how it´s applied.
The result of the query is the following:
How is it calculated? Let´s look at each dataitem individually.
If we filter in Business Central the only Location for this filtering is ‘BLANCO’.
There are no filters for this dataitem.
We just find two types if we filter Bin Types, ‘ALMAC.’ and ‘QC’.
The Location Code and Bin Type Code are filtered from the previous dataitems. So, only information from them will be displayed.
Finally, filtering ‘Bin Content’ with these filters we get the following result:
As the query has calculated, we can see that the Quantity(Base) for ‘ALMAC.’ adds 807. And for ‘QC’ there is only 1 unit.
Defines how the dataitems will merge together and wich datasets should be retrieved. The options are:
Except for CrossJoin, this property works together with the DataItemLink property. They combine records from tables into a single dataset.
Imagine ‘Table 1’ as the upper table and ‘Table 2’ as the lower one where we specify the DataItemLink. In our example they can be Location (Table 1) and Warehouse Entry (Table 2).
The inner join will only retrieve data that has a match between them. Where ‘Location Code’ = Location.Code. It means that the query will retrieve every record of the lower table (Bin Type) when there is a match for the DataItemLink property.
If we try a LeftOuterJoin and modify the query a little to ilustrate this option:
We get a list of all Locations (Table 1) although the DataItemLink didn´t find a record in the Warehouse Entry (Table 2). We can see that ‘Sum_Qty_Base’ is 0.
How To Use Queries By Code
When we have the query ready we can use it via code.
The query resulting dataset cannot be used directly by other objects such as pages and reports. Instead, you must create a Query Data Type instance and apply methods for handling the data from the query object.
We can apply filters to the exposed fields. The way to retrieve data is by usint a while loop. These are the steps to follow:
- Set the filters for the query
- Open the query with Query.Open
- Insert a while loop and read the data with Query.Read
- Apply the business logic inside the loop
- Close the query with Query.Close
For every loop a record is retrieved thanks to Query.Read. In this image we can see that we can apply some filtering if needed. Then, perform the while loop.
In this video test we can see the query dataset on the left and the ‘TestQuery’ function on the right.
API Query Type
We can expose queries as APIs and web services similar to what we do with pages. Just keep in mind that SOAP is not supported for queries, we have to use OData instead.
If you want to know the difference between OData and SOAP check the article below.
The way to create a query as an API we need to fill some more properties. They are:
Get more information about the API query type here:
That´s all. Hope you find it useful.