4D field types

You must specify a field type for each field. Field types affect how 4D manipulates and stores data in a field and how you enter or display data in forms.
By default, 4D assigns the Alpha type to each new field. 

To modify a field type:

1. Click on the field type icon in the table image and choose the type you want in the associated menu.
   
   OR
   Right-click on the field whose type you want to change and choose the new type from the Type submenu of the context menu.
   OR
   Select the field whose type you want to change and then click in the Type area of the Inspector palette in order to display the types menu.

You can change a field type at any time, unless the field has a relation or belongs to a primary key. In this case, the menu for choosing a field type is disabled. You must remove the relation or the primary key explicitly before you can change the field type.
If you change the field type before entering any data into the field, 4D simply changes the field type. If you change the field type after entering data into the field, 4D converts the data to the new type if possible, when the data is loaded for the first time after the change. Data from a Picture field converted to any other type does not display. Data from a field converted to a Picture field type does not display.
When you convert a field type, 4D retains the field’s original value until you modify the record. For example, if a Text field contains text such as “over 10” and you change the field type to Integer, the modified field displays “10.” If you change the field back to a Text field without editing the field value, 4D displays “over 10” again.

By default, 4D supports the following field types:

• Alpha: Alphanumeric text between 1 and 255 characters,
• Text: Text up to 2 GB,
• Date: Date between the year 100 and the year 32,767,
• Time: Time in hours:minutes:seconds format,
• Boolean: A field that can only take the values TRUE or FALSE,
• Integer: Number between -32,768 and 32,767,
• Long integer: Number in the range of plus or minus 2,147,483,647,
• Integer 64 bits(1): Number on 8 bytes included between +/- 2E64,
• Real: Floating point number in the range of ±1.7E±308,
• Float(1): Floating point number,
• BLOB (Binary Large Object): Any binary object such as a graphic, another application, or any document,
• Picture: picture in one of the native formats supported by 4D (see Native Formats Supported in the 4D Language Reference manual).

(1) Be careful, these field types are only used by the SQL engine of 4D. If these fields are used in the 4D language, their values are converted internally into real numbers. 

Note: The generic term "string" indicates an Alpha or Text type and "number" indicates an Integer, Long integer, Integer 64 bits, Real or Float type.

An Alpha field contains alphanumeric characters (letters and numbers), punctuation marks, and special characters such as the asterisk (*), percent sign (%), hyphen (-), and so on. Use an Alpha field to contain any information that must be treated as text and does not exceed 255 characters in length.

An Alpha field can be associated with a standard and/or keywords index. For more information about indexing, refer to Creating and modifying indexes.

Alpha is the most common field type. Typically, you use this field type for names, addresses, telephone numbers, postal codes, and so forth. During data entry, an Alpha field accepts any character, number, punctuation mark, or special character.

Zip codes are best placed in an Alpha field for two reasons: Numeric fields do not display leading zeros and some zip codes contain a hyphen. The general rule for deciding between a numeric field type or an alphanumeric field type is make it an alphanumeric field unless it will be used in a numerical calculation or searched or sorted based on numeric values.

You can set the maximum length of an Alpha field to be between 1 and 255 characters long.

You can concatenate two or more Alpha fields. For instance, you might want to join a person’s first name and last name for the first line in a label form. You can do so using a one-line method, such as:

The variable FullName can be displayed or printed. You can also extract part of the information for use in another place (extraction of a substring). The substring can be displayed or printed.

A Text field is similar to an Alpha field, except for a few points. 

A Text field can hold up to 2 GB of alphanumeric characters. In geneéral, you use a Text field to hold large blocks of text, like comments or descriptions.

For reasons concerning optimization, the contents of a Text field can be stored outside of records (see “Stored in the record” in Field properties). In this case, the field cannot be associated with a standard index. Like Alpha fields, Text fields can be associated with a keyword index. For more information about indexing, refer to Creating and modifying indexes.

In an input form, a Text field can have scroll bars. In a printed report, the Text field area can expand as necessary to print all the information, even if it covers several pages.

During data entry, Text fields provide basic text editing features: scrolling, word wrapping within the area set for the field display, double-clicking to select a word, moving the insertion point with the arrow keys, and standard cut, copy, and paste operations. If it has the Multiline option, a Text field accepts a carriage return during data entry to create a new paragraph (an Alpha field does not). If it has the Multi-style option, the field can contain text with style variations such as one or more words that appear underlined, in bold or in a different color.

You can paste text into Text fields, including text from word processors.

Note: Another way to store text with a record is to use the 4D Write plug-in. With 4D Write, you can use different font attributes, paragraph alignments, and other word processing features that are not available in standard Text fields. As with all 4D plug-ins, the 4D Write area must be placed in a field of the BLOB type — and not the Text type. For more information about using 4D Write, refer, for example, to 4D Write, Introduction to the language in the 4D Write Language Reference manual.

Use a Date field to store date values such as Start Date, Date Purchased, Birthdate, and so on. A Date field can store any date value (month, day, year) entered in a MM/DD/YYYY format between the year 100 and the year 32,767.

Note: In the United States, dates are specified in the month/day/year (MM/DD/YYYY) format. Other countries use different formats such as DD/MM/YYYY for British systems and YY/MM/DD for Swedish systems. 4th Dimension will store the date based on the format specified by the operating system of your computer.

Use a Time field type to manage times such as Current Time, Meeting Time, Billed Time, and so on. A Time field can store any time value entered in HH:MM:SS format.

Boolean fields (sometimes called logical fields) contain TRUE or FALSE values.

You can format a Boolean field as either a check box or as a pair of radio buttons. A check box that contains a check is TRUE; empty, it is FALSE. Either the first radio button is selected (TRUE), or the second button is selected (FALSE).

You should name a Boolean field so that you can ask the question, “Is field name true?” This question is useful for searching because during a search, 4D looks for a TRUE and FALSE value in a Boolean field. For example, you might want to name a field “Male” instead of “Sex.” Your search condition can then be written “Male is equal to True,” instead of “Sex is equal to True.”

Use an Integer field type for any field that stores whole numbers, that is, numbers without decimals (record number, invoice number, and so on). Integer fields can contain whole numbers between -32,768 and 32,767.

Use a Long Integer field type for any field that stores whole numbers that are too large for an Integer field. They can contain whole numbers (no decimal) between ±2,147,483,647.

This type of field can be used to store whole numbers on 8 bytes which allows the handling of very large numbers, included between +/- 2E64. 

Note: Be careful, this type of field is only used by the SQL engine of 4D. If this field is used in the 4D language, its value is converted internally into a real number.

A Real field stores real numbers, that is, decimal numbers (price, salary, expenses, and so on). Real number fields can hold any number in the range of ±1.7E±308.
Numeric display formats are automatically based on system regional parameters. 4D replaces the “,” and “.” characters in numeric display formats by, respectively, the thousands separator and the decimal separator specified in the operating system.

Note: In the 4D database engine, real number comparisons are always performed with an epsilon value of 10^-6 so as to obtain a sufficient level of accuracy. To ensure the consistency of data and calculations, this epsilon value cannot be changed. More particularly, the database engine does not take into account the SET REAL COMPARISON LEVEL command, which only applies to processing carried out in the language of 4D. Due to the inherent imprecision of calculations performed on real numbers, we do not recommend using this type of data to store precise values such as identifiers.

This field type is used to store floating point numbers. These types of numbers store real values without any loss of accuracy. 

Note: Be careful, this type of field is only used by the SQL engine of 4D. If this field is used in the 4D language, its value is converted internally into a real number.

Blob (Binary Large Object) fields store binary documents of any kind. For example, you can store documents created by other applications, scanned pictures, or other applications. A BLOB can be as large as 2 gigabytes. When you are working with a record that contains a BLOB field, the entire BLOB is loaded into memory. You can use a BLOB field to store entire desktop documents within your database. You can also write the contents of a BLOB field to a desktop document. For example, you can use a BLOB field in a document management system that stores documents in the database and delivers them to users upon request.

For reasons concerning optimization, the contents of BLOB fields are stored outside of records. BLOBs are only loaded when necessary, for example once the record being searched for has been found. 

The contents of a BLOB field are not displayed on-screen since a BLOB can represent any type of data.

Picture fields are used to store digitized photographs, diagrams, maps, and illustrations created using a graphics application. The pictures are kept in their native format. Some graphic applications store extra information with pictures that may provide special instructions for output devices such as a PostScript™ printer or, beginning with 4D v12, metadata. This information “tags along” when the picture is copied or pasted into a Picture field and can be used by 4D when printing the picture to an appropriate output device or, in the case of metadata, using the GET PICTURE METADATA and SET PICTURE METADATA commands.

For reasons concerning optimization, the contents of Picture fields are stored outside of records. Pictures are only loaded when necessary, for example once the records being searched for has been found. Since 4D v13, you can also choose to store pictures outside of the data file (see External data storage).

4D can memorize a default name for each Picture stored in a field. This means that you can set a default file name when saving the contents of a Picture field to a disk file through a user export or using the WRITE PICTURE FILE command (when you pass an empty string in the fileName parameter). If the contents of the field is copied into a variable or another field, its default name is also copied.

You can associate a default name with a picture stored in a Picture field in two ways:

• By programming, using the SET PICTURE FILE NAME command. You use this command to associate a default file name with the picture. You can also use the Get picture file name command to find out the default name of a picture.
• Automatically, when the contents of a picture file is imported into a Picture field using the context menu or using the READ PICTURE FILE command: in this case, 4D memorizes the name of the original picture file.

This is illustrated in the following sequence:

1. The user imports the file named logo64.png in a Picture field:
   
2. Next the user saves the contents of the Picture file: the name logo64.png is provided in the dialog box (note that this is a default name and can be changed):