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
コメント