Data Types in Microsoft® Dynamics™ NAV

Title Data Types in Microsoft® Dynamics™ NAV Author(s) David Studebaker Publisher Packt Publishing Published October 2007 ISBN 1904811744 Price $53.99 Pages 480

Data Types in Microsoft® Dynamics™ NAV

"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

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 chapter, 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:

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

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. Owner: The usage is not defined.

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.)

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.

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.

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 data type-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.

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.

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.

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 therefore harder 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:

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:

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:

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 or False, 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, it can 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. Numeric operations can be done on Char variables. Char variables can be defined with character values. Char variables cannot be defined as permanent variables in a 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.
  
• 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 10 Days 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.

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:

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.

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.

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.

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 10^th of the next month.

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 some other 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.

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:

FieldClasses

Each data field has a Field Class Property. The properties have not been covered in this chapter, 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 on Data 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 true if 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:

  

  The following screenshot shows seven FlowField types:

  

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

  data

  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:

  

  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 be defined 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 for equally valued entries	False	False
  		If the specified field is a FlowFilter and the OnlyMaxLimit parameter is True, 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 date filters for Balance Sheet data	True	False
  		If the specified field is a FlowFilter and the OnlyMaxLimit parameter is True, 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 date filters 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:

  

  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.

Filtering

As mentioned earlier, filtering is one of the very powerful tools within NAV C/AL. Filtering is the application of defined limits on the data to be considered in a process. Filter structures can be applied in at least three different ways, depending on the design of the process. The first way is for the developer to fully define the filter structure and the value of the filter. This might be done in a report designed to show only information on a selected group of customers, for example those with an open Balance on Account. The Customer table would be filtered to report only customers who have an Outstanding Balance greater than zero.

The second way is for the developer to define the filter structure, but allow the user to fill in the specific value to be applied. This approach would be appropriate in an accounting report that was to be tied to specific accounting periods. The user would be allowed to define what period(s) were to be considered for each report run.

The third way is the ad hoc definition of a filter structure and value by the user. This approach is often used for general analysis of ledger data where the developer wants to give the user total flexibility in how they slice and dice the available data.

It is quite common within the standard NAV applications and in the course of enhancements to use a combination of the different filtering types. For example, the report just mentioned that lists only customers with an open Balance on Account (via a developer-defined filter) could also allow the user to define additional filter criteria. Perhaps, the user wants to see only Euro currency-based customers, so they would filter on the Customer Currency Code field.

Filters are an integral part of FlowFields and FlowFilters, two of the three Field Classes. These are very flexible and powerful tools, which allow the NAV designer to create forms, reports, and other processes that can be used by the user under a wide variety of circumstances for various purposes. In most systems, user inquiries (forms and reports) and processes need to be quite specific to different data types and ranges. The NAV C/AL toolset allows you to create relatively generic user inquiries and processes and then allow the user to apply filtering to fit their specific needs.

Defining Filter Syntax and Values

Let us go over some common ways in which we can define filter values and syntax. Remember, when you apply a filter, you will only view or process records where the filtered data field satisfies the limits defined by the filter.

• Equality and inequality
  • either an equal (=) sign or no sign filters for data "equal to" the filter value.
    
    

    Data Type — description	Example Filters
    Integer	=200
    Integer	200
    Text	Chicago
    Text	" (two single quote marks)

  • a greater than (>) sign filters for data greater than the filter value
    
    

    Data Type - description	Example Filters
    Integer	>200
    Date	>10/06/07
    Decimal	>450.50

  • a less than (<) sign filters for data less than the filter value
    
    

    Data Type - description	Example Filters
    Integer	<150
    Date	<10/07/07

  • the equal sign can be combined with the greater than (>=) or less than (<=) signs to filter for data "greater than or equal" OR "less than or equal" to the filter value
    
    

    Data Type - description	Example Filters
    Integer	<=100
    Date	<=12/31/07
    Date	>=1/1/08
    Text	'>= Grade B

  • Not Equal is represented by the combination of the "less than" symbol plus the "Greater than" symbol to filter for data not equal to the filter value
    
    

    Data Type - description	Example Filters
    Integer	<>1
    Date	<>TODAY (TODAY is a system variable representing the current system date
    Boolean	<>yes (an awkward way of stating "No"

• Ranges
  • Ranges are defined by an expression containing two dots in a row (in other words ..). Ranges are inclusive, that is the maximum and minimum values are included within the range. Ranges have three variations. The first is the from - to version which includes both a bottom end or minimum to the range and a top end or maximum.
    
    

    Data Type - description	Example Filters
    Integer	1..10
    Date	5/1/07..5/31/07
    Text	Jones..Smith
    Decimal	100.01..199.99

  • The second range variation consists of the range operator (the two dots ..) plus a range maximum This means "give me all the values from the lowest possible value up to and including the range maximum. This is generally the same as using the less than or equal to (<=) format.
    
    

    Data Type - description	Example Filters
    Integer	..10 (Gives the same results as <=10)
    Date	..12/31/07
    Decimal	..99.99

  • The third range variation consists of a lower limit (minimum) value flowed by the range operator (..).
    
    

    Data Type - description	Example Filters
    Integer	100.. (Gives the same results as >=100)
    Date	1/1/07..
    Decimal	100000.00..

• Boolean operators
  • There are two Boolean operators. The operators are the ampersand sign (&) representing the logical AND operation and the pipe symbol (|) representing the logical OR operation.
  • The OR operator can be used to create a discontinuous set of allowed values.
    
    

    Data Type - description	Example Filters
    Integer	5|10|15|20 (This will give you matches on all four of the stated values and only on those values.)
    Date	10/1/07|11/1/07|12/1/07 (This filter will pass through on records dated on the first date of the three months)

  • The AND operator can generally only be used in combination with other filtering operators.
    
    

    Data Type - description	Example Filters
    Integer	(>=100) & (<=1000) (Gives the same result as the range 100.1000)
    Date	<>TODAY (TODAY is a system variable representing the current system date)
    Boolean	<>yes (an awkward way of stating "No")

• Wild cards
  • There are three wild card characters that can be used within filter constructs. Wild cards only apply to string data. You will not find the term wildcard defined or the usage of wildcards described in the Microsoft documentation or Help.
  • Asterisk (*) represents any character and any number of characters.
    
    

    Data Type - description	Example Filters
    Text	*st* (Includes all data containing the lowercase letters 'st')
    Text	st* (Includes only the data starting with the lowercase letters 'st')
    Text	*st (Includes only the data ending with the lowercase letters 'st')

  • Question mark (?) represents any character, but only one character.
    
    

    Data Type - description	Example Filters
    Text	?st? (Includes all data which is four characters long with the middle two characters being the lowercase letters 'st')
    Text	????st (Includes all data which is exactly six characters long ending with the lowercase letters 'st')

  • "At" symbol (@) eliminates case sensitivity for the value following. The @ is often used in combination with the asterisk to make the filter value satisfy a wider range of data
    
    

    Data Type - description	Example Filters
    Text	*@st* (Includes all data containing any of the strings 'st', 'St', 'ST' or 'sT')
    Text	@*st* (Gives the same results as the previous example)

• Combinations - Many of these filter constructs can be used in combination. Again, the caution applies about thoroughly testing your creations before inflicting them on unsuspecting users. It is relatively easy to create a filter which, on initial thought seems logical, but which won't work the way you thought it would. In addition, the C/AL compiler routine which interprets filters is not perfect. It can get confused or just fail. Be very cautious about using combinations that contain wildcards, especially (but not limited to) those expressions containing both wildcards and Boolean operators.

Be very cautious about constructing filters based on exclusions. Generally, the limited "inclusive" approach works better. For example, you might want to print a Customer list excluding all Customers for the Salespeople with codes of JR and MD.

You might try create a filter on Salesperson Code such as (<>JR) AND (<>MD). The C/SIDE routine that checks filter will not accept that as a valid entry. The same goes for <> (JR AND MD), as well as the attempt to put in two separate filter entries (only one filter string is allowed per data field.). What to do?

To simplify, let us assume all our Salesperson Code are just two characters long. You should create a filter on the Salesperson Code in the form (..JQ) | (JP..MC) | (ME..). This translates to all the Customers having either a Salesperson Code less than or equal to JQ or (the pipe symbol: | ) from JP to MC or greater than or equal to ME. In other words, all the two character codes except JR and MD.

Experimenting with Filters

Now it is the time for you to do some creative experimenting with filters. We want to accomplish several things through our experimentation. Our first purpose is to get more comfortable with how filters are entered. Secondly, we want to see the effects of different types of filter structures and combinations. If we had a database with a large volume of data in it, we could also experience the speed of effecting the filtering on fields in keys and fields not in keys. But the amount of data in the Cronus database is small and our computers are very fast, so any speed differences will be difficult to see.

We could experiment on any report that allows filtering. To give us some options for our experimentation, we will use the Customer/Item List. This will report which Customer purchased what Items. The Customer/Item List can be accessed on the NAV user menu via Sales & Marketing | Reports | Customer | Customer/Item List.

When you initially run the Customer/Item List, you will see just three data fields listed for entry of Filters on the Customer table as shown in the following screenshot:

There are also three data fields listed for entry of Filters on the Value Entry table as shown in the following screenshot:

In each case, these are the fields that the developer determined should be emphasized. If you run the report without any filters at all, using the standard Cronus data, the contents of the first page of the report will resemble the following screenshot:

If you want to print information only for customers whose names begin with a letter A, your filter will be very simple, similar to the following screenshot:

The resulting report will be similar to the following screenshot, showing only data for the two customers on file whose names begin with the letter A.

If you want to expand the customer fields on which you can apply filters, click on the first empty field and you will see something similar to the following screenshot. The size of the pop-up window can be stretched as large as your display image allows and you can then scroll down to see rest of the fields.

This provides us access to all the fields in the customer record, the table identified in the Tab heading. From this list we can choose one or more fields and then enter filters on those fields. If we chose Territory Code, for example, then the Request Form would look similar to the following screenshot. And if we clicked on the lookup arrow in the Filter column, a screen would pop-up allowing us to choose data items from the related table, in this case, Territories.

This particular Request Form has tabs for each of the two primary tables in the report. Click on the Value Entry tab to filter on the Item-related data. If we filter on the Item No. for Item No's that contain the letter W, the report will be similar to the following screenshot:

If we want to see all the items containing either the letter W or the letter S, our filter would be *W* | *S*. If you made the filter W | S, then you would get only entries equal exactly to W or to S because we didn't use any wild cards.

You should go back over the various types of filters we discussed and try them all. Then you should try some combinations. Get creative! Try some things that may or may not work and see what happens. Explore a variety of reports or list screens in the system and try applying filters to see what happens. A good screen to which to apply filters is the Customer List (Sales & Marketing menu | Sales | Customers | F5). This is supposed to be a non-threatening learning experience (you can't hurt anything or anyone).

This is also an opportunity to learn more about the NAV User Interface, because that is what you must use to do your filtering. There are four buttons at the top of the screen that relate to filtering, plus one for choosing the active key. In Windows XP, they look like the following screenshot:

From left to right, they are as follows:

• Field Filter (F7) - Highlight a field, press F7 (or select View | Field Filter), and the data in that field will appear in a display ready for you to define a filter on that data field. You can edit the filter in any way before you click OK.
• Table Filter (Ctrl+F7) - Press the Ctrl Key and F7 simultaneously (or select View | Table Filter). You will be presented with a form that allows you to choose any number of fields in the left column and, in the right column, enter filters to apply to those fields. Each of these individual filters is a Field Filter, the same as would have been applied using the Field Filter option just described. The filters for the individual fields are "ANDed" together (i.e. they all apply simultaneously). If you invoke the Table Filter form when any Field Filters are already applied, they will be displayed.
• Flow Filter (Shift+F7) - Press the Shift Key and F7 simultaneously (or select View | Flow Filter). You will be presented with a form that allows you to choose any number of fields in the left column and in the right column, enter filters to use with those fields. On initial display, it will show all the Flow Filter fields available. For any Flow Filter field, you can enter a filter, which will then be applied to the underlying data for FlowFields whose definition includes a constraint by that particular Flow Filter field.
  
  You can also use this form to enter Field Filters, but you will not be able to see the field filters that are already in effect via this form. To remove Flow Filters, you must call up this form and manually remove the filters, by deleting the filter lines or at least the filter values.
• Show All (Shift+Ctrl+F7) - This will remove all Field Filters, but will not remove any Flow Filters.
• Sort (Shift+F7) - allows you (or your user) to choose which key is active on a displayed data list (unless the underlying C/AL code overrules). By properly choosing a key that contains the field on which you wish to filter, you can significantly affect the speed of the filtering process. Of course this is true for filtering processes coded in C/AL as well. When you are viewing a form and want to check if filters are in effect, check the bottom of the screen for the word FILTER as shown in the next image.
  
  
  
  
  One of the most frequent support calls by new users seems to be "My data has disappeared." The proper response is "Does it say FILTER at the bottom of the screen?" Almost always the answer is "Yes", in which case the proper assistance is to use the Filter icons we just reviewed to inspect and/or clear unwanted filters (typically using the Table Filter and Flow Filter to inspect, possibly using the Show All and Flow Filter to clear filters.

Summary

In this chapter, 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 make that 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 chapter, 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 gives C/AL an edge in business applications.

Then we discussed Filtering in some detail, and how filtering is considered as we design our database structure, and how the users will access data.