Hi, Readers.
Yesterday we discussed how to count the same value of different fields in one line (For example, how many “OK” in a line) in Business Central, I received a lot of feedback, which was much appreciated.
One of them is a very interesting topic raised by Jeremy Vyska.
![画像に alt 属性が指定されていません。ファイル名: image-221.png](https://yzhums.com/wp-content/uploads/2022/08/image-221.png)
He has 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).
According to the method he provided, I applied it to my test code yesterday.
![画像に alt 属性が指定されていません。ファイル名: image-223-1024x536.png](https://yzhums.com/wp-content/uploads/2022/08/image-223-1024x536.png)
Result:
![画像に alt 属性が指定されていません。ファイル名: image-224-1024x585.png](https://yzhums.com/wp-content/uploads/2022/08/image-224-1024x585.png)
Keypoint:
![画像に alt 属性が指定されていません。ファイル名: image-225.png](https://yzhums.com/wp-content/uploads/2022/08/image-225.png)
So in this post I would like to briefly talk about what is Record.FilterGroup(-1). Hope this will help.
Let’s take a look at MS Docs first.
Record.FilterGroup([Integer]) Method: Gets or sets the filter group that is applied to a table.
Dynamics 365 Business Central uses the following filter groups internally. (-1..7)
![画像に alt 属性が指定されていません。ファイル名: image-222.png](https://yzhums.com/wp-content/uploads/2022/08/image-222.png)
Number | Name | Description |
---|---|---|
-1 | Cross-column | Used to support the cross-column search. |
And Microsoft provides a good example.
The following example finds all customers where the Customer Name or Contact Name contains the string John.
var
SearchString: Text;
begin
Customer.FilterGroup := -1;
SearchString := '@*John*';
Customer.SetFilter(Customer.Name, SearchString);
Customer.SetFilter(Customer.Contact, SearchString);
end;
Let’s see more details.
If you have filters on multiple fields in the same filter group, then only records matching all filters are visible. (And Condition)
For example,
The Customer table.
![](https://yzhums.com/wp-content/uploads/2022/08/image-227-1024x371.png)
Add two filters, “Salesperson Code” is ‘JO’ and “Location Code” is ‘EAST’.
![](https://yzhums.com/wp-content/uploads/2022/08/image-230.png)
We will get a record.
![](https://yzhums.com/wp-content/uploads/2022/08/image-231-1024x500.png)
The only exception to this is filtergroup -1 where records only need to match at least one of the filters. (OR Condition)
For example,
The Customer table.
![](https://yzhums.com/wp-content/uploads/2022/08/image-227-1024x371.png)
Add FilterGroup(-1) and two filters, “Salesperson Code” is ‘JO’ or “Location Code” is ‘EAST’.
![](https://yzhums.com/wp-content/uploads/2022/08/image-232.png)
We will get three records.
![](https://yzhums.com/wp-content/uploads/2022/08/image-233-1024x500.png)
Test Video:
Actually Record.FilterGroup(-1) is the same as the SQL query below.
![](https://yzhums.com/wp-content/uploads/2022/08/image-234-1024x611.png)
SELECT [No_]
,[Name]
,[Salesperson Code]
,[Amount]
,[Location Code]
FROM [Demo Database BC (20-1)].[dbo].[CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]
WHERE [Salesperson Code]='JO' OR [Location Code]='BLUE';
PS:
1. Record.FilterGroup(-1) cannot be used to add default filter in OnOpenPage trigger.
For example, I did the following test where we get three records, but still show all records on the page. I think this should be caused by the system’s inability to add this special filters. So please use the method I tested above.
![](https://yzhums.com/wp-content/uploads/2022/08/image-235.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-236-1024x415.png)
General filtering:
![](https://yzhums.com/wp-content/uploads/2022/08/image-238.png)
![](https://yzhums.com/wp-content/uploads/2022/08/image-237-1024x392.png)
2. Source Code:
page 50100 "New Customer List"
{
ApplicationArea = All;
Caption = 'New Customer List';
PageType = List;
SourceTable = Customer;
SourceTableTemporary = true;
UsageCategory = Lists;
layout
{
area(content)
{
repeater(General)
{
field("No."; Rec."No.")
{
ApplicationArea = All;
ToolTip = 'Specifies the number of the customer. The field is either filled automatically from a defined number series, or you enter the number manually because you have enabled manual number entry in the number-series setup.';
}
field(Name; Rec.Name)
{
ApplicationArea = All;
ToolTip = 'Specifies the customer''s name.';
}
field("Responsibility Center"; Rec."Responsibility Center")
{
ApplicationArea = All;
ToolTip = 'Specifies the code for the responsibility center that will administer this customer by default.';
}
field("Salesperson Code"; Rec."Salesperson Code")
{
ApplicationArea = All;
ToolTip = 'Specifies a code for the salesperson who normally handles this customer''s account.';
}
field("Location Code"; Rec."Location Code")
{
ApplicationArea = All;
ToolTip = 'Specifies from which location sales to this customer will be processed by default.';
}
field("Phone No."; Rec."Phone No.")
{
ApplicationArea = All;
ToolTip = 'Specifies the customer''s telephone number.';
}
field(Contact; Rec.Contact)
{
ApplicationArea = All;
ToolTip = 'Specifies the name of the person you regularly contact when you do business with this customer.';
}
field("Balance (LCY)"; Rec."Balance (LCY)")
{
ApplicationArea = All;
ToolTip = 'Specifies the payment amount that the customer owes for completed sales. This value is also known as the customer''s balance.';
}
}
}
}
trigger OnOpenPage()
var
Cust: Record Customer;
begin
Cust.Reset();
//Cust.FilterGroup(-1);
Cust.SetRange("Salesperson Code", 'JO');
Cust.SetRange("Location Code", 'EAST');
if Cust.FindSet() then
repeat
Rec := Cust;
Rec.Insert();
until Cust.Next() = 0;
end;
}
END
Hope this will help.
Thanks for reading.
ZHU
コメント