D365 BC – Joins in Query Objects [Practical Example]

In this Article we are going to Join 3 Tables with each other and will explain the Code Step-By-Step.

This article is linked with my previous article. To clear your concepts of Types of Join. Read my Previous Article. [Link]

Final Code

The Final Code is Present below.

query 60147 "Custom Sales Document"
{
    QueryType = Normal;
    OrderBy = descending(Quantity);

    elements
    {
        dataitem(Customer; Customer)
        {
            column(No_; "No.") { }
            column(Name; Name) { }

            dataitem(Sale_Line_Document; "Sales Line")
            {
                DataItemLink = "Sell-to Customer No." = Customer."No.";
                column(Quantity; Quantity) { }
            }
        }
    }

    var
        myInt: Integer;

    trigger OnBeforeOpen()
    begin
        // Code for initialization or setup can be added here if needed
    end;
}

Now we are going to explain this Code and then will show the result. To start the query we are going to use the Snippet. For that write the “tquery”.

1. Query Declaration

query 60147 "Custom Sales Document"

• This line defines a query object named “Custom Sales Document” with the ID 60147. Queries in Business Central are used to retrieve data from the database in a structured format.

2. Query Properties

{ 
QueryType = Normal; 
OrderBy = descending(Quantity);
}

• QueryType = Normal;: This sets the query type to “Normal,” which is the standard type for retrieving data without any special characteristics.
• OrderBy = descending(Quantity);: This orders the results based on the Quantity column in descending order, meaning it will list results from the largest to the smallest quantity.

3. Elements Section

 elements 
     { 
           dataitem(Customer; Customer) 
           { 
                      column(No_; "No.") { } 
                      column(Name; Name) { }
           }
     }

• elements: This section defines the structure of the query.
• dataitem(Customer; Customer): Defines a data item based on the Customer table. A data item acts as a source of data, similar to a table or view.
• column(No_; “No.”) { }: Adds a column to the query output named No_, which corresponds to the “No.” field in the Customer table.
• column(Name; Name) { }: Adds another column named Name, which corresponds to the Name field in the Customer table.

4. Nested Data Item

 dataitem(Sale_Line_Document; "Sales Line") 
      { 
            DataItemLink = "Sell-to Customer No." = Customer."No."; 
            column(Quantity; Quantity) { }
       }

• dataitem(Sale_Line_Document; “Sales Line”): Defines a nested data item based on the Sales Line table. This is used to retrieve related sales line data for each customer.
• DataItemLink = “Sell-to Customer No.” = Customer.”No.”;: Links the Sales Line data item to the Customer data item. It matches the “Sell-to Customer No.” field in the Sales Line table with the “No.” field in the Customer table. This means it only includes sales lines that correspond to the current customer.
• column(Quantity; Quantity) { }: Adds a column named Quantity, corresponding to the Quantity field in the Sales Line table, to the query output.

Results

In Business Central, the new query retrieves the Customer No. and Name columns from the Customer table. It then examines the Sales Line table, specifically the Sell-to Customer No., to match each customer. The query pulls the corresponding Quantity from the Sales Line table and displays it in descending order. This approach allows for an organized view of sales data per customer, sorted by quantity.

Summary

• The query retrieves customer data (No. and Name) and their associated sales line quantities.
• The results are ordered by Quantity in descending order.
• The Sales Line is linked to the Customer table using the “Sell-to Customer No.” field.

This setup is useful for analyzing sales data per customer, providing insights into the quantities ordered.

I hope you find this article helpful.

Best Regards,

Ahmad Subhani

D365 Business Central Functional Consultant

MB-800 Certified

Email: ahmadsubhani424@gmail.com

WhatsApp: +923357687164