As a user of the Business Central you have some constants you can use to filter or insert values.
These constants contain useful values for data manipulation such as
- t / today for date field – return current date
- q / quarter for date field filters – return range of the current quarter
- w / week, m / month, y / year ….
However, in some cases, we need some other constants. For that reason, we can use codeunit 41 “Filter Tokens” (codeunit 58 “Filter Tokens Impl.”). It contains functionality that allows defining our own constants for custom fields. Source codes for this functionality are on Microsoft’s GitHub.
Let’s look at some details.
Available procedures & events in “Filter Tokens” codeunit
We have four different procedures for filter token validation based on the requested data type. All procedures accept filter token as a parameter and returns (within the same input-output parameter) date value or date filter value.
procedure MakeDateFilter(var DateFilterText: Text)
procedure MakeTimeFilter(var TimeFilterText: Text)
procedure MakeTextFilter(var TextFilter: Text)
procedure MakeDateTimeFilter(var DateTimeFilterText: Text)These procedures have support for some constants by default.
For text variables, supported constants are ME, USER (return UserId()) and COMPANY (returns CompanyName()).
For date, time and DateTime variables we have similar constants to the constants available for standard table filters such as NOW, TODAY, WORKDATE, YESTERDAY, TOMORROW, WEEK, MONTH, YEAR.
However, the biggest advantage of this functionality is that we can use our own constants. To add support for a new constant we have to define constant transformation (to date or time variables) using the following OnResolve publishers.
procedure OnResolveDateFilterToken(DateToken: Text; var FromDate: Date; var ToDate: Date; var Handled: Boolean)
procedure OnResolveTextFilterToken(TextToken: Text; var TextFilter: Text; var Handled: Boolean)
procedure OnResolveTimeFilterToken(TimeToken: Text; var TimeFilter: Time; var Handled: Boolean)
procedure OnResolveDateTokenFromDateTimeFilter(DateToken: Text; var DateFilter: Date; var Handled: Boolean)
procedure OnResolveTimeTokenFromDateTimeFilter(TimeToken: Text; var TimeFilter: Time; var Handled: Boolean)How to add own constants
Imagine we want to add support for new constants Monday to Sunday from Last, This and Next week. To do that, just implement OnResolveDateFilterToken from Codeunit Filter Tokens. See code below.
[EventSubscriber(ObjectType::Codeunit, Codeunit::"Filter Tokens", 'OnResolveDateFilterToken', '', false, false)]
local procedure OnResolveDateFilterTokenFilterTokens(DateToken: Text; var FromDate: Date; var ToDate: Date; var Handled: Boolean)
var
StartingDate: Date;
begin
StartingDate := WorkDate();
if DateToken.Contains('last') then
StartingDate := CalcDate('<-CW-1D>', WorkDate());
if DateToken.Contains('this') then
StartingDate := CalcDate('<-CW>', WorkDate());
if DateToken.Contains('next') then
StartingDate := CalcDate('<+CW+1D>', WorkDate());
case true of
DateToken.Contains('monday'):
FromDate := CalcDate('<-CW>', StartingDate);
DateToken.Contains('tuesday'):
FromDate := CalcDate('<-CW+1D>', StartingDate);
DateToken.Contains('wednesday'):
FromDate := CalcDate('<-CW+2D>', StartingDate);
DateToken.Contains('thursday'):
FromDate := CalcDate('<-CW+3D>', StartingDate);
DateToken.Contains('friday'):
FromDate := CalcDate('<-CW+4D>', StartingDate);
DateToken.Contains('saturday'):
FromDate := CalcDate('<-CW+5D>', StartingDate);
DateToken.Contains('sunday'):
FromDate := CalcDate('<-CW+6D>', StartingDate);
else begin
Handled := false;
exit;
end;
end;
ToDate := FromDate;
Handled := true;
end;That’s all. We can try it for example in sales orders. Using “friday..next friday” we will get all orders with a posting date between this Friday and the next one (inclusive).
