Any time you have to enter a DateFormula, such as Payment Terms or other places where a value such as “30D” would work, there are a few easy ways you can get the beginning or end of the current Year, Quarter, Month, or even week. Simply use:
| Period Type | Beginning | Ending |
| Year | -CY | CY |
| Quarter | -CQ | CQ |
| Month | -CM | CM |
| Week | -CW | CW |
For users, this means if you wanted the Payments of Customers always due at the end of the current month, you’d setup a new Payment Term with a Formula of ‘CM’.
For developers, this means you can quickly get the current year into a filter like so:
DateRec.SETRANGE("Period Start",CALCDATE('-CY',WORKDATE),CALCDATE('CY',WORKDATE));
We’ll dive into some of the stranger uses of DateFormula in a later post, including how to factor in Weekdays, or the xth day of the month/week.
You can test out the above examples with this object. Save it as as text file, then import, compile, and run.
OBJECT Form 80001 Closing Dates Math Example
{
OBJECT-PROPERTIES
{
Date=04/20/11;
Time=[ 3:28:41 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
Width=8000;
Height=8000;
}
CONTROLS
{
{ 1000000002;TextBox;3630 ;220 ;1700 ;440 ;CaptionML=ENU=Date Basis;
SourceExpr=MyDates[1];
OnValidate=BEGIN
Calc;
END;
}
{ 1000000003;Label ;220 ;220 ;3300 ;440 ;ParentControl=1000000002 }
{ 1000000004;TextBox;3630 ;1100 ;1700 ;440 ;CaptionML=ENU=-CY;
SourceExpr=MyDates[2] }
{ 1000000005;Label ;220 ;1100 ;3300 ;440 ;ParentControl=1000000004 }
{ 1000000006;TextBox;3630 ;1650 ;1700 ;440 ;CaptionML=ENU=CY;
SourceExpr=MyDates[3] }
{ 1000000007;Label ;220 ;1650 ;3300 ;440 ;ParentControl=1000000006 }
{ 1000000008;TextBox;3630 ;2310 ;1700 ;440 ;CaptionML=ENU=-CM;
SourceExpr=MyDates[4] }
{ 1000000009;Label ;220 ;2310 ;3300 ;440 ;ParentControl=1000000008 }
{ 1000000010;TextBox;3630 ;2860 ;1700 ;440 ;CaptionML=ENU=CM;
SourceExpr=MyDates[5] }
{ 1000000011;Label ;220 ;2860 ;3300 ;440 ;ParentControl=1000000010 }
{ 1000000020;TextBox;3630 ;4070 ;1700 ;440 ;CaptionML=ENU=CW;
SourceExpr=MyDates[7] }
{ 1000000021;Label ;220 ;4070 ;3300 ;440 ;ParentControl=1000000020 }
{ 1000000024;TextBox;3630 ;3520 ;1700 ;440 ;CaptionML=ENU=-CW;
SourceExpr=MyDates[6] }
{ 1000000025;Label ;220 ;3520 ;3300 ;440 ;ParentControl=1000000024 }
}
CODE
{
VAR
MyDates@1000000000 : ARRAY [10] OF Date;
PROCEDURE Calc@1000000000();
BEGIN
MyDates[2] := CALCDATE('-CY',MyDates[1]);
MyDates[3] := CALCDATE('CY',MyDates[1]);
MyDates[4] := CALCDATE('-CM',MyDates[1]);
MyDates[5] := CALCDATE('CM',MyDates[1]);
MyDates[6] := CALCDATE('-CW',MyDates[1]);
MyDates[7] := CALCDATE('CW',MyDates[1]);
END;
BEGIN
END.
}
}
To read the (very) technical explanation, here’s the MSDN documentation on DateFormula.
