How to Retrieve Distinct Values and Their Counts in Business Central

When working with Microsoft Dynamics 365 Business Central, there may be times when you need to retrieve distinct values from a table along with the count of each distinct value's occurrences. This can be especially useful for reporting and data analysis purposes. In this blog post, we will walk through an example of how to achieve this using a query object, a codeunit, in Business Central.

Scenario Overview

Imagine you have a "Sales Invoice Line" table that includes a custom field called "Integration Code." You want to find all the distinct integration codes and count how many times each one appears in the table. The table may look something like this:

Our goal is to find:

• IVT-SRT-001: 3 occurrences
• IVT-SRT-002: 3 occurrences
• IVT-SRT-003: 3 occurrences
• IVT-SRT-004: 1 occurrence

Step 1: Define the Query Object

To retrieve distinct "Integration Code" values and their counts, start by creating a query object. This query will group the records by "Integration Code" and count the number of records in each group.

Create a new AL file in your Visual Studio Code environment and define the query as follows:

tableextension 50510 tt extends "Sales Invoice Line"
{
    fields
    {
        // Add changes to table fields here
        field(50510; "Integraiton Code"; Text[20])
        {
            DataClassification = ToBeClassified;
            Caption = 'integration code';
        }
    }
}

query 50510 "DistinctCodes with Count"
{
    Caption = 'Distinct Integration Codes with Count';
    QueryType = Normal;

    elements
    {
        dataitem(SalesInvoiceLine; "Sales Invoice Line")
        {
            column(IntegrationCode; "Integraiton Code")
            {
                Caption = 'Integration Code';
            }
            column(Count)
            {
                Method = Count;
                Caption = 'Count';
            }
        }
    }

}        

Key Points:

• Integration Code Column: This column retrieves the "Integration Code" values from the "Sales Invoice Line" table.
• Count Column: The Count column counts the number of occurrences of each distinct integration code using the Method = Count; property.
• Filter: We filter out any blank values to ensure we're only counting meaningful data.

Step 2: Create a Codeunit to Retrieve Data

Next, create a codeunit that will execute the query and process the results. This codeunit will store the distinct values and their counts in a dictionary.

codeunit 50510 IntegrationCodeHandler
{
    procedure GetDistinctIntegrationCodesWithCount(): Dictionary of [Text[20], Integer]
    var
        IntegrationCodeQuery: Query "DistinctCodes with Count";
        IntegrationCodes: Dictionary of [Text[20], Integer];
        IntegrationCode: Text[20];
        Count: Integer;
    begin
        IntegrationCodeQuery.Open();
        while IntegrationCodeQuery.Read() do begin
            IntegrationCode := IntegrationCodeQuery.IntegrationCode;
            Count := IntegrationCodeQuery.Count;
            IntegrationCodes.Add(IntegrationCode, Count);
        end;
        IntegrationCodeQuery.Close();

        exit(IntegrationCodes);
    end;
}        

Key Points:

• Query Execution: The codeunit opens the query and iterates through the results.
• Dictionary Storage: Each integration code and its count are stored in a Dictionary, where the key is the integration code and the value is the count