We are going to segregate the data types into several groups. We will first look at Fundamental data types and then at Complex data types.
Fundamental data types are the basic components from which the complex data types are formed. They are grouped into Numeric, String, and Date/Time data types.
Just like other systems, NAV supports several numeric data types. The specifications for each NAV data type are defined for NAV, independent of the supporting SQL Server database rules. However, some data types are stored and handled somewhat differently from a SQL Server point of view than the way they appear to us as NAV developers and users. For more details on the SQL Server-specific representations of various data elements, refer to the Developer and IT Pro Help. Our discussion will focus on NAV representation and handling for each data type.
The various numeric data types are as follows:
An option string is a set of choices listed in a comma-separated string, one of which is chosen and stored as the current option. Since the maximum length of this string is 250 characters, the practical maximum number of choices for a single option is less than 125 characters. The currently selected choice within the set of options is stored in the option field as the ordinal position of that option within the set. For example, selection of an entry from the option string of red, yellow, and blue would result in the storing of 0 (red), 1 (yellow), and 2 (blue). If red were selected, 0 would be stored in the variable and if blue were selected, 2 would be stored. Quite often, an option string starts with a blank to allow an effective choice of "none chosen". An example of this (blank, Hourly, Daily,…) is as follows:
The following are the data types included in String data:
The following are the data types included in Date/Time data:
A date constant can be written as the letter D preceded by either six digits in the format MMDDYY or eight digits as MMDDYYYY (where M = month, D = day, and Y = year). For example, 011915D or 01192015D both represent January 19, 2015. Later, in DateFormula, we will find D interpreted as day, but here the trailing D is interpreted as the date (data type) constant. When the year is expressed as YY rather than YYYY, the century portion (in this case, 20) is 20 if the two digit year is from 00 to 29, or 19 if the year is from 30 through 99.
NAV also defines a special date called the Closing date, which represents the point in time between one day and the next. The purpose of a closing date is to provide a point at the end of a day, after all of the real date- and time-sensitive activity is recorded—the point when accounting closing entries can be recorded.
Closing entries are recorded, in effect, at the stroke of midnight between two dates—this is the date of closing accounting books, and it is designed so that one can include or exclude, at the user's option, closing entries in various reports. When sorted by date, the closing date entries will get sorted after all normal entries for a day. For example, the normal date entry for December 31, 2015 would display as 12/31/15 (depending on the date format masking), and the closing date entry would display as C12/31/15. All of the C12/31/15 ledger entries would appear after all normal 12/31/15 ledger entries. The following screenshot shows two 2014 closing date entries mixed with normal entries from December 2015 and January through April 2015. (This data is from the Cronus demo. The 2014 Closing entries have an "Opening Entry" description, which shows that these were the first entries for the demo data in the respective accounts. This is not a normal set of production data.)
Each complex data type consists of multiple data elements. For ease of reference, we will categorize them into several groups of similar types.
The following data types are in the data structure group:
With the three-tier architecture of NAV 2015, business logic runs on the server and not the client. We need to keep this in mind any time we refer to local external files, because they will be on the server by default. Use of Universal Naming Convention (UNC) paths can make this easier to manage.
Page, Report, Codeunit, Query, and XMLPort, each represents an object data type. Object data types are used when there is a need to refer to an object or a function in another object. Examples:
The following are Automation data types (these are not supported by the NAV Web client.) OCX and Automation data types are supported in NAV 2015 for backward compatibility only:
The following are the Input/Output data types:
DateFormula provides for the definition and storage of a simple, but clever, set of constructs to support the calculation of runtime-sensitive dates. A DateFormula is stored in a nonlanguage dependent format, thus supporting multilanguage functionality. A DateFormula is a combination of:
Payment Terms for Invoices support 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 and 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, 2015, a Friday:
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 Dates.
To do this, navigate to Tools | Object Designer | Tables. Then, 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 are done with this test, we will save this 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, 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 C/AL Globals sub-option, 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 the Esc key) from this form back to the list of data fields:
From the Table Designer form that displays 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 can see all of the field triggers plus the trigger for the new function that we just defined. The table triggers will not be visible, unless we scroll up to show them. Note 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.
Since our goal now is to focus on experimenting with the DateFormula, we will not go into detail and explain the logic of what we are creating. The logic that we're going to code is as follows:
First, you need to create the logic within our new function, CalculateNewDate()
, to evaluate and store a Date Result based on the DateFormula and Reference Date that you 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 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 and the system date. The same date will appear in the Date Result field because at this point, no Date Formula has been entered. Now, enter 1D
(number 1 followed by uppercase or lowercase D (C/SIDE will make it uppercase) 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 will 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). 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. Note that the DateFormula logic handles month end dates correctly, including a leap year. Try starting with a date in the middle of February 2016 to confirm this. The following screen shows the Date Formula Test window:
Now, 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. So, what happens when you do this? 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. Before we put this function into production, we want our code to check for a Reference Date 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 and then experiment. We can create a variety of different algebraic date formulae and get some very interesting results. One NAV user has due dates on Invoices for the tenth of the next month. Invoices are dated at various times during the month than they are actually printed. By using the DateFormula of CM + 10D
, the due date is always automatically calculated to be the tenth 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 (for 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:
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.The following data types are used for advanced functionality in NAV, sometimes supporting an interface with an external object:
Since the specific record, field, and key references are assigned at runtime, RecordRef, FieldRef, and KeyRef are used to support logic which can run on tables that are not specified at design time. This means that one routine built on these data types can be created to perform a common function for a variety of different tables and table formats.
About forty percent of the data types can be used to define the data that is either stored in tables or in working storage data definitions (that is, in a Global or Local data definition within an object). Two data types, BLOB and TableFilter, can only be used to define table-stored data, but not working storage data. About sixty percent of the data types can only be used for working storage data definitions.
The following list shows which data types can be used for table (persisted) data fields and which ones can be used for working storage (variable) data: