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 contains ‘dataitems’. And inside them, there can be ‘columns’, ‘filters’ or other ‘dataitems’.
The ‘QueryCategory’ property allows to show the result of the query on 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 on 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 usually 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 at 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 performing 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 responsible for the totaling 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.
- DataItemLink
- DataItemTableFilter
- SqlJoinType
DataItemLink
Property for linking between dataitems. It´s the same property as when we develop report dataitems. Get more information about it here:
DataItemTableFilter
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.
Location
If we filter in Business Central the only Location for this filtering is ‘BLANCO’.
Warehouse Entry
There are no filters for this dataitem.
BinTypes
We just find two types if we filter Bin Types, ‘ALMAC.’ and ‘QC’.
Bin content
The Location Code and Bin Type Code are filtered from the previous dataitems. So, only information from them will be displayed.
Finally, by 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.
SqlJoinType
Defines how the dataitems will merge together and which datasets should be retrieved. The options are:
- CrossJoin
- FullOuterJoin
- InnerJoin
- LeftOuterJoin
- RightOuterJoin
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 illustrate 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.
Get more information about the ‘SqlJoinType’ property here.
You can learn more about Sql joins in this link.
For further understanding and practice follow the learning path from Microsoft:
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 in some more properties. They are:
- QueryType
- APIPublisher
- APIGroup
- APIVersion
- Caption
- EntityName
- EntitySetName
Example:
Get more information about the API query type here:
That´s all. Hope you find it useful.
This is really useful, thank you. You write “The query resulting dataset cannot be used directly by other objects such as pages” but what I hadn’t realised until I read this was that you can open a query simply by adding datasourcetype=Query to the URL. This will open a page called RunQueryPage.
Previously, I’d read blogs saying you should create a temporary table and then populate this using the query in a separate page, which seems like a lot of hassle when you can effectively open the query directly.
Is there a simple way of doing this directly, rather than via a List Page? It would be good to have a Cue Card drilldown that goes straight to a query, for example.
Hi! Thanks for the compliment. That´s actually a quote from Microsoft docs I didn´t specify.
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-overview
Yun has a good blog about how to use queries with a temporary table. Have you checked it? It isn´t that difficult.
https://yzhums.com/4869/
As far as I know there is no simpler way than using the ‘QueryCategory’ property. Will try to run a query from a cue, though. I didn´t think about it.
Great post!
Thanks!
I usually publish my query as a webservice and then check the odata result for a quick check on the results. No need for a page then
left, right cross is not working like SQL. This is fixed in last version?>
Hi, I´m not aware of the issue. Can you provide more information?
yea, the SQLjoinType doesn’t really seem to work. I joined Item and Itemledger Entry with leftouterjoin. It should show all Items and if matches Item Ledger Entries. But in this case in still only shows Items with available Item Ledger Entries.