A field is the basic element of data definition in NAV—the atom in the structure of a system. The elemental definition of a field consists of its number, its description (name), its data type, and, of course, any properties required for its particular data type. A field is defined by the values of its properties and the C/AL code contained in its triggers.
The specific properties that can be defined for a field depend on the data type. There are a minimum set of universal properties. We will review these first. Then, we will review the rest of the more frequently used properties, some that are data dependent and some that are not. You can check out the remaining properties by using Developer and IT Pro Help within the Table Designer.
We can access the properties of a field while viewing the table in Design mode by highlighting the field and then clicking on the Properties icon, or by clicking on Properties under View, or by pressing Shift + F4. All of the property screenshots in this section were obtained this way for fields within the standard Customer table. As we review various field properties, you will learn more if you follow along in your NAV system using the Object Designer. Explore different properties and the values they can have. Make good use of NAV 2015's Help functions liberally for additional information and examples.
The property value that is enclosed within < > (less than and greater than brackets) is the default value for that property. When we set a property to any other value, < > should not be present unless they are supposed to be part of the property value (for example, as part of a text string value). When a property has been changed from its default value, the NAV 2015 C/AL Editor displays the new property value in bold.
All of the fields, of any data type, have the following properties:
- Field No.: The identifier for the field within the containing table object.
- Name: This is the label by which the C/AL code references the field. A name can consist of up to 30 characters, including special characters. The name can be changed by a developer at any time and NAV will automatically ripple that change throughout the system. If no caption value has been defined, the name is used as the default caption when data from this field is displayed. Changing names that are used as literals in C/AL code can cause problems with some functions, such as web services and GETFILTERS, in which the reference is based on the field name rather than the field number.
- Caption: This contains the defined caption for the currently selected language. It will always be one of the defined multilanguage captions. The default language for a NAV installation is determined by the combination of a set of built-in rules and the languages available in the installation.
- CaptionML: This defines the multilanguage caption for the table. It also identifies the language in use; for example, ENU for US English (as shown in the following screenshot).
- Description: This is an optional use property for our internal documentation.
- Data Type: This defines what type of data format applies to this field (for example, Integer, Date, Code, Text, Decimal, Option, or Boolean).
- Enabled: This determines whether or not the field is activated for user generated events. The property defaults to <Yes> and is rarely changed.
- AccessByPermission: This determines the permission mask required for a user to access this field in pages or in the user interface.
The following screenshot shows the properties for the Picture field of the Data Type BLOB in the Company Information table (this field is often used to store a company's logo image):
The set of properties shown for a BLOB data type field is the simplest set of field properties. After the properties that are shared by all of the data types, appear the BLOB-specific properties scan be seen; these are SubType, Owner, and Compressed.
- SubType: This defines the type of data stored in the BLOB and sets a filter in the import/export function for the field. The three SubType choices are Bitmap (for bitmap graphics), Memo (for text data), and User-Defined (for anything else). User-Defined is the default value.
- Owner: This defines the NAV Server user who owns the object in the BLOB field.
- Compressed: This defines whether the data stored in the BLOB is stored in a compressed format. If we want to access BLOB data with an external tool (from outside of NAV), this property must be set to
No.
The properties of Code and Text data type fields are quite similar to one another. This is logical, since both represent types of textual data. The following images are from the Customer table:
The following are some properties common to both the Code and Text data types:
- DataLength: This specifies the maximum number of characters the field can contain. This is 250 characters in a table, if no maximum limit is specified for a Text field, there is no length limitation for a variable stored only in memory (working storage). Code fields in memory cannot exceed 1024 characters in length.
- InitValue: This is the value that the system will supply as a default when a field is initialized.
- AltSearchField: This has been replaced by Field Groups (described in Chapter 2, Tables) in the Role Tailored Client; it was a feature implemented in the old Classic Client. Presumably, it was left in place for backward compatibility.
- Caption Class: This can be set up by the developer to allow users to dynamically change the caption for a text box or a check box. Caption Class defaults to empty. For more information on this, refer to Developer and IT Pro Help. Used in base NAV in the Dimensions fields.
- Editable: This is set to
Nowhen we don't want to allow a field to be edited; for example, if it is a computed or assigned value field that the user should not change. Editable defaults toYes. - NotBlank, Numeric, CharAllowed, DateFormula, and ValuesAllowed: Each of these allows us to place constraints on the data that can be entered into the field by a user. They do not affect data updates driven by the application's C/AL code.
- SQL Data Type: This applies to the Code fields only. SQL Data Type allows you to define what data type will be allowed in a particular Code field and how it will be mapped to a SQL Server data type. This controls the sorting and display. Options are
Varchar,Integer,BigInteger, andVariant.Varcharis the default and causes all of the data to be treated as a text.IntegerandBigIntegerallow only numeric data to be entered. AVariantcan contain any data type from a wide range of NAV data types. In general, once set, this property should not be changed. These settings should not affect any data handling that is done in SQL Server external to NAV, but the conservative approach is not to make changes here. - TableRelation: This is used to specify a relationship to the data in the specified target table. The target table field must be in the primary key. The relationship can be conditional and/or filtered; it can be used for validation, lookups, and data-change propagation.
- ValidateTableRelation: If a TableRelation is specified, set this to
Yesin order to validate the relation when data is entered or changed. (In other words, confirm that the entered data exists in the target table.) If a TableRelation is defined, and this property is set toNo, the automatic table referential integrity will not be maintained. Note that application code can be written that will bypass this validation. - TestTableRelation: This is a property that has been left over from earlier versions and no longer has any use or value.
- ExtendedDataType: This property allows the optional designation of an extended data type that automatically receives special formatting and validation. Type options include an e-mail address, a URL, a phone number, a report filter, a progress bar ratio, or a masked entry (as dots). An Action Icons may also be displayed, when three fields are defined with ExtendedDataType, as shown in the following screenshot:
Let's take a look at the properties of two more data types, Decimal and Integer, especially the properties related to numeric content:
- DecimalPlaces: This sets the minimum and maximum number of decimal places (min:max) for storage and display in a Decimal data item. The default is
2(2:2), the minimum is0, and the maximum is255. - BlankNumbers, BlankZero, and SignDisplacement: This can be used to control the formatting and display of the data field on a page. BlankNumbers and BlankZero all fields of the chosen values to be displayed as blank. SignDisplacement allows data positioning to be shifted for negative values.
- MinValue and MaxValue: When set, these constrain the range of data values allowed for user entry. The available range depends on the field data type.
- AutoIncrement: This allows you to define one Integer field in a table to automatically increment for each entered record. When used, which is not often, it almost always supports the automatic updating of the filled that is used as the last field in a primary key, enabling the creation of a unique key. The use of this feature does not ensure a contiguous number sequence. Under some circumstances, the use of this feature can lead to table locking conflicts. When the property is set to
Yes, the automatic functionality should not be overridden in code.
The properties for an Option data type are similar to those of other numeric data types. This is reasonable because an Option is stored as an integer, but there are also properties that are specific to Option:
- OptionString: This details the text interpretations for each of the stored integer values that are contained in an Option field.
- OptionCaption and OptionCaptionML: These serve the same captioning and multilanguage purposes as caption properties do for other data types.
Internally, options are stored as integers, which are tied to each option's position in the OptionString starting with position 0, 1, 2, and so on. The OptionString and OptionCaption properties are shown in the following screenshot:
To see field triggers, let us look at our Table 50000 - Radio Show. Open the table in the Design mode, highlight the No. field, press F9, and you will see the following screenshot:
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. Its intended use is to validate that the entry conforms to the design parameters for the field. It can also be executed under program control through the use of theVALIDATEfunction (which we will discuss later).OnLookup(): OnLookup behavior can be triggered by pressing F4 or Shift + F4 from an ellipsis button or by clicking on the lookup arrow in a field, as shown in the following screenshot:
- If the field's TableRelation property refers to a table, then the default behavior is to display a drop-down list to allow the selection of a table entry to store it in this field. The list will be based on the Field Groups defined for the table. We may choose to override that behavior by coding different behavior for a special case. We must be careful because any entry whatsoever in the body of an
OnLookup()trigger, even a comment line, will eliminate the default behavior of this trigger.
Some good examples of tables in the standard product to review for particular features are as follows:
- 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 Triggers table. A wide variety of field property variations can be seen in this table as well.
- Table 14 – Location and Table 91 – User Setup both have good examples of
OnValidatetrigger C/AL code, as do all of the primary master tables (Customer, Vendor, Item, Job, and so on.)
The number of each field within its parent table object is the unique identifier that NAV uses internally to identify that field. We can easily change a field number when we initially define a table layout. However, after other objects such as pages, reports, or code units reference the fields in a table, it becomes difficult to change the numbers of referenced fields. Deleting a field and reusing its field number for a different purpose is not a good idea and can easily lead to programming confusion.
Note
We cannot easily safely change the definition, renumber, or delete a field that has data present in the database. The same can be said for reducing the defined size of a field to less than the largest size of data that is already present in that field. However, if we force the change, the force function will override the system's built-in safeguards. This action can truncate or delete data.
When we add new fields to standard NAV product tables (those shipped with the product), the new field numbers must be in the 50,000 to 99,999 number range, unless we have been explicitly licensed for another number range. Field numbers for fields in new tables that we create can be anything from 1 to 999,999,999 (in all cases without the commas).
When a field representing the same data element appears in related tables (for example, Table 37 – Sales Line and Table 113 – Sales Invoice Line), the same field number should be assigned to that data element for each of the tables. Not only is this consistent approach easier for reference and maintenance, but it also supports the TRANSFERFIELDS function. TRANSFERFIELDS allows you to copy data from one table's record instance to another table's record instance by doing record-to-record mapping based on the field numbers.
If we plan ahead and number the fields logically and consistently from the beginning of our design work and provide an entry in the Description column for each field, we will create code that's easier to maintain. It's a good idea to leave frequent gaps in field number sequences within a table. This allows easier insertion of new fields that are numerically adjacent to related, previously defined fields. In turn, this makes it easier for the following developer to understand the modification's data structure.
For additional information, please see Object Numbering Conventions in the Developer and IT Pro Help in the Development Environment.
In general, the rules for naming fields (data elements in a table) and variables (data elements within the working storage of an object) are the same, and we will discuss them on that basis. The Developer and IT Pro Help section's Naming Conventions describes many recommended best practices for naming within NAV. A lot of additional information can also be found in the recently released C/AL Coding Guidelines at https://community.dynamics.com/nav/w/designpatterns/156.cal-coding-guidelines, which includes a How do I video.
Variables in NAV can either be global (with a scope across the breadth of an object) or local (with a scope only within a single function). Variable names should be unique within the sphere of their scope. 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 is not a good idea and will almost certainly confuse the next developer that follows. Therefore, we should make our working variable names unique within the object.
Uniqueness includes not duplicating reserved words or system variables. Refer to the C/AL Reserved Words list in the Developer and IT Pro Help. Avoid using as a variable name, any word that appears as an UPPER CASE word in either the Developer and IT Pro Help or any of the published NAV technical documentation. For example, we shouldn't use the words Page or Image as variable names.
Variable names in NAV are not case sensitive. There is now a 128-character limit on variable names (but still a 30-character limit on field names in tables). Variable names can contain all ASCII characters except for control characters (which can contain ASCII values from 0 to 31 and 255) and the double quotes (ASCII value 34) as well as some Unicode characters that are used in languages other than English. Characters outside the standard ASCII set (0-127) may display differently on different systems.
Note
Note that the compiler won't tell us that an asterisk (*, ASCII value 42) or question mark (?, ASCII value 63) cannot be used in a variable name. However, since both the asterisk and the question mark can be used as wildcards in many expressions, especially filtering, neither one should be used in a variable name.
The first character of a variable name must be a letter between A and Z (upper or lower case) or an underscore ( _, ASCII value 95) unless the variable name is enclosed in double quotes when it is referenced in code (and such names should be avoided). Alphabets other than the 26-character English alphabet may interpret the ASCII values to characters other than A to Z and may include more than 26 characters. A variable name's first character can be followed by any combination of the legal characters.
If we use any characters other than the A-Z alphabets, numerals, and underscore, we must surround our variable name with double quotes each time we use it in the C/AL code (for example, Cust List, which contains an embedded space, or No., which contains a period).
When we create a variable with a complex data type such as Record, Report, Codeunit, Page, XMLport, Query, or Testpage, and do not supply a name; the variable name will be automatically generated according to C/AL Coding Guidelines by the Development Environment.
See Naming Conventions in the Developer and IT Pro Help for additional guidance to name C/AL variables.