KPI Counting Tool

In this post I would like to share a simple but not so easy to understand KPI tool for counting records in any given table in Business Central.

This is a developers and endusers oriented tool. You can filter any table and apply any possible filter you might need.

Although it is a quite simple code it involves some concepts that the less experienced might not be familiar with:

• FilterPageBuilder complex data type.
• RecordRef
• GetView/SetView
• Data Entry Page

I will go step by step for better understanding.

Concept

The tool has a data entry page to enter a table number. A RecordRef opens the table and with the help of a FilterPageBuilder the table can be filtered. Finally, a message is displayed with the number of records found.

Data Entry Page

The data entry page is a ‘StandardDialog’ page to let the users select the table they will filter from.

page 50130 "DataEntry"
{

    Caption = 'Data Entry';
    PageType = StandardDialog;

    layout
    {
        area(content)
        {
            field(TableNo; xgInteger)
            {
                ApplicationArea = All;
                Caption = 'Table No.';
            }
        }
    }

    var
        xgInteger: Integer;

    procedure SetNo(): Integer
    begin
        exit(xgInteger);
    end;
}

To use the table number in the FilterPageBuilder we have to save it in a variable:

FilterPageBuilder

Description:

“A FilterPageBuilder data type is a complex data type that stores filter configurations for a filter page. A filter page is a dynamic page type that contains one or more filter controls that enables users to set filters on fields of the underlying tables.”

That is, a dynamic filtering page for tables.

To use it we can set a caption (PageCaption) for the page and add the table (AddTable) from which we wish to filter from. In our case it will be the table number of our data entry page:

RecordRef and GetView/SetView

With the help of RecordRef we will open the table dynamically, that is, depending on the table number from the data entry page.

The GetView method is used for retrieving filters from the PageBuilder and the SetView for filtering the RecordRef. The final code will be as shown below:

action(KPITool)
{
    ApplicationArea = All;
    Image = TestReport;

    trigger OnAction()
    var
        pglEntry: Page DataEntry;
        xlRecRef: RecordRef;
        xlPageBuilder: FilterPageBuilder;
        xlTableNo: Integer;
        ProcessCanceledLbl: Label 'Process canceled';
    begin
        if pglEntry.RunModal() in [Action::OK, Action::Yes] then begin
            xlTableNo := pglEntry.SetNo();

            xlPageBuilder.PageCaption('Quick KPI tool');
            xlPageBuilder.AddTable('Filter', xlTableNo);
            if xlPageBuilder.RunModal() then begin
                xlRecRef.Open(xlTableNo);
                xlRecRef.SetView(xlPageBuilder.GetView('Filter'));
            end else begin
                Error(Format(ProcessCanceledLbl));
            end;
            Message(Format(xlRecRef.Count));
        end;
    end;
}

All the code needed to build the KPI tool is shown below:

Let´s test it!. As an example we would like to know the amount of customers that exceed a Credit Amount of 5000:

As we can see the amount of customers is 6. We have chosen table 18 for filtering customers but we can select any table and apply any possible filter.

The only issue is that the table number needs to be known beforehand. But with the shortcut (Ctrt+Alt+F1) we can easily see it and then use the tool.

Hope you find it clear and useful.