Data Types in Microsoft® Dynamics™ NAV

Exclusive offer: get 50% off this eBook here
Programming Microsoft® Dynamics™ NAV

Programming Microsoft® Dynamics™ NAV — Save 50%

Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook

$35.99    $18.00
by David A. Studebaker | October 2007 | .NET Microsoft

The secret of getting ahead is getting started. The secret of getting started is breaking your complex overwhelming tasks into small manageable tasks, and then starting on the first one—Mark Twain

In this article, we will cover the data types that you are most likely to use. We will also take an overview of the others. In addition, we will also cover field classes, which are where the special features are enabled.

As you know, design of an application starts with the data. The data design depends on the types of data that your development tool set allows you to use. Since NAV is designed specifically to develop financially oriented business applications, the NAV data types are financially and business oriented, and also have some special features that make it easier to design and develop typical business applications. Furthermore, these same special features can make your applications run faster.

In this article, we will cover the data types that you are most likely to use. We will also take an overview of the others. In addition, we will also cover field classes, which are where the special features are enabled.

Fields

A field is the basic element of data definition in NAV—the "atom" in the structure of a system. The mechanical definition of a field consists of its number, its description (name), and its data type (and, of course, any parameters required for its particular data type). From a logical point of view, a field is also defined by its Properties and the C/AL code contained in its Triggers.

Field Properties

The specific properties that can be defined for a field partially depend on the data type. First we will review the universal field properties. Then we will review the properties that are data-type dependent plus some other field properties. You can check out the remaining properties by using Help within the Table Designer.Fields

You can access the properties of a field while viewing the table in Design mode, by highlighting the field line whose properties you wish to examine and clicking on the Properties icon or pressing Shift + F4. All the property screenshots in this section are obtained in this way for fields within the standard Customer table. As we review various field properties, you will learn more if, using the Object Designer, you follow along in your NAV system. Poke around and explore different properties and the values they can have. Use the Field Help function liberally and read the help for various properties.

The property value enclosed in < > (less than sign, greater than sign), is the default value for that property. When you set a property to any other value, < and > should not be present unless they are supposed to be the part of the property value (e.g. part of a Text string value).All data types have the following properties:

Property

Property Description

Field No.

Identifier for the field within the table object

Name

Label by which code references the field. The name can be changed at any time and NAV will automatically ripple that change throughout the code

Caption and Caption ML

Work similarly as named table properties

Description

Used for internal documentation only

Data Type

Identifies what kind of data format applies to this field (e.g. Integer, Date, Code, Text, etc.)

Enabled

Determines if the field is activated for data handling or not. This property defaults to yes and is rarely changed

 

The following screenshot shows the BLOB properties for the Picture Field in the Customer table:

Data Types in Microsoft® Dynamics™ NAV

This set of properties, for fields of the BLOB data type, is the simplest set of field properties. After the properties that are shared by all data types, appear the BLOB-specific properties—SubType and Owner:

      
  1. SubType: This defines the type of data stored in the BLOB. The three  sub-type choices are Bitmap (for bitmap graphics), Memo (for text data), and  User-Defined (for anything else). User-Defined is the default value.
  2.   

  3. Owner: The usage is not defined.
  4.  

The available properties of Code and Text fields are quite similar to one another. The following are some common properties between the two as shown in the screenshot overleaf:

     
  • DataLength: This specifies how many characters long the data field is.
  • InitValue: This is the value that the system should supply as a default when  the system actively initializes the field.
  • AltSearchField: This allows definition of an alternative field in the same  table to be searched for a match if no match is found on a lookup on this datastyle="width: 761px; height: 446px;"  item. For example, you might want to allow customers to be looked up eitherstyle="width: 761px; height: 446px;"  by their Customer No. or by their Phone No. In that case, in the No. field  properties you would supply the Phone No. field name in the AltSearchField  field. Then, when a user searches in the No. field, NAV will first look for  a match in the No. field and, if it is not found there, it will then search  the Phone No. field for a match. Use of this property can save you a lot of  coding, but make sure both fields have high placement in a key so the lookup  will be speedy.
  • Editable: This is set to No when you don't want to allow a field to ever be  edited for example, if this is a computed or assigned value field that the user  should not change.
  • NotBlank, Numeric, CharAllowed, DateFormula, and ValuesAllowed: All  these support placing constraints on the specific data that can be entered into  this field.
  • TableRelation and ValidateTableRelation: These are used to  control referencing and validation of entries against another table.  (TestTableRelation is an infrequently used property, which controls whether  or not this relationship should be tested during a database validation test.)

Data Types in Microsoft® Dynamics™ NAV

Let us take a look at the properties of couple more Data types, Integer and Decimal. You may find it useful to explore them on your own as well. Specific properties related to the basic numeric content of these data types are as follows and are also shown in the following screenshot:

  • DecimalPlaces: This sets the number of decimal places in a Decimal  data item.
  • BlankNumbers, BlankZero, and SignDisplacement: All these can be used to  influence the formatting and display of the data in the field.
  • MinValue and MaxValue: These can constrain the range of data values allowed.
  • AutoIncrement: This allows setting up of one field in a table to automatically  increment for each record entered. This is almost always used to support  automatic updating of a field used as the last field in a primary key, enabling  creation of a unique key.

Data Types in Microsoft® Dynamics™ NAV

The field properties for an Integer field with a FieldClass property of FlowField are similar to those of a field with a FieldClass property of Normal. The differencesstyle="width: 761px; height: 446px;"relate to the fact that the field does not actually contain data but holds the formula by which the displayed value is calculated, as shown in the following screenshot overleaf.

Note the presence of the CalcFormula property and the absence of the AltSearchField, AutoIncrement, and TestTableRelation properties. Similar differences exist for FlowFields of other data types.

Data Types in Microsoft® Dynamics™ NAV

The properties for an Option data type, whose properties are shown in the following screenshot, are essentially like those of the other numeric data types, but with a datatype-specific set of properties as described below:

  • OptionString: This spells out the text interpretations for the stored integer  values contained in Option data type fields.
  • OptionCaption and OptionCaptionML: These serve the same captioning  and multi-language purposes as other caption properties.

Data Types in Microsoft® Dynamics™ NAV

The properties defined for FlowFilter fields, such as Date Filter in the following screenshot overleaf, are similar to those of Normal data fields. Take a look at the Date Filter field (a Date FlowFilter field) and the Global Dimension 1 Filter field (a Code FlowFilter field) in the Customer table. The Date Filter field property looks similar to a Normal FieldClass field.

Programming Microsoft® Dynamics™ NAV Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook
Published: October 2007
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

 

 

The Global Dimension 1 Filter field property values are different than those of the Date Filter because of the data type and its attributes rather than the fact that this is a FlowFilter field.

Data Types in Microsoft® Dynamics™ NAV

Field Numbering

The number of each field within its parent table object is the unique identifier that NAV uses internally to identify that field. You can easily change a field number when you are initially defining a table layout. But after you have a number of other objects (e.g. forms, reports, or codeunits) referencing the fields in a table, it becomes challenging (and therefore inefficient, sometimes to the point of almost impossible) to change the numbers of one or more fields. Therefore, you should plan ahead to minimize any need to renumber the fields. You should also be cautioned that, although you can physically delete a field and then reuse its field number for a different purpose, doing so is likely to cause you much grief.

You must take care about how the field numbers affect your users because the numeric sequence of fields within the table controls the sequence of the field names when they are displayed in field selection lists. These field selection lists are presented when a user or developer constructs a data Filter, does Form or Report Designer field selection, views a zoom display or creates a default form or report. If the fields in a table are not in relatively logical sequence, or fields with a similar purpose are not grouped, the system will be harder to understand and thereforeharder to use.

Unfortunately, that criticism could be made about the field sequence structure of some of the standard system's principle Master tables (e.g. Customer, Vendor, and Item). This has happened over a decade of changes and functional expansion. During that time, the original field numbers have largely remained unchanged in support of backward compatibility. At the same time, new related fields have been added in less than ideally related field number sequences. The result is a list of fields presented to users in a sequence that follows very few logical rules.

For new fields that you add to tables which are part of the standard NAV product, the new field numbers must be in the 50000 to 99999 number range, unless you have been explicitly licensed for another number range. Field numbers for fields in new tables that you create may be anything from 1 to 999,999,999 (without the commas).

Renumbering a Field

What if, after considering the layout of the fields in the Member table, it looks like it would make more sense to have the Business Class field sequenced after the NAV Involvement Since field (admittedly a very subjective and arbitrary design decision). Since we have not yet tied these fields to other objects in a complicated way, maybe it's still easy to do the renumbering.

Before we try the next experiment, make sure that you have data in your Member table for at least one Member. For this test, make sure the Business Class field is filled in. Now, open the Member table with the Designer, then renumber field 1010 (Business Class) to 1025. Exit, save, and compile the table. Since you have data in field 1010, you will get a message similar to the following screenshot:

Data Types in Microsoft® Dynamics™ NAV

In the screenshot, NAV is explaining that you cannot make this change. Why not? Because, C/SIDE is checking the consistency of the definition of the stored data of that field in the table definition and that checking is based on the field number, not the field name. You are not allowed to change the field numbers when data is present in the field.

This particular situation comes up regularly when we are enhancing existing routines. For example, we want to reduce the size of a text field for some reason. If the data already in the table has any values that would exceed the new smaller field size, NAV will not allow us to make the change until we resolve the inconsistency. We can expand the field, because that will not create any inconsistency.

NAV acts like the understanding parent of a teenager. It gives us enough freedom to do lots of creative things on our own, but every now and then it warns us and keeps us from hurting ourselves.

Changing the Data Type of a Field

The larger issue here is the question of how to change the Data Type of a field. This change may be the result of a field renumbering, as we just saw in our experiment or it could be the result of an enhancement. Of course, if the change is at our discretion, we might decide simply not to do it. But what if we have no choice? For example, perhaps we had originally designed the Postal Zone field as an Integer to only handle US ZIP Codes, which are numeric. Then later we decide to generalize and allow postal codes for all countries. In that case, we must change our data field from integer to code, which allows all numerals and upper case letters.

In this case, how do we solve the data definition—data content inconsistency caused by the change? We have a couple of choices. The first option, which could work in our C/ANDL database because we have very little data and it's just test data, is simply to delete the existing data, proceed with our change, then restore the data through keyboard entry.

When dealing with a significant volume of production data (more typical when changing a production system), you must take a more conservative approach. Of course, more conservative means more work.

Let us look at the steps required for a common example of changing the data type because of a design change. In this example, we will assume that the field 110 Post Code was defined as Data Type of Integer and we need to change it to Data Type of Code, Length 20. The steps are as follows:

  1. Create a new, temporary field 111 named Temp Post Code, data type Code, and Length 20. Any allowable field number and unique name would work.
  2. Copy the data from the original field 110 Post Code into the new temporary field 111, deleting the data from field 110 as you go, using a Processing Only report object created just for this purpose.
  3. Redefine field 110 to new Data Type.
  4. Copy the data from the temporary field 111 back into the redefined field 110, deleting the data from field 111, using a second Processing Only report object created just for this purpose.
  5. Delete the temporary field 111.

If we had to renumber the fields, we would essentially have to do the same thing as just described, for each field. Whenever you attempt a change and see the earlier message, you will have to utilize the procedure just described.

What a lot of work just to make a minor change in a table! Hopefully, this convinces you of the importance of carefully considering how you define fields and field numbers initially. By the way, this is exactly the sort of process that Upgrade Data Conversions go through to change the field structure of a table in the database to support added capabilities of the new version.

Field Triggers

To see what field triggers are, let us look at our Table 50000 Member. Open the table in Design mode, highlight the Member ID field and press F9. The window shown in the following screenshot will appear:

Data Types in Microsoft® Dynamics™ NAV

Each field has two triggers, the OnValidate() trigger and the OnLookup() trigger, which function as follows:

  • OnValidate(): The C/AL code in this trigger is executed whenever an entry is made by the user. It can also be executed under program control through  use of the VALIDATE function (which we will discuss later).
  • OnLookup(): The C/AL code in this trigger is executed in place of the system's default Lookup behavior, even if the C/AL code is only a comment. Lookup behavior can be triggered by pressing F6 or by clicking on the lookup arrow in a field as shown in following screenshot:

Data Types in Microsoft® Dynamics™ NAV

If the field's TableRelation property refers to a table and that table has a default LookupFormID defined, then the default behavior for that Lookup form is to display that form, to allow selection of an entry to be stored in this field. You may choose to override that behavior in a special case by coding different behavior.

Be careful. Any entry whatsoever in the body of an OnLookup() trigger will eliminate the default behavior. This is true even if the entry is only a comment and there is no executable code present. A comment line could make an intended default lookup not occur

Some Data Structure Examples

Some good examples of tables in the standard product to review for particular features are:

  • Table 18—Customer, for a variety of Data Types and Field Classes. This table contains some fairly complex examples of C/AL code in the table Triggers. A wide variety of field property variations can be seen in this table as well.
  • Tables 21 and 32—Cust. Ledger Entry and Item Ledger Entry, for a variety of complex secondary key formats.
  • Table 37—Sales Line, for the SumIndexFields attached to various keys.
  • Table 50—Accounting Period, has a couple of very simple examples of Field OnValidate trigger C/AL code. For slightly more complex examples, take a look at Table 167—Job. For much more complex examples, you can look at almost all of the master tables such as Customer, Vendor, Item, etc.

You can find all the tables at Tools | Object Designer, by clicking on Tables.

More Definitions

Let's get some more basic definitions for NAV available, so we can make sure that our terminology is clear.

Data Type: This describes/defines what kind of data can be held in this storage element, whether it be numeric (e.g. integer, decimal), text, binary, time, date, Boolean, and so forth. The data type defines the constraints that are placed on what the contents of a data element can be, defines the functions in which that data element can be used, and defines what the results of certain functions will be.

Fundamental (Simple) data type: This has a simple structure consisting of a single value at one time, e.g. a number, string of text, character, etc.

Complex data type: This has a structure made up of or relating to simple data types, e.g. records, program objects such as Forms or Reports, BLOBs, DateFormulas, an external file, an indirect reference variable, etc.

Constant: This is a data element explicitly specified in the code by value, not modifiable 'on the fly', known in some circles as 'hard wired' data. All simple data types can be represented by constants.

Variable: This is a data element that can have a value assigned to it dynamically, as the program runs. Except for special cases, a variable will be of a single, unchanging, and specific data type.

Variable Naming

Variable names in NAV can either be global (defined across the breadth of an object) or local (defined only within a single function). Variable names must be unique within their sphere of definition. There must not be any duplication between global and local names. Even though the same local name can be used in more than one function within the same object, doing so can confuse the compiler. Therefore, you should make your working variable names unique within the object.

Variable names in NAV are not case sensitive. They are limited to 30 characters in length and can contain most of the standard ASCII character set. Uniqueness includes not duplicating reserved words or system variables. That is an interesting challenge as there is no comprehensive published list of the reserved words available. A good guideline is to avoid using as a variable name any word that appears in either the C/SIDE Help or the Application Designer's Guide as an UPPER CASE word.

There is a 30-character length limit on variable names. Variable names can contain all ASCII characters except for control characters (ASCII values 0 to 31 and 255) and the asterisk (*, ASCII value 42). Note that the compiler won't tell you an asterisk cannot be used in a variable name. It is also a very good idea to avoid using the question mark (?, ASCII value 63).

The first character must be a letter A to Z (upper or lower case) or an underscore (_, ASCII value 95). It can be followed by any combination of the legal characters. If you use any characters other than the alphabet, numerals, and underscore, you must surround your variable name with double quotes (e.g. "cust list", which contains an embedded space, or "No." which contains a period). While the Application Designer's Guide doesn't tell you that you can't use a double quote character within a variable name, common sense and the compiler tell you not to do so.

Data Types

We are going to segregate the data types into relatively obvious groupings. Overall we will first look at Fundamental (aka simple) data types, and then Complex data types. Within fundamental data types, we will consider Numeric, String, and Time Types, while in complex data types we will look at Data Items, Data Structures, Objects, Automation, Input/Output, References, and others.

Fundamental Data Types

Fundamental data types are the basics from which the complex data types are formed. They are grouped into Numeric, String, and Time Data Types.

Numeric Data

Just like other systems, NAV allows several types of numeric data types. What numeric data types you may use and how you may use them will be dependent on whether you are designing your code to run only on the C/SIDE database, only on the SQL Server database, or to be database independent. If the C/SIDE database approach works on SQL Server, then that is the database-independent approach. For details on the SQL Server-specific representations of various data elements, you can refer to the Application Designer's Guide documentation. The various numeric data types are as follows:

  • Integer: An integer number ranging from -2,147,483,648 to +2,147,483,647.
  • Decimal: A decimal number ranging from -1063 to +1063 stored in memory with 18 significant digits.
  • Option: A special instance of an integer, stored as an integer number ranging from -2,147,483,548 to +2,147,483,547 (we have not identified any instances of the negative values being used for options). An option is normally represented in the body of your C/AL code as an option string. You can compare an option to an integer in C/AL rather than using the option string, but that 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. The currently selected  choice within the set of options is stored as the ordinal position of that option within the set. For example, selection of an entry from the option  string of red, yellow, 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.
  • Boolean: These are stored as 1 or 0, programmatically referred to as True orFalse, but displayed as Yes or No.
  • Binary: This is just what its name indicates, binary data. There are limited tools available to deal with binary data in NAV but, with persistent effort, itcan be done.
  • BigInteger: 8-byte Integer as opposed to the 4 bytes of Integer. BigIntegers are for very big numbers.
  • Char: A numeric code between 0 and 256 representing an ASCII character. To some extent Char variables can operate either as text or as numeric. Numericoperations can be done on Char variables. Char variables can be defined withcharacter values. Char variables cannot be defined as permanent variables ina table, but only as working variables within C/AL objects.

String Data

The following are the data types included in String Data:

  • Text: This contains any string of alphanumeric characters from 1 to 250 characters long. The actual physical string in memory consists of a length byte plus the data. Thus an empty text field is only 1 byte long, providing the efficient use of space. When calculating the 'length' of a record for design purposes (relative to the maximum record length of 4096 characters), the full defined field length should be counted.
  • Code: This contains any string of alphanumeric characters from 1 to 250 characters long. All letters are automatically converted to uppercase when entered. All numeric entry is automatically right justified on display, otherwise the entry display is left justified. SQL Server applies a somewhat different set of sorting rules for code fields than does the C/SIDE database.

Time Data

The following are the data types included in Time Data:

  • Date: This contains an integer number, which is interpreted as a date ranging from January 1, 0 to December 31, 9999. A 0D (numeral zero, letter dee) represents an undefined date.
    A date constant can be written as a 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 011908D or 01192008D, both representing January 19, 2008. Later, in DateFormula, we will find D interpreted as Day, but here the trailing D is interpreted as date (data type) constant.
  • NAV also defines a special date called a "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 real date- and time-sensitive activity is recorded, when accounting "closing" entries can be recorded.
    Closing entries are recorded, in effect, at the stroke of midnight between two dates i.e. this is the date of closing of accounting books, designed so that one can include or not include, 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, 2006 would display as 12/31/06 (depending on your date format masking), and the closing date entry would display as C12/31/06. All C12/31/06 ledger entries would appear after all normal 12/31/06 ledger entries. The following screenshot shows some closing date entries from 2003 and 2004.

    Data Types in Microsoft® Dynamics™ NAV

  • Time: This contains an integer number, which is interpreted on a 24 hour clock, in milliseconds, from 00:00:00 to 23:59:59:999. A 0T (numeral zero, letter tee) represents an undefined time.
  • DateTime: This represents a combined Date and Time, stored in Coordinated Universal Time (UTC) and always displays local time (i.e. the local time on your system). DateTime fields do not support NAV "Closing Date". DateTime values can range from January 1, 1754 00:00:00.000 to December 31, 9999 23:59:59.999. An undefined DateTime is 0DT.
  • Duration: This represents the positive or negative difference between two DateTime values, in milliseconds.

Complex Data Types

Complex Data Types are constructed from the Fundamental Data Types. They are grouped into Data Item, Date Formula, Data Structure, Objects, Automation, Input/Output, References, and Other.

Data Item

The data types included in Data Item are as follows:

  • BLOB: This can contain either a graphic in the form of a bitmap or specially formatted text or other developer-defined binary data, up to 2 GB in size. The term BLOB stands for Binary Large OBject. BLOBs can be included in tables.
  • BigText: This can contain large chunks of text, up to 2GB in size. For working storage, BigText data are not included in tables for permanent storage, BigText data must be moved to BLOB variables. BigText variables cannot be directly displayed or seen in the debugger. There is a group of functions that can be used to handle BigText data (e.g. to move it to or from a BLOB, to read or write BigText data, to find a substring, to move data back and forth between BigText and normal Text variables, etc.).

    If you wish to handle text strings in a single data element greater than 250 characters in length, you can use a combination of BLOB and BigText variables.

  • GUID: This is used to assign a unique identifying number to any database object. GUID stands for Globally Unique Identifier, a 16-byte binary data type that is used for the unique global identification of records, objects, etc. The GUID is generated by an algorithm created by Microsoft.

DateFormula

The only data type defined in DateFormula is as follows:

DateFormula, provides the storage of a simple, but clever set of constructs to support the calculation of run-time sensitive dates. A DateFormula is a combination of:

  • Numeric multipliers (e.g. 1, 2, 3, 4...)
  • Alpha time units (all must be upper case)
    • D for a day
    • W for a week
    • WD for day of the week, i.e. 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
    • CM for current month
    • P for accounting period
    • Y for year
  • Math symbols
    • + (plus) as in CM + 10D means the Current Month end plus 10Days or the 10th of next month
    • – (minus) as in –WD3 means the date of the previous Wednesday
  • Positional notation (D15 means the 15th of the month and 15D means 15 days)

Payment Terms for Invoices make very productive 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 March 9, 2007, a Friday:

  • CM = the last day of Current Month, 03/31/07
  • CM + 10D = the 10th of next month, 04/10/07
  • WD6 = the next sixth day of week, 03/10/07
  • WD5 = the next fifth day of week, 03/17/07
  • CM – M + D = the end of the current month minus one month plus one  day, 03/01/07
  • CM – M = the end of the current month minus one month, 02/28/07

Let us do some experimenting with some hands-on evaluations of several DateFormula values. What we will do is create a table that will calculate the entered dates using DateFormula and Reference Dates.

First, create a table using the Table Designer as you did in earlier instances. Go to Tools | Object Designer | Tables. Click on the New button and define the fields as in the following screenshot. Save it as Table 60000, named Date Formula Test. After you are done with this test, we will save this table for some later testing.

Data Types in Microsoft® Dynamics™ NAV

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, suboption Globals, and then choose the Functions tab. Type in our new Function's, name as CalculateNewDate on the first blank line as shown in the following screenshot and then exit from this form back to the list of data fields.

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

Data Types in Microsoft® Dynamics™ NAV

In the following screenshot, you will see all the field triggers plus the trigger for the new function you just defined, all ready for you to add some C/AL code. The table triggers are not visible unless we scroll up to show them.

Data Types in Microsoft® Dynamics™ NAV

In the following screenshot, you will see all the field triggers plus the trigger for the new function you just defined, all ready for you to add some C/AL code. The table triggers are not visible unless we scroll up to show them.

Data Types in Microsoft® Dynamics™ NAV

Since our goal this time is to focus on experimenting with the DateFormula, we are not going to go into much detail about the logic we are creating. Hopefully, your past experience will allow you to understand the essence of the code.

We are simply going to create the logic within our new function, CalculateNewDate(), to evaluate and store a result date based on the DateFormula and Reference Date that we enter into the table.

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

Data Types in Microsoft® Dynamics™ NAV

When you close and save the table, if you get an error message of any type, you probably have not copied the C/AL code exactly as it is shown in the screenshot.

This code will cause the function CalculateNewDate() to be called any time an entry is made in either the Reference Date for calc or the Date Formula to test fields. The result will be placed in the Date Result field. The use of an integer value in the redundantly named PrimaryKey field allows you to enter several records into the table (by numbering them 1, 2, 3, and so forth) and also allows you to compare the results of date calculations using several different formulae.

Let us try a few examples. We will access the table via the Run button. Enter a Primary Key value of 1 (i.e. one).

  PrimaryKey = 1

For Reference Date for calc enter the letter t (tee), upper case or lower case, it doesn't matter. That will give you the date for Today, whatever the system date is while experimenting. The same date will appear in the Date Result field, because at this point there is no DateFormula entered. Now enter 1D (numeral 1 followed by the letter dee, upper case or lower case, C/SIDE will take care of making it upper case) in the Date Formula to test field. You will see the Date Result field contents are changed to be one day beyond the date in the Reference Date for calc field.

Let us enter another line. Start with a numeral 2 in the PrimaryKey field. Again, enter the letter t (tee) in the Reference Date for calc field and just enter the letter W in the Date Formula to test field. You should get an error message telling you that your formulas should include a number. Make the system happy and enter 1W. You should see a date in the Date Result field that is one week beyond your testing date.

Set the system's Work Date to a date about in the middle of a month. Start another line with the number 3 in the Primary Key, followed by a W (for Work Date) in the Reference Date for calc field. Enter cm (or CM or cM or Cm, it doesn't matter) in the Date Formula to test field. Your result date will be the last day of your 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 your result date will be the first day of your work-date month.

Enter another line with a new Primary Key. Skip over the Reference Date for calc field and just enter 1D in the Date Formula to test field. What happens? You get an error message. NAV cannot deal with making calculation without a Reference Date. If we put this function into production, we might enhance 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 following screenshot shows more sample calculations. Build on these and experiment on your own. You can create lots of different algebraic formulae and get some very interesting results. One NAV user has due dates on Invoices on 10th of the next month. The Invoices are dated at various times during the month they are actually printed. But by using the DateFormula of CM + 10D, the due date is always the 10th of the next month.

Data Types in Microsoft® Dynamics™ NAV

Don't forget to test with WD (weekday), P (period), Q (quarter), and Y (year).

It may seem that we overemphasized this experiment. But you got to see a lot more here than just date calculations.

      
  • You created a new table, just for the purpose of experimenting with a C/AL feature that you might use. This is a technique that comes in handy when you are learning a new feature, trying to decide how it works or how you might use it.
  •   

  • We put some critical 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 function. That function is then called from multiple places to which it applies.
  •   

  • We did our entire test with a table object and a default tabular form that is automatically generated when you Run a table. We didn't have to create much of a supporting structure to do our testing. Of course, when you are designing a change to a complicated existing structure, it is likely that you will have a more complicated testing scenario. But one of your goals will always be to simplify your testing scenarios to both minimize the setup effort and to keep your test narrowly focused on the specific issue.
  •   

  • 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 very date centered.
  •  

Data Structure

The following are the data types in Data Structure:

      
  • File: This refers to any standard Windows file outside the NAV database. There is a reasonably complete set of functions to allow creating, deleting, opening, closing, reading, writing and copying (among other things) data files. For example, you could create your own NAV routines in C/AL to import or export data from a file that had been created by someother application.
  •   

  • Record: This refers to a single line of data within a NAV table. Quite often multiple instances of a table are defined for access, to support some validation process. The working storage variable for the table will be of the data type Record.
Programming Microsoft® Dynamics™ NAV Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook
Published: October 2007
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Objects

Form, Report, Dataport, Codeunit, XMLPort, each represents an object of the type Form, Report, Dataport, Codeunit or XMLPort respectively. Object data types are used when there is a need for reference to an object or some portion of an object from within another object. Examples are cases where one object invokes another (e.g. calling a Report object from a Form object or from another Report object) or where one object is taking advantage of data validation logic that is coded as a function in a Table object or a Codeunit object.

Automation

The following are the data types in Automation:

      
  • 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 another, external application object, small or large, which you can then invoke from your NAV object.
  •   

  • Automation: This allows the definition of a variable that you may access similarly to an OCX but is more likely to be a complete independent application. The application must act as an Automation Server and must be registered with the NAV client calling it. For example, you can interface from NAV into the various Microsoft Office products (e.g. Word, Excel) by defining them in Automation variables.
  •  

Input/Output

The following are the data types in Input/Output:

      
  • Dialog: This allows the definition of a simple user interface window without the use of a Form object. Typically, dialog windows are used to communicate processing progress or to allow a brief user response to a go/no-go question. There are other user communication tools as well, but they do not use a dialog data item.
  •   

  • InStream and Outstream: These are variables that allow reading from and writing to external files, BLOBS, and objects of the Automation and OCX data types.
  •  

References and Other

The following data types are used for advanced functionality in NAV, typically supporting some type of interface with an external object.

      
  • RecordID: This contains the object number and primary key of a table.
  •   

  • RecordRef: This identifies a field in a table and thereby allows access to the contents of that field.
  •   

  • KeyRef: This identifies a key in a table and the fields it contains.
  •   

  • Variant: This defines variables typically used for interfacing with Automation and OCX objects. Variant variables can contain data of a number of other data types.
  •   

  • TableFilter: This defines variables used only by the permissions table related to security functions.
  •  

Data Type Usage

Some data types can be used to define permanently stored data (i.e. in tables) or working storage data definitions (i.e. within a Global or Local data definition within an object). A couple of data types can only be used to define permanently stored data. A much larger set of data types can only be used for working storage data definitions.

The list in the following screenshot shows which data types can be used where:

Data Types in Microsoft® Dynamics™ NAV

FieldClasses

Each data field has a Field Class Property. The properties have not been covered in this article, but the FieldClass has as much affect on the content and usage of a data field as does the data type, maybe even more in some instances. For that reason, we will discuss FieldClasses as a follow-on to our discussion onData Types.

The following are the three FieldClasses:

      
  • Normal: The FieldClass containing all the 'normal' data. If the FieldClass is Normal, then the field contains just what you would expect, based on the Data Type and all the descriptions.
  •   

  • FlowField: The FieldClass that connects a datafield to a previously defined SumIndexField in a table. The FlowField is an important and controlling property of a field. FlowFields do not contain data in any conventional sense. They are really virtual fields. A FlowField contains the definition of how to calculate the data that it represents at run time.

    A FlowField value is always 0, unless something happens to cause it to be calculated. If the FlowField is displayed directly on a form, then it is calculated automatically on initial display. FlowFields are also automatically calculated when they are the subject of predefined filters as part of the properties of a Data Item in an object. In all other cases, a FlowField must be forced to calculate using the C/AL <Record>.CALCFIELDS function. This is also trueif the underlying data is changed after the initial display of a form (i.e. the FlowField must be recalculated to take the change into account).

    Because a FlowField does not contain any actual data, it cannot be used as a field in a key.

    When a data item has its FieldClass set to FlowField, another directly associated property becomes available: CalcFormula. The CalcFormula is the place where you can define the formula for calculating the FlowField. This formula consists of five components as follows:  

    • FlowField type (aka Method)
    •   

    • Sign control (aka Reverse Sign)
    •   

    • Table
    •   

    • Field
    •   

    • Table Filter

    On the CalcFormula property line, there is an ellipsis button displayed.

    Clicking on that button will bring up the form similar to the following screenshot:

    Data Types in Microsoft® Dynamics™ NAV

    The following screenshot shows seven FlowField types:

    Data Types in Microsoft® Dynamics™ NAV

    The explanation of the seven FlowFields is given in the following table:

FlowField Type

Field Data Type

Description (in all cases it applies to the specified setwithin a specific column in a table (i.e. field)

Sum

Decimal

The sum total

Average

Decimal

The average value (i.e. the sum divided by the count)

Exist

Boolean

Yes or No, does an entry exist?

Count

Integer

The number of entries that exist

Min

Any

The smallest value of any entry

Max

Any

The largest value of any entry

Lookup

Any

The value of the specified entry

The Reverse Sign control allows you to change the displayed sign of the result for FlowField types Sum and Average only; the underlying data is not changed.

Table and Field allow you to define to what Table and to what Field within that table your Calculation Formula will apply. When you make the entries in your Calculation Formula screen, there is no validation checking by the compiler that you have chosen an eligible table–field combination. That checking doesn't occur until run time. Therefore, when you are creating a new FlowField, you should test it as soon as you get it defined.

The last, but by no means least significant, component of the FlowField Calculation Formula is the Table Filter. When you click on the ellipsis in the table filter field, the window shown in the following screenshot will appear:

Data Types in Microsoft® Dynamics™ NAV

When you click on the Field column, you will be invited to select a field from the table that was entered into the Table field earlier. This field will have the filter rules you define on this line, which will also indicate which type of filter is this. The explanation is given in the following table

Filter Type

Value Description

Filtering Action

OnlyMax-Limit

Valuels-Filter

Const

A constant which will bedefined in the Value field

Uses the constant to filter for equally valued entries

 

 

Filter

A filter which will be spelled out as a literal in the value field

Applies the filter expression from the Value field

 

 

Field

A field from the table within which this FlowField exists

Uses the contents of the specified field to filter forequally valued entries

FALSE

FALSE

 

 

If the specified field is a FlowFilter and the OnlyMaxLimit parameter isTrue, then the FlowFilter range will be applied on the basis of only having a Max Limit, i.e. having no bottom limit. For example, this is useful for datefilters for Balance Sheet data

TRUE

FALSE

 

 

If the specified field isa FlowFilter and the OnlyMaxLimit parameter isTrue, then the FlowFilter range will be applied on the basis of only having a Max Limit, i.e. having no bottom limit. For example, this is useful for datefilters for Balance Sheet data.

FALSE

TRUE

 

  • FlowFilters: These do not contain any information permanently. They are defined for the purpose of holding filters on a per user basis, with the information being stored at the local workstation. A FlowFilter field allows a filter to be entered at a parent record level by the user (e.g. G/L Account) and applied (through the use of FlowField formulas, for example) to constrain what child data (e.g. G/L Entry records) is selected.

    A FlowFilter allows you to provide very flexible data selection functions to the users in a way that is very simple to understand. The user does not need to have a full understanding of the data structure to apply filtering in intuitive ways, not just to the primary data table but also to the subordinate data. Based on your C/AL code design, FlowFilters can be used to apply filtering on more than one subordinate table. Of course, it is your responsibility as the developer to make good use of this tool. As with many C/AL capabilities, a good way to learn more is by studying standard code.

    A number of good examples on the use of FlowFilters can be found in the Customer (Table 18) and Item (Table 27) tables. In the Customer table, some of the FlowFields using FlowFilters are Balance, Balance (LCY), Net Change, Net Change (LCY), Sales (LCY), and Profit (LCY). There are others as well. The Sales (LCY) FlowField FlowFilter usage is shown in the following screenshot:

    Data Types in Microsoft® Dynamics™ NAV

    Similarly constructed FlowFields using FlowFilters in the Item table include Inventory, Net Invoiced Qty. Net Change, Purchases (Qty.), and a whole host of other fields.

    Throughout the standard code there are a number of FlowFilters that appear in most of the Master table definitions. These are the Date Filter and Global Dimension Filters (Global Dimensions are user defined codes to facilitate the segregation of accounting data by meaningful business break-outs such as divisions, departments, projects, customer type, etc.). Other FlowFilters that are widely used in the standard code, for example, related to Inventory activity, are Location Filter, Lot No. Filter, Serial No. Filter, and Bin Filter.

  •  

Summary

In this article, we have focused on the basic building blocks of NAV data structure, fields and their attributes. We reviewed the types of data fields, properties, and trigger elements for each type of field. Then, we walked through a number of examples to illustrate most of these elements, though we have postponed exploring triggers until we have enough knowledge of C/AL coding techniques to makethat worthwhile.

The Data Type and FieldClass determine what kind of data can be stored in a field. When you combine the table structure with properly designed fields, the essence of your application system design is defined. In this article, we have covered the broad range of Data Type options as well as the FieldClasses. We also considered some examples of different types and classes, and discussed how they are used in an application. We dug into the date calculation tool that givesC/AL an edge in business applications.

In the next article, we will discuss filtering in some detail, and how filtering is considered as we design our database structure, and how the users will access data.

Continue to "Filtering in Microsoft® Dynamics™ NAV

About the Author :


David A. Studebaker

David Studebaker is Chief Technical Officer and a founder of Liberty Grove Software with his partner Karen Studebaker. Liberty Grove Software, a Microsoft Partner, provides development, consulting, training, and upgrade services internationally for Microsoft Dynamics NAV resellers and end user customers.

David has been recognized by Microsoft as a Certified Professional for NAV in all three areas: Development, Applications, and Installation & Configuration. He has been honored by Microsoft as a Lead Certified Microsoft Trainer for NAV.

David just celebrated his first half century of programming, having started programming in 1962. He has been developing in C/AL since 1996. David has been an active participant in each step of computing technology from the first solid state mainframes to today's technology, from binary assembly language coding to today's C/AL and C#.

David's special achievements include his role as co-developer of the first production multi-programmed SPOOLing system in 1967. David has worked on a diverse set of software applications including manufacturing, distribution, retail, engineering, general accounting, association management, professional services billing, distribution/inventory management, freight carriage, data collection and production management, among others. Prior to co-authoring this book, David was the author of Programming Microsoft Dynamics NAV (for the Classic Client) and Programming Microsoft Dynamics NAV 2009 (for the Role Tailored Client).

David has had a wide range of development, consulting, sales and management roles throughout his career. He has been partner or owner and manager of several software development businesses, while always maintaining a hands-on role as a business applications developer.

David has a BS in Mechanical Engineering from Purdue University and an MBA from the University of Chicago. He has been writing for publication since he was an undergraduate. David has been a member of the Association for Computing Machinery since 1963 and was a founding officer of two local chapters of the ACM.

Books From Packt

Entity Framework Tutorial
Entity Framework Tutorial

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008  Installation, Migration, and Configuration

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

 


 

 

No votes yet
Try article by
I really liked the article, and the very cool blog

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
3
b
p
E
b
W
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software