Dynamics 365 Business Central: How to count the same value of different fields in one line (For example, how many “OK” in a line)

Dynamics 365 Business Central

Hi, Readers.
Today I would like to talk about how to count the same value of different fields in one line in Business Central.

As you might know, we can use Record.Count() Method to count the number of records in a table.

For example, if I want to know how many invoices there are in Customer Ledger Entries, I can add filters first and then use the Record.Count() Method to get the number of records very easily.

But yesterday, I was asked a very interesting question about how to count the number of records in landscape. For example, how many “OK” in a line?
Here is a finished example:

At this point we can’t filter the record because it’s on the same line, and we can’t use the Record.Count() Method. So how should we do it? In this blog, I will present three methods, and hopefully at least one of them will help you.

Test Video: Because the results of the three methods are the same, I put the test video here first. For convenience, I used OnAfterModify() trigger and OnAfterInsert() trigger. If you want the field value to be calculated immediately after modification, please move the code to the field’s OnValidate() trigger.

Method 01

This method is very easy to understand, manually counting the number of each field value in the line, but if the number of fields is very large, it will be a bit cumbersome.

Keypoint:

Source Code:

tableextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    fields
    {
        field(50101; "Process Check 01"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50102; "Process Check 02"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50103; "Process Check 03"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50104; "Process Check 04"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50105; "Process Check 05"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50106; "Process Check 06"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50107; "Total No. of OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50108; "Total No. of Pending"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50109; "Total No. of Not OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
    }
    var
        TotalNoOfOK: Integer;
        TotalNoOfPending: Integer;
        TotalNoOfNotOK: Integer;

    trigger OnAfterModify()
    begin
        CountProcessStatus();
    end;

    trigger OnAfterInsert()
    begin
        CountProcessStatus();
    end;

    local procedure CountProcessStatus()
    begin
        TotalNoOfOK := 0;
        TotalNoOfPending := 0;
        TotalNoOfNotOK := 0;
        CheckProcessStatus(Rec."Process Check 01");
        CheckProcessStatus(Rec."Process Check 02");
        CheckProcessStatus(Rec."Process Check 03");
        CheckProcessStatus(Rec."Process Check 04");
        CheckProcessStatus(Rec."Process Check 05");
        CheckProcessStatus(Rec."Process Check 06");
        Rec."Total No. of OK" := TotalNoOfOK;
        Rec."Total No. of Pending" := TotalNoOfPending;
        Rec."Total No. of Not OK" := TotalNoOfNotOK;
        Rec.Modify();
    end;

    local procedure CheckProcessStatus(var ProcessCheck: Enum "Process Check")
    begin
        case ProcessCheck of
            ProcessCheck::OK:
                TotalNoOfOK += 1;
            ProcessCheck::Pending:
                TotalNoOfPending += 1;
            ProcessCheck::"Not OK":
                TotalNoOfNotOK += 1;
        end;
    end;
}
pageextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    layout
    {
        addbefore("Due Date Calculation")
        {
            field("Process Check 01"; Rec."Process Check 01")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 01 field.';
            }
            field("Process Check 02"; Rec."Process Check 02")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 02 field.';
            }
            field("Process Check 03"; Rec."Process Check 03")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 03 field.';
            }
            field("Process Check 04"; Rec."Process Check 04")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 04 field.';
            }
            field("Process Check 05"; Rec."Process Check 05")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 05 field.';
            }
            field("Process Check 06"; Rec."Process Check 06")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 06 field.';
            }
            field("Total No. of OK"; Rec."Total No. of OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of OK field.';
            }
            field("Total No. of Pending"; Rec."Total No. of Pending")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Pending field.';
            }
            field("Total No. of Not OK"; Rec."Total No. of Not OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Not OK field.';
            }
        }
    }
}
enum 50100 "Process Check"
{
    Extensible = true;
    value(0; OK)
    {
    }
    value(1; Pending)
    {
    }
    value(2; "Not OK")
    {
    }
}

Method 02

This method is similar to the Method 01, except that a temporary table is created, each value is inserted into the temporary table, and then using the Record.Count() Method directly.

Keypoint:

Source Code:

tableextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    fields
    {
        field(50101; "Process Check 01"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50102; "Process Check 02"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50103; "Process Check 03"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50104; "Process Check 04"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50105; "Process Check 05"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50106; "Process Check 06"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50107; "Total No. of OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50108; "Total No. of Pending"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50109; "Total No. of Not OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
    }
    var
        CountTable: Record CountTable;

    trigger OnAfterModify()
    begin
        CountProcessStatus();
    end;

    trigger OnAfterInsert()
    begin
        CountProcessStatus();
    end;

    local procedure CountProcessStatus()
    begin
        CountTable.Reset();
        CountTable.DeleteAll();
        InsertCountTable(Rec."Process Check 01");
        InsertCountTable(Rec."Process Check 02");
        InsertCountTable(Rec."Process Check 03");
        InsertCountTable(Rec."Process Check 04");
        InsertCountTable(Rec."Process Check 05");
        InsertCountTable(Rec."Process Check 06");
        CountTable.Reset();
        CountTable.SetRange("Process Check", CountTable."Process Check"::OK);
        Rec."Total No. of OK" := CountTable.Count;
        CountTable.Reset();
        CountTable.SetRange("Process Check", CountTable."Process Check"::Pending);
        Rec."Total No. of Pending" := CountTable.Count;
        CountTable.Reset();
        CountTable.SetRange("Process Check", CountTable."Process Check"::"Not OK");
        Rec."Total No. of Not OK" := CountTable.Count;
        Rec.Modify();
    end;

    local procedure InsertCountTable(var "Process Check": Enum "Process Check")
    begin
        CountTable.Init();
        CountTable.EntryNo += 1;
        CountTable."Process Check" := "Process Check";
        CountTable.Insert();
    end;
}
pageextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    layout
    {
        addbefore("Due Date Calculation")
        {
            field("Process Check 01"; Rec."Process Check 01")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 01 field.';
            }
            field("Process Check 02"; Rec."Process Check 02")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 02 field.';
            }
            field("Process Check 03"; Rec."Process Check 03")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 03 field.';
            }
            field("Process Check 04"; Rec."Process Check 04")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 04 field.';
            }
            field("Process Check 05"; Rec."Process Check 05")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 05 field.';
            }
            field("Process Check 06"; Rec."Process Check 06")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 06 field.';
            }
            field("Total No. of OK"; Rec."Total No. of OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of OK field.';
            }
            field("Total No. of Pending"; Rec."Total No. of Pending")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Pending field.';
            }
            field("Total No. of Not OK"; Rec."Total No. of Not OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Not OK field.';
            }
        }
    }
}
enum 50100 "Process Check"
{
    Extensible = true;
    value(0; OK)
    {
    }
    value(1; Pending)
    {
    }
    value(2; "Not OK")
    {
    }
}
table 50110 CountTable
{
    DataClassification = CustomerContent;
    TableType = Temporary;
    fields
    {
        field(1; EntryNo; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(2; "Process Check"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
    }
    keys
    {
        key(PK; EntryNo)
        {
            Clustered = true;
        }
    }
}

Method 03

This method uses RecordRef Data Type, FieldRef Data Type and Field table. This method is recommended when you need to count a large number of fields.

Keypoint:

Source Code:

tableextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    fields
    {
        field(50101; "Process Check 01"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50102; "Process Check 02"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50103; "Process Check 03"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50104; "Process Check 04"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50105; "Process Check 05"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50106; "Process Check 06"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50107; "Total No. of OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50108; "Total No. of Pending"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50109; "Total No. of Not OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
    }
    var
        TotalNoOfOK: Integer;
        TotalNoOfPending: Integer;
        TotalNoOfNotOK: Integer;

    trigger OnAfterModify()
    begin
        CountProcessStatus();
    end;

    trigger OnAfterInsert()
    begin
        CountProcessStatus();
    end;

    local procedure CountProcessStatus()
    var
        RecRef: RecordRef;
        FldRef: FieldRef;
        FieldRec: Record Field;
    begin
        TotalNoOfOK := 0;
        TotalNoOfPending := 0;
        TotalNoOfNotOK := 0;
        Clear(RecRef);
        Clear(FldRef);
        RecRef.Open(Rec.RecordId.TableNo);
        RecRef.Get(Rec.RecordId);
        FieldRec.Reset();
        FieldRec.SetRange(TableNo, Rec.RecordId.TableNo);
        FieldRec.SetRange(Enabled, true);
        FieldRec.SetRange(Class, FieldRec.Class::Normal);
        if FieldRec.FindSet() then
            repeat
                FldRef := RecRef.Field(FieldRec."No.");
                case Format(FldRef.Value) of
                    Format(Enum::"Process Check"::OK):
                        TotalNoOfOK += 1;
                    Format(Enum::"Process Check"::Pending):
                        TotalNoOfPending += 1;
                    Format(Enum::"Process Check"::"Not OK"):
                        TotalNoOfNotOK += 1;
                end;
            until FieldRec.Next() = 0;
        Rec."Total No. of OK" := TotalNoOfOK;
        Rec."Total No. of Pending" := TotalNoOfPending;
        Rec."Total No. of Not OK" := TotalNoOfNotOK;
        Rec.Modify();
    end;
}
pageextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    layout
    {
        addbefore("Due Date Calculation")
        {
            field("Process Check 01";
            Rec."Process Check 01")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 01 field.';
            }
            field("Process Check 02"; Rec."Process Check 02")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 02 field.';
            }
            field("Process Check 03"; Rec."Process Check 03")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 03 field.';
            }
            field("Process Check 04"; Rec."Process Check 04")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 04 field.';
            }
            field("Process Check 05"; Rec."Process Check 05")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 05 field.';
            }
            field("Process Check 06"; Rec."Process Check 06")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 06 field.';
            }
            field("Total No. of OK"; Rec."Total No. of OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of OK field.';
            }
            field("Total No. of Pending"; Rec."Total No. of Pending")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Pending field.';
            }
            field("Total No. of Not OK"; Rec."Total No. of Not OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Not OK field.';
            }
        }
    }
}
enum 50100 "Process Check"
{
    Extensible = true;
    value(0; OK)
    {
    }
    value(1; Pending)
    {
    }
    value(2; "Not OK")
    {
    }
}

PS: A interesting question from Jeremy Vyska.😀

He also provided us with the answer.
More details: Twitter info
And the source code: Demo-FilterGroupNegativeOne

In his example, he used the Record.FilterGroup([Integer]) Method, Record.FilterGroup(-1). What is Record.FilterGroup(-1)? Generally, when we add filters in AL, we use And Statement, but if you set Record.FilterGroup(-1), it will be OR Statement. (More details: Cross-column search (OR filters on multiple fields) ★FilterGroup(-1))

So, according to the method he provided, apply it to my test code this time.

Keypoint:

Source Code:

tableextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    fields
    {
        field(50101; "Process Check 01"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50102; "Process Check 02"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50103; "Process Check 03"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50104; "Process Check 04"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50105; "Process Check 05"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50106; "Process Check 06"; Enum "Process Check")
        {
            DataClassification = CustomerContent;
        }
        field(50107; "Total No. of OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50108; "Total No. of Pending"; Integer)
        {
            DataClassification = CustomerContent;
        }
        field(50109; "Total No. of Not OK"; Integer)
        {
            DataClassification = CustomerContent;
        }
    }
    var
        TotalNoOfOK: Integer;
        TotalNoOfPending: Integer;
        TotalNoOfNotOK: Integer;

    trigger OnAfterModify()
    begin
        CountProcessStatus();
    end;

    trigger OnAfterInsert()
    begin
        CountProcessStatus();
    end;

    local procedure CountProcessStatus()
    var
        RecRef: RecordRef;
        FldRef: FieldRef;
        FieldRec: Record Field;
    begin
        TotalNoOfOK := 0;
        TotalNoOfPending := 0;
        TotalNoOfNotOK := 0;
        Clear(RecRef);
        Clear(FldRef);
        RecRef.Open(Rec.RecordId.TableNo);
        RecRef.Get(Rec.RecordId);
        FieldRec.Reset();
        FieldRec.SetRange(TableNo, Rec.RecordId.TableNo);
        FieldRec.SetRange(Enabled, true);
        FieldRec.SetRange(Class, FieldRec.Class::Normal);
        if FieldRec.FindSet() then
            repeat
                FldRef := RecRef.Field(FieldRec."No.");
                case Format(FldRef.Value) of
                    Format(Enum::"Process Check"::OK):
                        TotalNoOfOK += 1;
                    Format(Enum::"Process Check"::Pending):
                        TotalNoOfPending += 1;
                    Format(Enum::"Process Check"::"Not OK"):
                        TotalNoOfNotOK += 1;
                end;
            until FieldRec.Next() = 0;
        Rec."Total No. of OK" := TotalNoOfOK;
        Rec."Total No. of Pending" := TotalNoOfPending;
        Rec."Total No. of Not OK" := TotalNoOfNotOK;
        Rec.Modify();
    end;
}
pageextension 50111 ZYPaymentTermsExt extends "Payment Terms"
{
    layout
    {
        addbefore("Due Date Calculation")
        {
            field("Process Check 01"; Rec."Process Check 01")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 01 field.';
            }
            field("Process Check 02"; Rec."Process Check 02")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 02 field.';
            }
            field("Process Check 03"; Rec."Process Check 03")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 03 field.';
            }
            field("Process Check 04"; Rec."Process Check 04")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 04 field.';
            }
            field("Process Check 05"; Rec."Process Check 05")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 05 field.';
            }
            field("Process Check 06"; Rec."Process Check 06")
            {
                ApplicationArea = All;
                ToolTip = 'Specifies the value of the Process Check 06 field.';
            }
            field("Total No. of OK"; Rec."Total No. of OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of OK field.';
            }
            field("Total No. of Pending"; Rec."Total No. of Pending")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Pending field.';
            }
            field("Total No. of Not OK"; Rec."Total No. of Not OK")
            {
                ApplicationArea = All;
                Editable = false;
                ToolTip = 'Specifies the value of the Total No. of Not OK field.';
            }
        }
    }

    actions
    {
        addfirst(processing)
        {
            action(TotalsAction)
            {
                ApplicationArea = All;
                Image = Totals;
                Promoted = true;
                PromotedCategory = Process;
                PromotedOnly = true;
                Caption = 'Table Counts';
                ToolTip = 'This will Count the OK entries';

                trigger OnAction()
                begin
                    CountAcrossLines();
                end;
            }
        }
    }

    local procedure CountAcrossLines()
    var
        PaymentTerms: Record "Payment Terms";
        ResultMsg: Label 'There are\OK: %1\Pending: %2\Not OK: %3';
        TotalNoOfOK: Integer;
        TotalNoOfPending: Integer;
        TotalNoOfNotOK: Integer;
    begin
        PaymentTerms.Reset();
        PaymentTerms.FilterGroup(-1);  // Magic!  It's a Field level OR filter


        QuickApplyBatchFilters(PaymentTerms, Enum::"Process Check"::OK);
        TotalNoOfOK := PaymentTerms.Count;

        QuickApplyBatchFilters(PaymentTerms, Enum::"Process Check"::Pending);
        TotalNoOfPending := PaymentTerms.Count;

        QuickApplyBatchFilters(PaymentTerms, Enum::"Process Check"::"Not OK");
        TotalNoOfNotOK := PaymentTerms.Count;

        Message(ResultMsg, TotalNoOfOK, TotalNoOfPending, TotalNoOfNotOK);
    end;

    local procedure QuickApplyBatchFilters(var PaymentTerms: Record "Payment Terms"; ProcessCheck: Enum "Process Check")
    begin
        PaymentTerms.SetRange("Process Check 01", ProcessCheck);
        PaymentTerms.SetRange("Process Check 02", ProcessCheck);
        PaymentTerms.SetRange("Process Check 03", ProcessCheck);
        PaymentTerms.SetRange("Process Check 04", ProcessCheck);
        PaymentTerms.SetRange("Process Check 05", ProcessCheck);
        PaymentTerms.SetRange("Process Check 06", ProcessCheck);
    end;
}
enum 50100 "Process Check"
{
    Extensible = true;
    value(0; OK)
    {
    }
    value(1; Pending)
    {
    }
    value(2; "Not OK")
    {
    }
}

END

Hope this will help.

Thanks for reading.

ZHU

コメント

Copied title and URL