Fields

Definition

The "Definition" area of the Inspector palette configures the basic field properties. Some properties are only available when certain types of fields are selected.

Color

You can assign a color to each field name. Colors can be used to distinguish fields according to their role or attributes. For example, you can use one color for unique fields and another for mandatory ones. It is also possible to assign a color individually to each table and to each relation.

To set the color of one or more fields, select them and choose a color using:

• The Color button in the tool bar of the Structure editor,
• The Color command in the context menu of the fields,
• The Color option in the Inspector palette.

The Automatic option can be used to apply the standard original color to the field name.

Invisible

You can make a field invisible in the Application environment and for the plug-ins by selecting the Invisible property for this field. The Invisible attribute hides the field from the user. A field with this attribute does not appear in any standard 4D editors and dialog boxes that appear in the Application environment. In addition, it cannot be used by plug-ins. The following editors and dialog boxes in the Application environment are concerned:

• All Query editors,
• The Order by editor,
• The Label editor,
• The Quick reports,
• The Exporting and importing data dialog boxes,
• The Formula editor.

In each of these places, the user is unable to see or choose the field. For instance, the user cannot choose an invisible field for a design created with the Label editor.

Note: When using the editors, users have the option of saving their specifications (e.g., the query or sort they created) to disk files. In this case, any fields specified that are subsequently declared invisible will still be used in the operation. In addition, users can type the names of invisible fields in the Formula editor.

Invisible fields are displayed in italics in the Structure editor window.

Unique

Use the Unique attribute when you want to be certain that each record has a different (unique) value in this field. The Unique attribute should be used for the field that uniquely identifies each record in the table. The Unique attribute is useful to validate fields that store Employee numbers, Social Security numbers, Purchase Order numbers, and so on.

The Unique attribute prevents duplication of empty values as for any entries. An empty field cannot be duplicated in another record.

The information bar of the Structure editor indicates whether the Unique property is checked for a field.

In 4D, fields with the Unique attribute must be indexed. A visual signal indicates that an index will be created (if it does not already exist) when you select the Unique attribute for a field:

The editor prevents you from removing the index of a Unique field (None option or remove commands not available for indexes when such a field is selected). If you want to remove the index from a unique field, you need to remove the Unique attribute beforehand.

Expose as REST resource

This option controls whether the field is exposed to REST requests.

Notes

• You can also set this option at the level of the table.
• This option is used in the context of ORDA remote datastore features.

Reject NULL value input

This property is used to prevent the storage of NULL values. When this property is checked for a field, it will not be possible to store the NULL value in this field. If a field having this attribute receives a NULL value, an error will be generated. This low-level property corresponds exactly to the NOT NULL attribute of SQL.

note

In 4D, fields can also have the "Mandatory" attribute (see below). The two concepts are similar but their scope is different: the "Mandatory" attribute is a data entry control, whereas the "Reject NULL value input" attribute works at the level of the database engine.

Map NULL values to blank values

This property make the processing of "blank" values and NULL values consistent for the field via the 4D language.

Compatibility with ORDA

Using this option is not recommended with ORDA-based developments, where the NULL values are fully supported.

For compatibility reasons, NULL values stored in 4D database tables are automatically converted into default values when being manipulated via the 4D language. For example, in the case of the following statement:

$myTextVar:=[myTable]myTextField

... if the myTextField field contains a NULL value, the $myTextVar variable will contain "" (empty string). The default values depend on the data type. On the other hand, this mechanism in principle does not apply to processing carried out at the level of the 4D database engine, such as queries. In fact, searching for a “blank” value (for example $myvalue=0) will not find records storing the NULL value, and vice versa. When both types of values (default values and NULL) are present in the records for the same field, certain processing may be altered or require additional code. To avoid these inconveniences, the Map NULL values to blank values option can be used to standardize all the processing in the 4D language. This property can be used to extend the principle of using default values to all processing. Fields containing NULL values will be systematically considered as containing default values.

Notes

• The Alpha fields in UUID format that are not generated are not considered as NULL (see UUID format).
• This property is taken into account at a very low level of the database engine. It acts more particularly on the Is field value Null command.

Autoincrement

The Autoincrement property generates sequence numbers in the table. It can be used with fields of the Integer, Long Integer and Integer 64 bits types.

A field with this property is automatically incremented each time a record is created in the table. The sequence number assigned to the field matches that of the "internal counter" of the table, maintained by 4D. This internal counter can also be accessed using 4D commands Sequence number and Get database parameter and using the #N marker (see Default values).

The numbers assigned are never reused in the table, even when records are deleted. Numbers generated during a transaction that is cancelled are "lost”. The Autoincrement property corresponds to the SQL AUTO_INCREMENT attribute and can be specified using this language. The AUTO INCREMENT label also appears in the SQL information area of the Inspector for fields having this attribute.

warning

Although they are unique in the table when they are generated, autoincremented numbers are NOT recommended to fill unique ID primary key fields for records, especially when the structure is used with several data files, because in this case the internal counter could be desynchronized. In addition, the internal counter of a table could be reset using the SET DATABASE PARAMETER command. To create unique record IDs, it is strongly recommended to use UUIDs, which provide the following advantages:

• UUIDs generate IDs which are unique between all tables and databases.
• To generate UUID you can mix the Auto UUID option (see below) and the Generate UUID command.

Stored in record, data file or outside data file

This menu of options is only available for Text, BLOB, Picture and Object type fields. You can set a storage location for the field data.

In record

Data is stored with each record. Generally, you should not choose this option for fields containing voluminous data. However, it is selected by default for Text fields because this type of storage is required if you want to use "standard" B-Tree indexes. The index selection menu is hidden when Text data are stored outside of records.

In data file

Data is stored in the data file, but outside of each record. This option is selected by default for BLOB and Picture fields. You can optimize database functioning when working with voluminous data by storing texts, pictures, Blobs and objects outside of records.

Outside data file

Data is stored in separate files, outside the data file. This option is described in External data storage.

Notes

• This option is only taken into account for new records created subsequently in the table. When you set this option for a table where records have already been entered, they are not modified and the table will work in a mixed internal/external storage mode. If you want to extend this mode to the existing records as well, you need to compact the data using the Force updating of the records option.
• Data stored outside the data file are not handled by the data encryption feature, if enabled.
• This option is not available for Object type fields.

Internal storage max size

As described in the previous section, for optimization purposes, by default the data of BLOB, Picture, Text and Object type fields are stored outside of records or outside of the data file. In this configuration, it may be worthwhile, for performance reasons, to "force" data to be stored in the records when their size is limited. This functioning is particularly optimized when your application handles BLOB, Picture, text or object data of inconsistent sizes. The value entered in this area represents the size in bytes below which the data of the field will be stored in the record. For example, if you enter 30 000 for a picture field, a 20 KB picture will be stored in the record and a 40 KB picture will be stored at the location defined in the settings (in the data file or outside it). By default, the value is 0: all the data of the field is stored outside of the records.

UUID format

This property is available for Alpha type fields. It indicates that the field stores UUID identifiers. The stored data must conform to the UUID format (combination of 32 letters (A-F, a-f) and numbers (0-9)). To do this, you can use the Auto UUID property, the Generate UUID command, or any custom algorithm.

If you try to store a string that does not comply with the UUID format in this field, 4D converts it automatically. The same operation is also applied to the contents of existing non-Alpha fields that are transformed into UUID fields: when loading the records, the values are reformatted and then stored once again.

Fields with the UUID format property can be displayed in forms and remain enterable. Their contents appear in upper-case characters. You must pass through a variable if you want to display lower-case characters.

Notes

• Fields with the UUID format cannot be associated with keyword indexes nor with choice lists.
• You can create a relation between two fields that both have the UUID format but you cannot link a standard Alpha field to a field that has the UUID format.
• An UUID field that is initialized (generated) and that has the NULL value returns an empty string. An UUID field that is not generated is not NULL and returns "000..." (the number of 0s is equal to the number of characters). The property is not taken into account by non-generated UUID fields (display of "000...").

Auto UUID

This option is only active when you select the UUID format property. You can use the Auto UUID property to generate a UUID number automatically in the field.

This number is calculated automatically in the following contexts:

• when a record is created,
• when a record is loaded whose UUID field contains a Null value. This occurs more particularly for records created and saved before the UUID field is added in the table, when Map NULL values to blank values option was unchecked. Naturally, in all cases, the record must be saved in order for the automatically-generated UUID to be saved in the field.

note

When data is imported, even with this property selected, 4D does not generate a new number but uses the imported values (and transforms them when necessary if the format is not valid). However, if the value of the imported field is empty, a UUID is automatically generated.

Class

This property is available for fields of type Object (in 4D projects only). It allows you to define a class-typed object field, enhancing code completion, syntax checking, and runtime validation when typing code that involves object fields.

You can enter any valid class name in this property, including:

• User classes (e.g. cs.MyClass)
• Built-in 4D classes (e.g. 4D.File, 4D.Folder)
• Exposed component-defined classes (e.g. cs.MyComponent.MyClass)

If you enter an invalid class name, a warning is triggered and the input is rejected.

note

Non-streamable classes such as ORDA Data Model classes, file handles, web server... cannot be associated to object fields.

In your code, when assigning a value to a class-typed object field, 4D verifies that it belongs to the declared class. If not or if the object has no class, an error is triggered. Accessing unknown attributes will also raise syntax errors.

To retrieve the associated class name at runtime, use the classID property, for example ds.MyTable.MyField.classID.

Related blog post

Stricter class-based typing for objects

4D.Vector class

If you assign 4D.Vector as the class of an Object type field, you define a vector field. Such fields store embessings and can be used to query data using AI features. See the 4D.Vector class documentation to see how to query with vectors.

Note that the 4D Embedding Studio component can help you configuring and updating your 4D.Vector fields.

When you assign 4D.Vector as the class of an Object type field, a specific icon is displayed as field type in the Structure editor:

Related blog post

Vector Indexing in 4D: Faster Similarity Search for 4D.Vector Fields