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.
[DDET For developers, some sample code]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. } }
[/DDET]
To read the (very) technical explanation, here’s the MSDN documentation on DateFormula.
[…] Bron : Jeremy.Vyska.Info Lees meer… […]