Leveraging “Filter Tokens” codeunit to expand Business Central users’ filtering experience

Hello Readers!

A few weeks back, I watched Erik Hougaard‘s youtube video “Make your own Date Filters in AL and Business Central” and thought of trying it and adding my own bit to it.

First, what was the intention with custom filter tokens?

The standard application comes already with some tokens.

Think of dates, when you press “t” in a date field you get the today’s date, or when you press “q” in a date filter field you get the current quarter, and so on.

But what if we want to build our own tokens?

Custom Date Filters

For example, let’s assume that if I type “sv1” in a date filter I want the system to process my token into Jan 1st – Jan 31st. If I type “sv2” in a date filter I want the system to translate “sv2” into Feb 1st to Feb 29th or 28th depending on the current year, leap or not, and so on.

How can we do that? Extend the event OnResolveDateFilterToken from System Application codeunit “Filter Tokens” like in my sample code below:

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Filter Tokens", 'OnResolveDateFilterToken', '', false, false)]
    local procedure CustomDateFilter(DateToken: Text; var FromDate: Date; var Handled: Boolean; var ToDate: Date)
    begin
        DateToken := UpperCase(DateToken);
        case DateToken of
            'SV1':
                begin
                    FromDate := GetFromDate(Today(), 1);
                    ToDate := GetToDate(Today(), 1);
                    Handled := true;
                end;
            'SV2':
                begin
                    FromDate := GetFromDate(Today(), 2);
                    ToDate := GetToDate(Today(), 2);
                    Handled := true;
                end;
            'SV3':
                begin
                    FromDate := GetFromDate(Today(), 3);
                    ToDate := GetToDate(Today(), 3);
                    Handled := true;
                end;
            'SV4':
                begin
                    FromDate := GetFromDate(Today(), 4);
                    ToDate := GetToDate(Today(), 4);
                    Handled := true;
                end;
            'SV5':
                begin
                    FromDate := GetFromDate(Today(), 5);
                    ToDate := GetToDate(Today(), 5);
                    Handled := true;
                end;
            'SV6':
                begin
                    FromDate := GetFromDate(Today(), 6);
                    ToDate := GetToDate(Today(), 6);
                    Handled := true;
                end;
            'SV7':
                begin
                    FromDate := GetFromDate(Today(), 7);
                    ToDate := GetToDate(Today(), 7);
                    Handled := true;
                end;
            'SV8':
                begin
                    FromDate := GetFromDate(Today(), 8);
                    ToDate := GetToDate(Today(), 8);
                    Handled := true;
                end;

            'SV9':
                begin
                    FromDate := GetFromDate(Today(), 9);
                    ToDate := GetToDate(Today(), 9);
                    Handled := true;
                end;
            'SV10':
                begin
                    FromDate := GetFromDate(Today(), 10);
                    ToDate := GetToDate(Today(), 10);
                    Handled := true;
                end;
            'SV11':
                begin
                    FromDate := GetFromDate(Today(), 11);
                    ToDate := GetToDate(Today(), 11);
                    Handled := true;
                end;
            'SV12':
                begin
                    FromDate := GetFromDate(Today(), 12);
                    ToDate := GetToDate(Today(), 12);
                    Handled := true;
                end;
        end;
    end;

    local procedure GetFromDate(Dt: Date; mo: integer): Date
    begin
        Exit(DMY2Date(1, mo, Date2DMY(Dt, 3)));
    end;

    local procedure GetToDate(Dt: Date; mo: integer): Date
    begin
        case mo of
            1, 3, 5, 7, 8, 10, 12:
                Exit(DMY2Date(31, mo, Date2DMY(Dt, 3)));
            4, 6, 9, 11:
                Exit(DMY2Date(30, mo, Date2DMY(Dt, 3)));
            2:
                begin
                    if Date2DMY(Dt, 3) div 4 = 0 then
                        Exit(DMY2Date(29, mo, Date2DMY(Dt, 3)))
                    else
                        Exit(DMY2Date(28, mo, Date2DMY(Dt, 3)))
                end;
        end

The code could be refactored into a function that parses a 4 characters token of form “svxy” and call once GetToDate and once GetFromDate instead of 12 calls, but that’s not the goal of this blog.

Let’s test it.

Open Chart of Accounts page and use the flow filters in the “Filter Totals By” section of the page as below:

What about text filters? Can we customize them?

Custom Text Filters

This is the use case: each user has access to his list of customers (My Customer page):

Users can edit their own list of customers, adding/removing customers.

We also want, when we are in the Customers list, to be able to quickly filter the list of customers to the list in My Customers.

We can create a custom text filter token and by subscribing to event OnResolveTextFilterToken in codeunit “Filter Tokens” we get the functionality desired, like below:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Filter Tokens", 'OnResolveTextFilterToken', '', true, true)]
    local procedure CustomTextFilter(TextToken: Text; var TextFilter: Text; var Handled: Boolean)
    var
        _mc: Record "My Customer";
        _maxloops: integer;
    begin
        _maxloops := 10;
        TextToken := UpperCase(TextToken);
        Handled := true;
        case TextToken of
            'SV':
                begin
                    _mc.SetRange("User ID", UserId());
                    if _mc.FindSet() then begin
                        _maxloops -= 1;
                        _maxloops -= 1;
                        TextFilter := _mc."Customer No.";
                        if _mc.Next() <> 0 then
                            repeat
                                _maxloops -= 1;
                                TextFilter += '|' + _mc."Customer No.";
                            until (_mc.Next() = 0) or (_maxloops <= 0);
                    end
                end;
        end;
    end;

In the Customers List we can now use the new token:

When users filter the “No.” field to “%sv” the system finds all Customer “No.” in My Customer list and populates the filter for “No.” field.

My Customer list consists of customers 20000,30000, and 50000 and therefore when using custom text filter “sv” I get the list of my customers.

You could similarly create a custom token to filter Chart of Accounts to G/L accounts in “My Accounts”.

Things to consider

The token above “sv” would be triggered and parsed in any page.

For example, if we are in the Vendors list the same list (20000,30000 and 50000) will be the result of parsing “sv” token. And that might not be what we need.

A possible solution is to specialize the custom filters to customers or to vendors, like having 2 tokens: “csv” for customers and “vsv” for vendors.

For more considerations when using custom tokens read here.

Go on, try them!

One thought on “Leveraging “Filter Tokens” codeunit to expand Business Central users’ filtering experience

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s