Bookshelf v7.7: About Field Data Types

The Type property specifies the data type for the field. Field data types are used to identify the type of data retrieved from and sent to the database server.

These data types are not mapped to the physical data types defined by the database. The data type of the field is generally more specific than the data type of the underlying column (as identified in the Physical Type property of the column). For example, both DTYPE_NUMBER (decimal) and DTYPE_INTEGER field data types have the Number physical data type in the column.

It is not recommended to map a field to a different table column type, for example a DTYPE_NUMBER field mapping to a table column of type Varchar.

Just as the data type of the underlying column restricts the set of field data types that will work correctly, the data type of the field restricts the set of correctly functioning format options in the control or list column that displays it.

Most formatting is defaulted from the Microsoft Windows Control Panel. Overriding the default format in the repository is possible but might lead to confusion. For example, overriding a number format to show more or fewer decimal places would be useful, but overriding a date format to DD/MM/YY would be confusing to a user who has set the date format to MM/DD/YY in the Control Panel.

NOTE: Multi-value fields (fields with a Multi Valued property setting of TRUE) have a blank Type property, because the data type of the field is specified in the detail business component that populates it.

Table 36. Field Data Types
Field Data Type	Physical Type	Max. Length	Description
DTYPE_BOOL	Character	1	Refers to data stored as Y or N, often displayed as TRUE or FALSE and checked or unchecked.
DTYPE_CURRENCY	Number	22	Refers to the data as currency. You can control the appearance of currency values on a screen through the Windows Control Panel, or you can specify an explicit format mask in the Display Format property by using the following symbols: Dollar sign ($). Specifies the position for the currency symbol. Trailing period (.). Specifies the default precision for the currency. All valid symbols described for DTYPE_NUMBER.
DTYPE_DATE	Date	7	Refers to the data as a date. When the date is returned, any additional information (for example, time) is ignored. You can set the appearance of date values through the Windows Control Panel, or you can specify an explicit date format using the following symbols: YY. Two-digit year without a leading zero. Y. Two-digit year with a leading zero. YYYY. Four-digit year without a leading zero. YYY. Four-digit year with a leading zero. MM. Month without a leading zero. M. Month with a leading zero. DD. Day without a leading zero. D. Day with a leading zero. n Slash (/). Position of the date separator (you specify the character in the Windows Control Panel).
DTYPE_DATETIME	Date Time	7	Refers to the data as a date and time. You can set the appearance of time and date values through the Windows Control Panel, or you can specify an explicit date format using a combination of the symbols for DTYPE_DATE and DTYPE_TIME. Alternatively, you can use one of the following properties: Date. Displays only the date portion of the value, using the format specified in the Windows Control Panel. Time. Displays only the time portion of the value, using the format specified in the Windows Control PanelTimeNoSec. Displays only the hour-and-minute portion of the value, using the format specified in the Windows Control Panel. TimeNoSec. Displays only the hour-and-minute portion of the value, using the format specified in the Windows Control Panel.
DTYPE_ UTCDATETIME	UTC Date Time	30	Indicates that the corresponding field represents date information, with both a date and a time component, that will be stored in the database in UTC time (UTC is the equivalent of Greenwich Mean Time without any adjustments for daylight savings time). Fields of this type should correspond to database columns of type U, and the display values for these fields will be converted to/from UTC based on the default time zone specified in the user's preferences.
DTYPE_ID	Varchar	15	Refers to the data as the primary key automatically generated by the application. Fields mapped to extension columns of physical type Varchar(15) will automatically default to data type DTYPE_ID.
DTYPE_INTEGER	Number	22*	Refers to data as whole numbers ranging in value from - 2147483648 to 2147483647.
DTYPE_NOTE	Long	16 KB	Refers to the data as a long string less than or equal to 16 KB (16383 bytes); the default, if the length is not explicitly defined, is 16 KB. When used with the Pop-up Edit property in a control or list column, this data type is used to indicate to the user interface that a multiline edit box should be used. Users cannot query on fields of type DTYPE_NOTE.
DTYPE_NUMBER	Number	22	Refers to the data as a number. You can control the appearance of numeric values through the Windows Control Panel, or you can specify an explicit format mask using the following symbols: Zero (0). Specifies the position of a mandatory digit. Pound sign (#). Specifies the position of an optional digit. Comma (,). Specifies the position of the thousands separator (you specify the character in the Windows Control Panel). Period (.). Specifies the position of the decimal separator (you specify the character in the Windows Control Panel). Trailing period (.). Specifies default display precision. Plus sign (+). Specifies the position and appearance of negative value indicator (plus sign if positive, minus sign if negative). Minus sign (-). Same as plus sign.
DTYPE_NUMBER	Number	22
DTYPE_PHONE	Number	40	Refers to the data as a phone number. The DisplayFormat property is ignored for values of this type.
DTYPE_TEXT	Varchar	2 KB	Refers to the data as a string less than or equal to 2000 bytes; the default is 255. The DisplayFormat property is ignored for values of this type. You can use ForceCase = "Upper" or ForceCase = "Lower" to force the text to all uppercase or all lowercase after the end user tabs out of the field. You can use ForceCase = "FirstUpper" to force the first letter of each word to uppercase after the user steps off the record. Otherwise, the text is in mixed case as the user entered it.
DTYPE_TIME	Time	7	Refers to the data as a time. When the time is retrieved, any additional information (such as date) is ignored. You can set the appearance of time values through the Windows Control Panel, or you can specify an explicit time format using the following symbols: HH. Hour (based on 24-hour clock) without a leading zero. H. Hour (based on 24-hour clock) with a leading zero. hh. Hour (based on 12-hour clock) without a leading zero. h. Hour (based on 12-hour clock) with a leading zero. mm. Minute without a leading zero. m. Minute with a leading zero. ss. Second without a leading zero. s. Second with a leading zero. Colon (:). The position of the time separator (you specify the character in the Windows Control Panel).