书名：Programming Microsoft Dynamics NAV（Fifth Edition）
作者名：Mark Brummel David A. Studebaker Christopher D. Studebaker
本章字数：1713字
更新时间：2021-07-02 23:42:11

DateFormula

DateFormula provides for the definition and storage of a simple, but clever, set of constructs to support the calculation of runtime-sensitpe dates. A DateFormula data type is stored in a non-language dependent format, thus supporting multi-language functionality. A DateFormula data type is a combination of:

Numeric multipliers (for example 1, 2, 3, 4, and so on)
Alpha time units (all must be uppercase)
D for a day
W for a week
WD for day of the week, that is day 1 through day 7 (either in the future or in the past, not today), Monday is day 1, Sunday is day 7
M for calendar month
Y for year:
- CM for current month, CY for current year, CW for current week
Math symbols interpretation:
- + (plus) as in CM + 10D means the current month end plus 10 days (in other words, the 10th of next month)
- - (minus) as in (-WD3) means the date of the previous Wednesday (the third day of the past week)
Positional notation (D15 means the 15th day of the month and 15D means 15 days)

Payment terms for invoices support the full use of DateFormula. All DateFormula results are expressed as a date based on a reference date. The default reference date is the system date, not the work date.

Here are some sample DateFormulas and their interpretations (displayed dates are based on the US calendar) with a reference date of July 10, 2017, a Friday:

CM, the last day of the current month, 07/31/15
CM + 10D, the 10th of next month, 08/10/15
WD6, the next sixth day of the week, 07/11/15
WD5, the next fifth day of the week, 07/17/15
CM - M + D, the end of the current month minus one month plus one day, 07/01/15
CM - 5M, the end of the current month minus fpe months, 02/28/15

Let us take the opportunity to use the DateFormula data type to learn a few NAV development basics. We will do so by experimenting with some hands-on evaluations of several DateFormula values. We will create a table to calculate dates using DateFormula and Reference Date.

Go to Tools | Object Designer | Tables. Click on the New button and define the fields shown in the following screenshot. Save it as Table 50009, named Date Formula Test. After we've finished this test, we will save the table for some later testing:

Now we will add some simple C/AL code to our table so that, when we enter or change either the Reference Date or the DateFormula data type, we can calculate a new result date.

First, access the new table via the Design button, then go to the global variables definition form through the View menu option, the sub-option C/AL Globals, and finally, choose the Functions tab. Type in our new function name as CalculateNewDate on the first blank line, as shown in the following screenshot and then exit (by means of Esc key) from this form back to the list of data fields:

From the Table Designer form displaying the list of data fields, either press F9 or click on the C/AL Code icon:

This will take us to the following screen, where we see all the field triggers, plus the trigger for the new function that we've just defined. The table triggers are not visible, unless we scroll up to show them. Notice that our new function was defined as a LOCAL function. This means that it cannot be accessed from another object unless we change it to a Global function:

Because our goal now is to focus on experimenting with the DateFormula data type, we will not go into detail explaining the logic we are creating. The logic we're going to code follows:

When an entry is made (new or changed) in either the Reference Date field or in the Date Formula to Test field, invoke the CalculateNewDate function to calculate a new Result Date value based on the entered data.

First, we will create the logic within our new function, CalculateNewDate(), to evaluate and store a Date Result based on the DateFormula and Reference Date data type that we enter into the table.

Just copy the C/AL code exactly as shown in the following screenshot, exit, compile, and save the table:

If you get an error message of any type when you close and save the table, you probably have not copied the C/AL code exactly as it is shown in the screenshot (also shown in the following block of code for ease of copying):

  CalculateNewDate; 
  "Date Result" := CALCDATE("Date Formula to Test","Reference Date  
    for Calculation");

This code will cause the CalculateNewDate() function to be called via the OnValidate trigger when an entry is made in either the Reference Date for Calculation or the Date Formula to Test fields. The function will place the result in the Date Result field. The use of an integer value in the redundantly named Primary Key field allows us to enter any number of records into the table (by manually numbering them 1, 2, 3, and so forth).

Let's experiment with several different date and date formula combinations. We will access the table via the Run button. This will cause NAV to generate a default format page and run it in the Role Tailored Client.

Enter a primary key value of 1 (one). In Reference Date for Calculation, enter either an upper or lower case T for Today, the system date. The same date will appear in the Date Result field, because at this point there is no Date formula entered. Now enter 1D (the numeral 1 followed by uppercase or lowercase letter D, C/SIDE will take care of making it upper case) in the Date Formula to Test field. We will see that the Date Result field contents are changed to be one day beyond the date in the Reference Date for Calculation field.

Now for another test entry, start with a 2 in the Primary Key field. Again, enter the letter T (for Today) in the Reference Date for Calculation field, and enter the letter W (for Week) in the Date Formula to Test field. We will get an error message telling us that our formulas should include a number. Make the system happy and enter 1W. We'll now see a date in the Date Result field that is one week beyond our system date.

Set the system's Work Date to a date in the middle of a month (remember, we discussed setting the Work Date in Chapter 1, Introduction to NAV 2017). Start another line with the number 3 as the primary key, followed by a W (for Work Date) in the Reference Date for Calculation field. Enter cm (or CM or cM or Cm, it doesn't matter) in the Date Formula to Test field. Our result date will be the last day of our Work Date month. Now enter another line using the Work Date, but enter a formula of -cm (the same as before, but with a minus sign). This time our result date will be the first day of our Work Date month. Notice that the DateFormula logic handles month end dates correctly, even including leap years. Try starting with a date in the middle of February 2016 to confirm that:

Enter another line with a new primary key. Skip over the Reference Date for Calculation field and just enter 1D in the Date Formula to Test field. What happens? We get an error message stating that You cannot base a date calculation on an undefined date. In other words, NAV cannot make the requested calculation without a Reference Date data type. Before we put this function into production, we want our code to check for a Reference Date data type before calculating. We could default an empty date to the System Date or the Work Date and avoid this particular error.

The preceding and following screenshots show different sample calculations. Build on these sample calculations and then experiment more on your own:

We can create a variety of different algebraic date formulae and get some very interesting and useful results. One NAV user business has due dates for all invoices of 10th of the next month. The invoices are dated on the dates they are actually printed, at various times throughout the month. But by using DateFormula of CM + 10D, each invoice due date is always automatically calculated to be the 10th of the next month.

Don't forget to test with WD (weekday), Q (quarter), and Y (year) as well as D (day), W (week), and M (month). For our code to be language independent, we should enter the date formulae with < > delimiters around them (example: <1D+1W>). NAV will translate the formula into the correct language codes using the installed language layer.

Although our focus for the work we just completed was the Date Formula data type, we've accomplished a lot more than simply learning about that one data type:

We created a new table just for the purpose of experimenting with a C/AL feature that we might use. This is a technique that comes in handy when we are learning a new feature, trying to decide how it works or how we might use it.
We put some critical OnValidate logic in the table. When data is entered in one area, the entry is validated and, if valid, the defined processing is done instantly.
We created a common routine as a new LOCAL function. That function is then called from all the places to which it applies.
We did our entire test with a table object and a default tabular page that is automatically generated when we run a table. We didn't have to create a supporting structure to do our testing. Of course, when we are designing a change to a complicated existing structure, we will have a more complicated testing scenario. One of our goals will always be to simplify our testing scenarios, both to minimize the setup effort and to keep our test narrowly focused on the specific issue.
Finally, and most specifically, we saw how NAV tools make a variety of relatpe date calculations easy. These are very useful in business applications, many aspects of which are date centered.