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

POSTED ON SEPTEMBER 24, 2022 BY IVANRJLG

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.

Project Overview

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.

query 52100 "Top 5 Customers"
{
    QueryType = API;
    APIPublisher = 'ivansingleton';
    APIGroup = 'app1';
    APIVersion = 'v1.0';
    EntityName = 'Top5Customers';
    EntitySetName = 'Top5Customers';
    TopNumberOfRows = 5;
    QueryCategory = 'Customer List';
    Caption = 'Top 5 Customers';
    OrderBy = descending(Total_Sales_Amount);

    elements
    {
        dataitem(Customer; Customer)
        {
            column(No; "No.")
            {
                Caption = 'No', Locked = true;
            }
            column(Name; Name)
            {
                Caption = 'Name', Locked = true;
            }
            column(Email; "E-Mail")
            {
                Caption = 'Email', Locked = true;
            }
            dataitem(Cust_Ledger_Entry; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Customer."No.";
                SqlJoinType = LeftOuterJoin;
                DataItemTableFilter = "Document Type" = FILTER(Invoice | "Credit Memo");
                column(Total_Sales_Amount; "Sales (LCY)")
                {
                    Caption = 'Total_Sales_Amount', Locked = true;
                    Method = Sum;
                }
            }
        }
    }

    trigger OnBeforeOpen()
    begin

    end;
}

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