Dynamics 365 Business Central: How to check if there is only one value in the filter for a field (For example, check if number of G/L Account No. filter is greater than 1)

Hi, Readers.
Today I would like to talk about an interesting question I was asked before, is there any way to check if there is only one value in the filter for a field, for example, check if number of G/L Account No. filter is greater than 1.

As you might know, when you enter criteria, you can use all the numbers and letters that you normally use in the field. But there’s also a set of special symbols that you can use as operators to further filter the results. The following sections describe these symbols and how to use them as operators in filters.

(..) Interval

(|) Either/or

(<>) Not equal to

(>) Greater than

(>=) Greater than or equal to

(<) Less than

(<=) Less than or equal to

(&) And

(*) An indefinite number of unknown characters

(?) One unknown character

Combined Format Expressions

This is a very powerful feature, and it’s not just limited to the UI, it can also be done in code.

Record.SetRange(Any [, Any] [, Any]) Method: Sets a simple filter, such as a single range or a single value, on a field.

Record.SetFilter(Any, Text [, Any,…]) Method: Assigns a filter to a field that you specify.

You can add filter expression. A valid expression consists of alphanumeric characters and one or more of the following operators: <, <=, >, >=, <>, &, .., *, | and @. You can use replacement fields (%1, %2, and so on) to insert values at runtime.

So what should we do when we want to run the code only when there is only one value in the filter? Yes, first we need to get the filter content. At this time, we can use Record.GetFilter(Any) Method.

Record.GetFilter(Any) Method: Gets a list of the filters within the current filter group that are applied to a field.
Let’s see a simple example.

Great.

Test video:

Next, we need to determine whether the operators are included in the filter content.
Operators: <, <=, >, >=, <>, &, .., *, | and @
Another thing to note is that if the filtered content is empty, it means that all value is included, and this should also be excluded.

And, the ‘=’ equal sign cannot be excluded, because when there is only one, it can also be written towards the following.

Let’s do a test.
PS: Text.StrPos(Text, Text) Method: Searches for the first occurrence of substring inside a string.

Looks good.

Source Code: Github

pageextension 50100 GeneralLedgerEntries extends "General Ledger Entries"
{
    actions
    {
        addafter(ReverseTransaction)
        {
            action(GetFilter)
            {
                Caption = 'Get Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = GetActionMessages;
                ApplicationArea = All;

                trigger OnAction()
                begin
                    Message(Rec.GetFilter("G/L Account No."))
                end;
            }
            action(CheckFilter)
            {
                Caption = 'Check Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = DefaultFault;
                ApplicationArea = All;

                trigger OnAction()
                begin
                    if (Rec.GetFilter("G/L Account No.") = '') or
                    (StrPos(Rec.GetFilter("G/L Account No."), '|') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '>') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '<') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '*') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '?') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '..') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '@') <> 0) or
                    (StrPos(Rec.GetFilter("G/L Account No."), '&') <> 0) then
                        Message('Contains more than one value in the filter for G/L Account No. field')
                    else
                        Message('Only one value in the filter for G/L Account No. field');
                end;
            }
        }
    }
}

We can also use the following two methods.
1. Using Text.DelChr(Text [, Text] [, Text]) Method: Deletes chars contained in the which parameter in a string based on the contents on the where parameter.
and Text.StrLen(Text) Method: Gets the length of a string you define.

pageextension 50100 GeneralLedgerEntries extends "General Ledger Entries"
{
    actions
    {
        addafter(ReverseTransaction)
        {
            action(GetFilter)
            {
                Caption = 'Get Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = GetActionMessages;
                ApplicationArea = All;

                trigger OnAction()
                begin
                    Message(Rec.GetFilter("G/L Account No."))
                end;
            }
            action(CheckFilter)
            {
                Caption = 'Check Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = DefaultFault;
                ApplicationArea = All;

                trigger OnAction()
                var
                    SpecialChars: Label '|><*?.@&';
                    Len: Integer;
                begin
                    Len := 0;
                    Len := StrLen(DelChr(Rec.GetFilter("G/L Account No."), '=', DelChr(Rec.GetFilter("G/L Account No."), '=', SpecialChars)));
                    if (Rec.GetFilter("G/L Account No.") = '') or (Len > 0) then
                        Message('Contains more than one value in the filter for G/L Account No. field')
                    else
                        Message('Only one value in the filter for G/L Account No. field');
                end;
            }
        }
    }
}

2. Using Foreach control structure
PS: Business Central 2023 wave 1 (BC22):Iterating with foreach on Text variables

pageextension 50100 GeneralLedgerEntries extends "General Ledger Entries"
{
    actions
    {
        addafter(ReverseTransaction)
        {
            action(GetFilter)
            {
                Caption = 'Get Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = GetActionMessages;
                ApplicationArea = All;

                trigger OnAction()
                begin
                    Message(Rec.GetFilter("G/L Account No."))
                end;
            }
            action(CheckFilter)
            {
                Caption = 'Check Filter';
                Promoted = true;
                PromotedCategory = Process;
                Image = DefaultFault;
                ApplicationArea = All;

                trigger OnAction()
                var
                    SpecialChars: Label '|><*?.@&';

                    CurrentLetter: Text;
                    i: Integer;
                begin
                    i := 0;
                    foreach CurrentLetter in SpecialChars do begin
                        if StrPos(Rec.GetFilter("G/L Account No."), CurrentLetter) > 0 then
                            i += 1;
                    end;
                    if (Rec.GetFilter("G/L Account No.") = '') or (i > 0) then
                        Message('Contains more than one value in the filter for G/L Account No. field')
                    else
                        Message('Only one value in the filter for G/L Account No. field');
                end;
            }
        }
    }
}

That’s all. The last thing I want to mention is that the text you need to check cannot contain these operators (<, <=, >, >=, <>, &, .., *, | and @), which will affect the code checking. However, this is not limited to the functions discussed this time. There will also be problems with filtering standard functions, so please avoid doing this.
Give it a try!!!😁

PS:
1. How to find duplicates via AL (Filter for duplicate values)

2. How to get a filter for the selected records on any page (For example, ‘1..3|6’)

3. How to count the same value of different fields in one line (For example, how many “OK” in a line)

4. How to check whether selected records have the same value in a field?

5. How to prohibit users from entering illegal characters such as emoji, Hiragana, kanji, etc.

END

Hope this will help.

Thanks for reading.

ZHU