How to use the Query object as an API in Business Central

In the next post, I want to mention a way to expose data using Queries.

Queries are a compelling object type within Business Central, as they allow us to retrieve records from one or more tables and then combine them in rows and columns into a single dataset.

This object type also allows you to perform calculations such as limiting the number of rows to retrieve, sorting data, and finding the sum or average of all the values in a specific column.

There are 2 types of Query:

The Query type API, is essentially the same as the Normal type, what changes are the properties that convert it into an API type, these properties are very similar to the Page type API, they are used to generate web service endpoints. This last one is the one that is going to be deepened in this Post.

Business Central

Next, I will show the code of a simple example of an API type Query.

The first step will be to use the tquery snippet: API type query to get the correct template, the following image shows the use of the command:

Once we have the structure of the Query we must add the information that will convert it into an API type.

 QueryType = API;
 APIPublisher = 'ivansingleton';
 APIGroup = 'app1';
 APIVersion = 'v1.0';
 EntityName = 'Top5Customers';
 EntitySetName = 'Top5Customers';

Now, I show what this complete example will look like. It is about calculating the 5 clients with the highest amount in sales. For this, the DataItem Customer linked to the DataItem Customer Ledger Entries was used.

Bonus:

In view, using a relatively recent feature in Business Central called QueryCategory, in my case set QueryCategory = 'Customer List', gives us the opportunity to view the data as a page through Smart List in the list of customers .

To see the Query as a page, we click on SmartList and then on Top 5 Customers

Our Query looks like this:

Using API Query in Power BI

Once we have published our API Query, it is very easy to perform the query in Power BI.

The steps are the following:

Click Get Data
Click on More..

Click on Online Services
Click on Dynamics 365 Business Central

Click on the Environment and then on Advance APIs
We look for the APIPublisher, in my case IvanSingleton and then we click on it to get the objects published there.

Now, we look for the EntitySetName, in this case I name it Top5Customers and we click there.
Finally, we click on Load

And we can now make any report in Power BI, the interesting thing is that the data is already filtered from Business Central.

Using API Query in Postman

In order to make calls to our API Query, from postman or any other web service, mobile application, azure functions, etc., we must first configure Outh2 authentication.

Through the following steps we can obtain the data from our Query API in Postman:

Step 1: Correctly configure the Oauth2. For this I will leave the link from a previous Post, whereI have explained in detail how to successfully achieve authentication with Outh2 in Business Central services.

To connect correctly through postman, we must have all these variables configured:

Step 2: Now, we will use the following template to build our URL and be able to connect to our query API.

Starting from the information previously configured in our query:

The URL template would look like this:

URL = https://api.businesscentral.dynamics.com/v2.0/ + /Environment_Name + /api + /APIPublisher + /APIGroup + /APIVersion + /companies(aaaaaaaca-bbbb-ccc-dddd-000eeeeeee7) + /EntityName

The Final URL would look like this:

URL: https://api.businesscentral.dynamics.com/v2.0/sandbox/api/ivansingleton/app1/v1.0/companies(aaaaaaaca-bbbb-ccc-dddd-000eeeeeee7)/Top5Customers

Step 3: With the variables configured, and the URL completed, it only remains to make the request in postman and we would have something similar to the following image:

Using API Query in Excel

To make the call in Excel, it is very similar to how it was done in Power BI, except that we have to know the URL, and it is exactly the same as what we use in Postman.

So the steps would be as follows:

Step 1: Click on Get Data
Step 2: Click on From Online Services and then on From Online Services
Step 3: Click on From Dynamics 365 (Online)

Step 4: We use the URL generated in the Postman section.

URL: https://api.businesscentral.dynamics.com/v2.0/sandbox/api/ivansingleton/app1/v1.0/companies(aaaaaaaca-bbbb-ccc-dddd-000eeeeeee7)/Top5Customers

Note: In the previous step, similar to PowerBI, you may be asked for the Business Central username and password. If this is the case, it is necessary to enter our credentials in order to authenticate ourselves.

Step 5:The following window will appear, which we will click on Load.

Step 6: Finally we will have our data loaded into Excel.

Conclusion

The Query object allows you to define relational data models that translate into efficient SELECT statements.

Data is selected, joined, grouped, sorted, aggregated, and filtered at the SQL Server level, ensuring minimal effect on performance, ensuring minimal pressure on system resources.

In Query, a query can be used to export data to an XML or CSV file, but can also be used as a data source for APIs and other clients, such as Power BI, Excel, or other applications in Microsoft Power Platform.

The advantages of using queries:

Limit the number of rows to retrieve. (Such as the example here published)
Join tables with different linking criteria and select subsets of fields from multiple tables. (Such as the example here published)
Filter tables by specifying filtering criteria, sort data.
Group and aggregate data.

For more information on how to learn how to use the Business Central query object, I leave the official documentation.

Code on GitHub

All the code used in this post can be found at the following link:

Code

I hope this has been helpful.

Post Views: 5,842