wiki.dynamicsbook: NAV Data Types

[Blog bot]

Summary: /* C/AL Data Types */



== Overview ==

The various Data types that are available in Microsoft Dynamics NAV are available irrespective of what database is being used at the back end.

== Storage Data Types ==

{| class="wikitable" border="1" cellpadding="10" cellspacing="0" width="70%"
|-
! Data Type
! Description
! SQL DataType
|-
| '''Integer'''
| The Integer data type holds whole numbers between -2,147,483,647 and 2,147,483,647
| INTEGER
|-
| '''Text'''
| This data type, as you would expect, stores text. When creating a Text field, the length has to be defined. This defaults to 30 character long, but can be changed up to a maximum of 250 characters.
| VARCHAR
|-
| '''Code'''
| Similar to the Text type, but only uppercase characters are permitted. Any lowercase characters entered either manually or via code are implicitly converted to uppercase. It is preferable to use Code fields when defining keys rather than Text type fields.

When first created, a code defaults to length 10, but as with the Text type, the length can be varied from length 1 to 250.
|
*VARCHAR
*INTEGER
*VARIANT
*BIGINTEGER
[[NAV_Data_Types#Using CODE in SQL Server|More info here]]
|-
| '''Decimal'''
| A [[Wikipedia:Floating_point|floating point]] storage type which allows up to 18 [[Wikipedia:Significant_digit|significant digits]].
| DECIMAL
|-
| '''Option'''
| As a data type, the option field is stored in exactly the same way as an integer (with the same limitations). However, it is presented to the developer and user as a string of options. These options are entered into the ''OptionString'' property, seperated by commas, and is presented to the user as a drop down list of options. In C/AL, an option field can be referenced either by its name, or by it's index.
| INTEGER
|-
| '''Boolean'''
| A simple datatype that can hold 2 states, representing the values ''TRUE'' or ''FALSE'', sometimes expressed in Dynamics NAV as ''Yes'' or ''No''.
| TINYINT
|-
| '''Date'''
| Allows the entry of dates between January 1st 0000 to December 31st 9999. SQL is restricted further in that dates before the year 1754 are not allowed. In SQL, a date field is stored as a DateTime with the time portion ignored.
| DATETIME
|-
| '''Time'''
| In SQL, a time field is stored as a DateTime with the date portion of the field ignored.
| DATETIME
|-
| '''DateTime'''
| Datetime fields were introduced in version 3.00 (Attain) and contain both the date and time portions in the same field.
| DATETIME
|-
| '''Binary'''
| A Binary is a simple data type used to store variable lengths of binary data in a record. The maximum length of a binary field is 250 bytes.
| VARBINARY
|-
| '''Blob'''
| Blob stands for Binary Large Object and is a datatype designed for the storage of binary files such as pictures and documen ts.
| IMAGE
|-
| '''DateFormula'''
| A DateFormula is a language independant method of storing date calculation formulas, used in various C/AL functions such as CALCDATE.
| VARCHAR (32)
|-
| '''TableFilter'''
| Use this data type to apply a filter to another table. At the moment, this data type can only be used when you are setting security filters from the Permission table.
| VARBINARY(252)
|-
| '''BigInteger'''
| BigIntegers can be used if the number that needs to be stored is larger than the maximum supported by the standard integer field.
| BIGINT(8)
|-
| '''Duration'''
| Duration is used to express time as an interval. Each unit represents 1 millisecond (1/1000 of 1 second).
| BIGINT(8)
|-
| '''GUID'''
| A GUID is short for ''Globally Unique IDentifier'', and in NAV is expressed as a Hexadecimal expression. A variation of the UUID data type, GUIDs are generally used in Microsoft platform code to provide a universally unique reference number.
| UNIQUEIDENTIFIER
|-
| '''RecordID'''
| A Record ID stores the primary key reference & table no. for a record usually retrieved by the C/AL function ''RECORDID''.
| VARBINARY(224)
|-
|}


=== Using CODE in SQL Server ===
Special care has to be taken when selecting Code fields for use in SQL server, or when converting a database on the Native database to SQL. By default, a SQL Server implementation will sort code fields as text, which

The most common example of where this can become a problem is on the Chart of Accounts.
The example below displays a sample balance sheet (shortened), showing the sorting that would be achieved using the Dynamics NAV database server:

<PRE>
Sample Balance Sheet
Dynamics NAV Server default sort order
----------------------

1 Balance Sheet
2 Fixed Assets
3 Buildings
19 Buildings - Total
20 Fixtures & Plant
49 Fixtures & Plant - Total
99 Fixed Assets - Total
100 Current Assets
101 Trade Debtors
149 Trade Debtors - Total
150 Bank Accounts
169 Bank Accounts - Total
199 Current Assets - Total
999 Balance Sheet - Total
</PRE>

If the above balance sheet was transferred to a database running on Microsoft SQL Server, the sorting would change the display order to the following:

<PRE>
Sample Balance Sheet
Default SQL Server sort order
-----------------------------

1 Balance Sheet
100 Current Assets
101 Trade Debtors
149 Trade Debtors - Total
150 Bank Accounts
169 Bank Accounts - Total
19 Buildings - Total
199 Current Assets - Total
2 Fixed Assets
20 Fixtures & Plant
3 Buildings
49 Fixtures & Plant - Total
99 Fixed Assets - Total
999 Balance Sheet - Total
</PRE>

The sorting by SQL server is clearly wrong, as the SQL Server option for NAV is sorting the code field as text, rather than being able to detect that all the values in the code field are numbers and adjusting accordingly. This can be remedied by explicitly telling SQL Server how to sort the code field, a topic which is discussed in more depth in this article: [[Sorting in NAV vs SQL]].

== C/AL Data Types ==

This section deals with the variables that can be declared within the programme. There are subtle differences between the variables that can be declared, and their NAV storage datatype counterparts.

=== File ===

=== TransactionType ===

=== OCX ===

[[Categoryevelopment]]
[[Category:C/SIDE]]

Источник http://wiki.dynamicsbook.com/index.p...NAV_Data_Types