D365 Business Central : Using Query to Get Distinct Values

TeddyH

12,868 Super User 2024 Season 1

Like ( 1 )

Report

When working with table data, there will be times when we need to retrieve a unique record value from the table (also called SELECT DISTINCT). Unfortunately, D365 Business Central does not provide this logic out of the box. We will need to find a way to do this ourselves.

We can use combination of looping and temporary table to find distinct values. However, I found that using Query is the easiest and give you the best performance.

Let’s take an example where we want to retrieve all unique combination of Customer and Document Type from Customer Ledger Entries and show every combinations.

First, we need to create a Query object. We need to put all unique columns into it. Let’s put Customer and Document Type as the columns. We also need to put at least one aggregate column to trigger the distinct. On this case, I will use Count column.

query 50001 "My Query"
{
    Caption = 'My Query';
    QueryType = Normal;

    elements
    {
        dataitem(CustLedgerEntry; "Cust. Ledger Entry")
        {
            column(CustomerNo; "Customer No.")
            {
            }
            column(DocumentType; "Document Type")
            {
            }
            column(Count)
            {
                Method = Count;
            }
        }
    }
}

Let’s use the Query object on the page to show the combination.

pageextension 50001 "Customer Ledger Entries" extends "Customer Ledger Entries"
{

    actions
    {
        addlast(processing)
        {
            action("Show Combination")
            {
                ApplicationArea = All;
                trigger OnAction()
                var
                    MyQuery: Query "My Query";
                    CombinationText: Text;
                begin
                    if MyQuery.Open() then begin
                        while MyQuery.Read() do
                            CombinationText += MyQuery.CustomerNo + '-' + Format(MyQuery.DocumentType) + '; ';
                        MyQuery.Close();
                    end;

                    Message(CombinationText);
                end;
            }
        }
    }
}

This is the result.