Table Fundamentals
59Choosing Data Types
When you have selected an identification number and name for a field, you have to select an appropriate data type. You can use many different types of fields in the C/SIDE database system. Each type is designed to hold a specific kind of information, such as text, numbers, dates and so on.
Fields in a record can be of the following types:
Data Type Description Size
Option Denotes an integer in the range -2,147,483,647 and
2,147,483,647. An option field is defined with an option string, which is a comma-separated list of strings representing each valid value of the field. This string is used when a field of type Option is formatted and its value is converted into a string. An example:
The Option field "Color" is defined with the option string
"Red,Green,Blue". Valid values of the field are then 0, 1 and 2, with 0 representing "Red" and so on. When the "Color" field is formatted, 0 is converted into the string "Red", 1 into "Green", and 2 into "Blue".
The size of the corresponding SQL data type, INTEGER, is 4 bytes.(A)(B)
4 bytes
Integer Denotes an integer between -2,147,483,647 and 2,147,483,647.
The size of the corresponding SQL data type, INTEGER, is 4 bytes.(A)(B)
4 bytes
Decimal A decimal number between -1063 and 1063. The exponent ranges from -63 to +63. Decimal numbers are held in memory with 18 significant digits. The representation of a decimal number is a Binary Coded Decimal (BCD).
The size of the corresponding SQL data type, DECIMAL(38,20), is 17 bytes.(A)(B)
12 bytes
Text Any alphanumeric string. The field must be defined to be between 1 and 250 characters. The space used by a text field equals the maximum length of the text plus one byte. This extra byte is a used to hold the length of the string. An empty text string has the length zero.
The size of the corresponding SQL data type, VARCHAR, is 1 byte per character in the field’s value.(A)(B)
Maximum string length + 1 byte (see note).
Chapter 6. Table Fundamentals
60
Code An alphanumeric string, which is right-justified if the contents are numbers only. If letters or blanks occur among the numbers, the contents are left-justified. All letters are converted to uppercase upon entry.
The field must be defined to be between 1 and 250 characters.
The space used by a code field equals the maximum length of the text plus two bytes. The first of the extra bytes holds information about the length of the string, and the second byte stores alignment information.
In the Microsoft SQL Server Option for Navision, code fields work in a different way. You can use the SQL Data Type property to indicate whether code fields can contain integers or text strings. Refer to the online C/SIDE Reference Guide for information about the SQL Data Type property. Further,
"Numbering in Navision" on page 521 contains information about the sorting of numeric values in code fields.
The size of the corresponding SQL data type, VARCHAR, is 1 byte per character in the field’s value.(A)(B)
Maximum string length + 2 bytes (see note).
Date A date value in the range from January 1, 0 to December 31, 9999. An undefined date is expressed as 0. All dates have a corresponding closing date. The system regards the closing date for a given date as a period that follows the given date but comes before the next normal date; that is, a closing date is sorted immediately after the corresponding normal date but before the next normal date.
The size of the corresponding SQL data type, DATETIME, is 8 bytes.(A)(B)
4 bytes
Time Any time in the range 00:00:00 to 23:59:59.999. A time field contains 1 plus the number of milliseconds since 00:00:00 o'clock, or 0 (zero), an undefined time. A time value is calculated in the following way:
Time = 1 + (number of milliseconds since 00:00:00).
The size of the corresponding SQL data type, DATETIME, is 8 bytes.(A)(B)
A time field is stored as an integer (four bytes).
Boolean Assumes the values TRUE or FALSE. When formatted, a boolean field is shown as "Yes" or "No".
The size of the corresponding SQL data type, TINYINT, is 1 byte.(A)(B)
4 bytes
Binary Contains binary data. The binary data is stored in the record.
The size of the corresponding SQL data type, VARBINARY, is the number of bytes in the field’s value.(A)(B)
Maximum length is 250 bytes (see note).
BLOB Binary Large Object. Used to store bitmaps and memos.
Notice that the BLOB isn’t stored in the record, but in the BLOB area of the table.
The size of the corresponding SQL data type, IMAGE, is the number of bytes in the field’s value.(A)(B)
8 bytes in the record + size of BLOB data.
(max. 2 GB)
Data Type Description Size
6.1 What Is a Table?
61 (A) THECALCULATIONOFTHESIZEOFASPECIFIC SQL SERVERRECORDREQUIRESMORETHAN SIMPLYSUMMINGTHESIZESOFTHEFIELDVALUES. REFERTO MICROSOFT’S SQL SERVER DOCUMENTATIONFORFURTHERINFORMATION.
(B) THISISTHE SQL SERVERDATATYPETHAT NAVISIONUSESWHENITCREATESTHE NAVISIONDATA TYPE. FORFURTHERINFORMATION, SEEPAGE 75.
. . .
Note
In Navision Database Server, data is stored with a four byte alignment because of performance considerations. The sizes of text, code and binary fields (that can have variable lengths) are rounded up to the nearest value that is a multiple of four. This
. . .
means that, for example, a text string of 10 characters will occupy 12 bytes.
Besides the ordinary fields discussed in this section, the C/SIDE database system also includes two special types of fields:
· FlowField®
· FlowFilter®
How these special fields provide powerful data retrieval mechanisms is described on page 83.
DateFormula Used to verify the date entered by the user. The syntax is for example:
30D (=30 days)
CM+1M (=current month plus one month) D15 (=on the 15th of each month)
4 bytes
TableFilter This data type is used to apply a filter to another table.
Currently, this can only be used to apply security filters from the Permission table.
BigInteger A 64 bit integer. 8 bytes
Duration Represents the difference between two points in time, in milliseconds. This value can be negative.
8 bytes
DateTime Represents a point in time as a combined date and time. The datetime is stored in the database as Coordinated Universal Time (UTC) and is always displayed as local time in Navision.
Local time is determined by the time zone regional settings used by your computer.
You must always enter datetimes as local time. When you enter a datetime as local time, it is converted to UTC using the current settings for the time zone and daylight saving time.
The DateTime datatype does not support closing dates.
Stored as two 4 byte integers
GUID Globally unique identifier 16 bytes
RecordID Unique record identifier
Data Type Description Size
Chapter 6. Table Fundamentals
62
6.2 V
IEWING ANDM
ODIFYINGP
ROPERTIESThis section describes how you can use properties in your table design. As you learned earlier, there are three kinds of properties:
· Table Properties
· Field Properties
· Key Properties
Viewing and Modifying Table Properties
A table in C/SIDE has a number of properties that determine the behavior of the table.
When you create a table, C/SIDE automatically defines a number of default values for these properties. Depending on what the table is going to be used for and how it is related to other application objects, you may want to change these default values.
C/SIDE contains the following table properties:
Property Name Use this property to...
ID define the ID of the table.
Name define a name (used as caption) for the table.
Caption display the caption in the currently selected language. The value is taken from the CaptionML property if this property is set. A caption is the text the system uses to show the identity of a control (for example, in the caption bar of a form or as the basis for a label for another control).
CaptionML provide the text that will be used to identify a control or other object in the user interface. CaptionML is multilanguage enabled. This means that it can contain a list of texts in different languages. The text that is actually used will be selected according to the current language setting of the user.
Description include an optional description of the table. This description is for internal purposes only and is not visible to the end user. A short description of the table’s purpose makes it easier to maintain the application.
DataPerCompany determine whether the system will create a version of the data for each company in the database.
IncludeDataInDesc determine whether data will be included when application objects are exported and imported.
Permissions define extended permissions for the table.
LookupFormID define the ID of the form you want to use as a lookup.
DrillDownFormID define the ID of the form you want to use as a drill down.
DataCaptionFields define a list of fields to be used as captions when a record from this table is displayed in, for example, a form.
PasteIsValid tell the system whether it should be allowed to insert records in this table by pasting.
LinkedObject determine whether this Navision table description is to be linked to an existing SQL Server object.
6.2 Viewing and Modifying Properties
63