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 (with 13 significant digits),
• 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),
• Object: attribute/value pairs in JSON notation, up to 2 GB.

(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 general, 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.

Notes:

• 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. 4D will store the date based on the format specified by the operating system of your computer.
• Although a date field can store dates up to the year 32 767, certain operations passing through the system impose a lower limit. 

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):
   

Object fields store attribute/value pairs of different kinds with no predefined data schema. The stored data structure is not necessarily the same between different records. For example, a [Person]Address Object field can contain different attributes depending on the city, the country, and so on:

record1= {"street1":"Cotton Treasure Grounds", "street2":"Place Corners", "state":"MD",...} 
record2= {"street1":"Umber Road", "Number":"28", "state":"MO",...}

The structure of 4D objects is based on the standard principle of "property/value" pairs. The syntax of these objects is based on JSON notation, but does not follow it completely:

• An attribute name is always a text, for example "Name".
• An attribute value can be one of the following types:
  
  • number (Real, Integer, etc.)
  • text
  • array (text, real, longint, integer, Boolean, object, pointer)
  • null
  • Boolean
  • date (format "YYYY-MM-DDTHH:mm:ssZ")
  • object (objects can be nested on several levels)

Warning: Keep in mind that attribute names differentiate between upper and lower case.

An Object field can be as large as 2 GB. When you are working with a record that contains an Object field, the entire object is loaded into memory. Just like Text, Picture or BLOB fields, Object fields can be stored in the data file (within records or not), or outside of the data file; this option is discussed in the External data storage section.

You use 4D’s Objects (Language) commands to manage Object fields. Use the OB Get and OB SET commands to read and write data to and from Object fields. You can also store and read arrays as attributes using the OB SET ARRAY and OB GET ARRAY commands.

You can use dedicated commands such as QUERY BY ATTRIBUTE, QUERY SELECTION BY ATTRIBUTE, DISTINCT ATTRIBUTE VALUES or DISTINCT ATTRIBUTE PATHS in order to perform queries and processing among Object fields.

Since Object fields are text-based, the contents of an Object field are displayed in a 4D form by default as text and formatted in JSON (see the following section).

Note: To work with JSON objects, you can use the commands found in the "JSON" theme.

The Object field data type allows you to define schema-less dynamic fields. These Object fields can be considered as "user-defined" or "custom" fields. In 4D, you have a choice between schema or schema-less data models. In both cases, you can execute fast indexed queries.

Also note that Object fields can simplify standard data models. For example, for a conventional "Contacts" table, a single Object field avoids the creation of dozens of fields representing every possible value - most of which are not used in 90% of cases. The information model is created on-the-fly only if required.

All commands of the Objects (Language) theme now accept an Object field as first parameter (object).

Just like standard language objects, Object field values are handled using commands from the Objects (Language) theme. For example:

Arrays are supported as well, for example:

To save modifications applied to the attributes of an object field, in most cases you will need to explicitly notify 4D of the change before saving the record. This notification is performed by reassigning the object field to itself:

This step is necessary since an object reference can be used in different places within the application, including other object fields. The 4D language cannot detect if any of the object field attributes have been modified when saving the record.

You must explicitly reassign the field to save its contents as soon as attributes are modified:

• when you handle any attributes through object notation:
  
   [Person]Info.firstName:="Jane"
 [Person]Info:=[Person]Info //mandatory to save the edits
 SAVE RECORD([Person])
• when you handle attributes beyond the first level using the OB SET command:
  
   OB SET([Person]Info.Children[0];"firstName";"Jenny")
 [Person]Info:=[Person]Info //mandatory to save the edits
 SAVE RECORD([Person])

Note: Assigning the field is not necessary when handling first-level attributes with the OB SET command:

Object fields can be used in formulas (using the standard formula editor or the EXECUTE FORMULA command). However, in this context, Object fields can only be handled using the following commands:

• OB Get
• OB Is empty
• OB Is defined
• OB Get type

For example, you can execute the following query formula:

By default, Object fields are represented as text areas in 4D forms. Within these areas, object data must be either undefined, or formatted as JSON text; otherwise, an error is returned.

For example, if you have defined the [Rect]Desc field as an Object field, you can write:

If the [Rect]Desc field is included in your form, the following contents are displayed:

You can edit the displayed values directly in the text field, or enter object data directly using standard object notation; it will be formatted automatically in JSON when you press the [Tab] key:

However, direct editing must be carried out with precaution since any misplaced space or symbol will result in a JSON parsing error and the edited data will not be saved:

It is usually more accurate to handle the contents of Object fields using the Objects (Language) and JSON commands.

Most of 4D standard features support Object fields as any kind of data. However, some advanced parts of the application are not "Object field-ready" in the current release. These parts will be updated progressively and will become available to Object fields in upcoming releases.

The following features or commands partially support Object fields through 4D formulas:

The following 4D features or commands do not support Object fields: