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!
