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:
- Integer: This is an integer number ranging from -2,147,483,646 to +2,147,483,647
- Decimal: This is a decimal number in the range of +/- 999,999,999,999,999.99. Although it is possible to construct larger numbers, errors such as overflow, truncation, or loss of precision might occur. In addition, there is no facility to display or edit larger numbers.
- Option: This is a special instance of an integer, stored as an integer number ranging from 0 to +2,147,483,647. An option is normally represented in the body of our C/AL code as an option string. We can compare an option to an integer in C/AL, rather than using the option string. However, this is not a good practice because it eliminates the self-documenting aspect of an option field.
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:
- Boolean: A Boolean variable is stored as 1 or 0. In a C/AL code, it is programmatically referred to as True or False, but sometimes, it is referred in properties as Yes or No. Boolean variables may be displayed as Yes or No (language dependent), P or blank, or True or False.
- BigInteger: 8-byte Integer, as opposed to the 4 bytes of Integer. BigIntegers are for very big numbers (from -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807).
- Char: This is a numeric code between 0 and 65535 (hexadecimal FFFF) that represents a single 16-bit Unicode character. Char variables can operate either as text or numbers. Numeric operations can be done on Char variables. Char variables can also be defined with individual text character values. Char variables cannot be defined as permanent variables in a table; they can only be defined as working storage variables within C/AL objects.
- Byte: This is a single 8-bit ASCII character with a value ranging from 0 to 255. Byte variables can operate either as text or numbers. Numeric operations can be done on Byte variables. Byte variables can also be defined with individual text character values. Byte variables cannot be defined as permanent variables in a table, but only as working storage variables within C/AL objects.
- Action: This is a variable returned from a PAGE RUNMODAL function or RUNMODAL (Page) function that specifies what action a user performs on a page. The possible values are OK, Cancel, LookupOK, LookupCancel, Yes, No, RunObject, and RunSystem.
- ExecutionMode: This specifies the mode in which a session runs. The possible values are Debug or Standard.
The following are the data types included in String data:
- Text: This contains any string of alphanumeric characters. In a table, a Text field can be from 1 to 250 characters long. In working storage within an object, a Text variable can be any length if no length is defined. If a maximum length is defined, it must not exceed 1024. NAV 2015 does not require a length to be specified, but if we define a maximum length, it will be enforced. When calculating the 'length' of a record for design purposes (relative to the maximum record length of 8,000 bytes), the full defined field length should be counted.
- Code: Although the Help says that the length constraints for Code variables are the same as those for text variables, the C/AL Editor enforces length limits of 1 to 250 characters. All of the letters are automatically converted to uppercase when data is entered into a Code variable; any leading or trailing spaces are removed.
The following are the data types included in Date/Time data:
- Date: This contains an integer number, which is interpreted as a date ranging from January 1, 1754 to December 31, 9999. A 0D (numeral zero, letter D) represents an undefined date (stored as a SQL Server DateTime field) that is interpreted as January 1, 1753. According to the Developer and IT Pro Help that, NAV 2015 supports a Date of 1/1/0000 (presumably as a special case for backward compatibility, but it is not supported by SQL Server).
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.)
- Time: This contains an integer number, which is interpreted on a 24-hour clock, in milliseconds plus 1, from 00:00:00 to 23:59:59:999. A 0T (numeral zero, letter T) represents an undefined time and is stored as 1/1/1753 00:00:00.000.
- DateTime: This represents a combined Date and Time, stored in Coordinated Universal Time (UTC), and it always displays local time (that is, the local time on our system). DateTime fields do not support NAV Closing dates. DateTime is helpful for an application that must support multiple time zones simultaneously. DateTime values can range from January 1, 1754 00:00:00.000 to December 31, 9999 23:59:59.999, but dates earlier than January 1, 1754 cannot be entered (don't test with dates late in 9999 as an intended advance to the year 10000 won't work). Assigning a date of 0DT will yield an undefined or blank DateTime.
- Duration: This represents the positive or negative difference between two DateTime values, in milliseconds, stored as a BigInteger. Durations are automatically output in the text format as DDD days HH hours MM minutes SS seconds.
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:
- File: This refers to any standard Windows file outside the NAV database. There is a reasonably complete set of functions to allow to create, delete, open, close, read, write, and copy (among other things) data files. For example, we could create our own NAV routines in C/AL to import or export data from or to a file that had been created by some other application.
Note
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.
- Record: This refers to a single data row within a NAV table that consists of individual fields. Quite often, multiple variable instances of a Record (table) are defined in working storage to support a validation process, allowing access to different records within the table at one time in the same function.
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:
- Invoking a Report or an XMLPort from a Page or a Report
- Calling a function for data validation or processing is coded as a function in a Table or a Codeunit
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:
- OCX: This allows the definition of a variable that represents and allows access to an ActiveX or OCX custom control. Such a control is typically an external application object that we can invoke from our NAV object.
- Automation: This allows us to define a variable that we can access similar to an OCX. The application must act as an Automation Server and be registered with the NAV client or server that calls it. For example, we can interface from NAV into the various Microsoft Office products (Word, Excel, and so on) by defining them in Automation variables.
- DotNet: This allows us to define a variable for .NET Framework interface types within an assembly. It supports accessing .NET Framework type members, including methods, properties, and constructors from C/AL. These can be members of the global assembly cache or custom assemblies.
The following are the Input/Output data types:
- Dialog: This supports the definition of a simple user interface window without the use of a Page object. Typically, Dialog windows are used to communicate processing progress or allow a brief user response to a go/no-go question, though this latter use could result in bad performance due to locking. There are other user communication tools as well, but they do not use a Dialog type data item.
- InStream and Outstream: These allow us to read from and write to external files, BLOBS, and objects of the Automation and OCX 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:
- Numeric multipliers (for example, 1, 2, 3, 4, and so on)
- Alpha time units (all must be in uppercase)
- D for a day
- W for a week
- WD for day of the week, that is, from day 1 to day 7 (either in the future or in the past, but not today). Monday is day 1 and 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 tenth of the next month)
- – (minus) as in (-WD3) means the date of the previous Wednesday (which is the 3rd 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 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:
- CM is the last day of Current Month, 07/31/15
- CM + 10D is the tenth of the next month, 08/10/15
- WD6 is the next sixth day of the week, 07/11/15
- WD5 is the next fifth day of the week, 07/17/15
- CM – M + D is the end of the current month minus one month plus one day, 07/01/15
- CM – 5M is the end of the current month minus five 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 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:
- 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 or trying to decide how it works or how we might use it.
- We put some critical
OnValidatelogic 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. This 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 design 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 relative date calculations easy. These are very useful in business applications, many aspects of which are date centered.
The following data types are used for advanced functionality in NAV, sometimes supporting an interface with an external object:
- RecordID: This contains the object number and primary key of a table.
- RecordRef: This identifies a row in a table, a record. RecordRef can be used to obtain information about the table, the record, the fields in the record, and the currently active filters on the table.
- FieldRef: This identifies a field in a table; thus, it allows access to the contents of that field.
- KeyRef: This identifies a key in a table and the fields in that key.
Note
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.
- Variant: This defines variables that are typically used to interface with Automation and OCX objects. Variant variables can contain data of various C/AL data types to pass them to an Automation or OCX object as well as external Automation data types that cannot be mapped to C/AL data types.
- TableFilter: For variables which can only be used for setting security filters from the Permissions table.
- Transaction Type: This has optional values of UpdateNoLocks, Update, Snapshot, Browse, and Report that define SQL Server behavior for a NAV Report or XMLport transaction from the beginning of the transaction.
- BLOB: This can contain either specially formatted text, a graphic in the form of a bitmap, or other developer-defined binary data up to 2 GB in size. The term Binary Large Object (BLOB). BLOBs can only be included in tables and not used to define working storage Variables. Refer to Developer and IT Pro Help for additional information.
- BigText: This can contain large chunks of text up to 2 GB in size. BigText variables can only be defined in the working storage within an object, but they cannot be included in tables. BigText variables cannot be directly displayed or seen in the debugger. There is a group of special functions that can be used to handle BigText data. Refer to Developer and IT Pro Help for additional information.
- GUID: This is used to assign a unique identifying number to any database object. Globally Unique Identifier (GUID), a 16-byte binary data type that is used for unique global identification of records, objects, and so on. GUID is generated by an algorithm developed by Microsoft.
- TestPage: This is used to store a test page, which is a logical representation of a page that does not display a user interface. Test pages are used for NAV application testing, using the automated testing facility that is part of NAV.
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:
