The Field Definition Dialog

The Field Definition dialog is made up of several sections, (e.g., Field Name, Type, Derivation, Validation Formula, Help, and Field Data options). Each section is explained in the order it appears (from top to bottom and from left to right) beginning with Field Name below.

If you define a form over an existing table, DataEase automatically uses the column definition to assign the field's characteristics.
Specify the Field Name and appearance only.

Field Name

identifies the field and the column in the associated database table that stores entered data. When you create a form that:

Defines a table, DataEase creates a new data column using the name you specify.

Uses a table, you choose the name of an existing data column from a drop-down list that DataEase displays.

NOTE: You cannot define a field without a Field Name. If you do not specify a Field Name, DataEase provides a default name (e.g., Field1).

Default Field Names

DataEase automatically creates a default Field Name for each field (e.g., the first field on the form is assigned the default name FIELD1, the second field is assigned FIELD2, etc.). To change the default, simply type another name.

Custom Field Names

A Field Name can be up to 30 characters long, including letters, numbers, the underscore character, and spaces. Do NOT use characters other than A-Z, a-z, 0-9, plus the space character and underscore character.

If a field with the same name as the field you are creating (or pasting) already exists on the document, DataEase appends a numeric suffix to the Field Name (e.g., DataEase changes the field named ADDRESS to ADDRESS2).

Each SQL engine type enforces its own restrictions on the length, characters, and words that make up a column name.

NOTE: Do not use DQL commands or function names as Field Names.

Field Definition Display Options

The four radio buttons, Type, Derivation, Validation Formula, and Help, (beneath the Field Name) control which options DataEase displays on the Field Definition dialog.

The four Field Definition Display controls are:

Field Data

The Field Data options on the right side of the Field Definition dialog let you specify whether a user can, cannot, or must enter
data in the field when filling in a record during record entry.

Required

prevents a user from saving the current record until data is entered in the field. The CLUB ID field on the MEMBERS form

is an example of a Required field. The Required DataEase native Field characteristic is equivalent to the Not Null column

attribute in an SQL database table.

Virtual

does not store the field value in the table. A Virtual field value is automatically rederived each time the record is displayed

or printed. The CLUB NAME field on the RESERVATIONS form (which is derived by the formula lookup CLUBS CLUB NAME)

is an example of a Virtual field.

Advantages    Disadvantages

Field is automatically rederived so  Field can't be assigned the Indexed or Unique attribute.

it always displays the correct value. 

Virtual attribute saves disk space Performance is decreased when a Virtual field is in a Derivation

since the value is not stored.  formula or is used to enter a record selection criteria in QBF or QBM.

NOTE: A Virtual field does not save data in the associated database table.

Prevent Entry

prevents a user from entering data in the field. Choose Prevent Entry for any field which has a value derived by a Derivation Formula (e.g., lookup CLUBS CLUB NAME). The RESERVATION ID field on the RESERVATIONS form is a Prevent Entry field.

NOTE: The cursor can only be placed in a Prevent Entry field when you are searching for records using QBF.

Indexed

creates an Indexed field attribute that helps DataEase find and/or sort records quickly. When you search for a particular record
using an Indexed field, DataEase uses the index just as you look in the index of a book to find a particular subject.

If the document defines or uses a:

NOTE: When you remove the Index attribute from a field that defines a Native table, DataEase deletes the corresponding Index file.

Which Fields Should Be Indexed?

We recommend that you index fields that are used:

• For searching and sorting.

• As Match (key) fields in a relationship.

NOTE: For maximum performance, Index as few fields as possible. If you find you need additional indexes, you can add them later.

Benefits and Costs of Indexing Fields

You should weigh the following benefits and costs of Indexing before creating a field:

Indexing lets DataEase work much faster when:

• Selecting records.

• Grouping and/or sorting records.

• Matching fields in related forms (performing lookups).

• Finding duplicate records.

The disadvantages of Indexing native tables (only) are:

• Data entry functions run more slowly since it takes longer to add or modify records in each Index file.

• Index files take up additional storage space on your hard drive.

Fast Text Index

Lets DataEase find matching records quickly and reduces the size of the index file. When you add a Fast Text Index to a field, DataEase compresses or collapses the size of the field's index key, optimizing query performance.

We recommend that you add a Fast Text Index to a field that:

• Is a Match field in a relationship.

• You frequently use for exact match searches(e.g., PRICE=495.00).

However, we recommend that you do not add a Fast Text Index to a field that:

• You frequently use for range selection searches(e.g., PRICE<650.00).

• Use to sort records. DataEase cannot sort records based on the values in a field with a Fast Text Index.

You can create a Fast Text Index on any Text or Numeric String field greater than six characters in length. You'll notice the greatest performance increase with fields longer than 40 characters. DataEase automatically creates a Fast Text Index on any Indexed Text or Numeric String field longer than 117 characters.

Composite Indexing

Most SQL engines support composite (or concatenated) indexes in addition to indexes based on single fields. A composite index identifies records based on the values in two or more fields (columns).

For example, if you indexed both the LAST NAME and the FIRST NAME fields on the MEMBERS form, DataEase orders the records in the Index file in alphabetical order by LAST NAME, then by FIRST NAME.

Clustering

Clustering is a server feature in which data is physically stored in indexed order to speed data retrieval. The field (or column) used to index clustered data is called the cluster key. The cluster key is made up of all the unique indexed fields in the table.

Clustering can greatly improve the performance of certain queries that select records based on a contiguous range of indexed values.

Unique

creates a Unique field attribute that prevents duplicate records and preserves data integrity. MEMBER ID is a sample Unique field.

When To Create a Unique Field

When you create a form that:

• Defines a table - we recommend that you define at least one field as Unique. Unique fields greatly decrease the time it takes DataEase to search for or sort data in an SQL table.

• Uses an existing table - you must create a field for each Unique column in the table, or DataEase will not let you save records in the form. DataEase automatically defines a Unique Field attribute for each field defined over a corresponding Unique column.

NOTE: The combination of Unique fields on a DataEase form is called a Unique key. DataEase lets you save the same Unique field value in more than one record, as long as the combination (key) is unique.

Field Security

The Field Security options on the right side of the Field Definition dialog let you specify the minimum security level required to see (View) or enter and modify (Write) data.

View

Lets you choose the minimum security level required to view data in the current field. If your security level is less than the View security level, DataEase displays a blank field. The default View security level is Low 3.

Write

Lets you choose the minimum security level required to write data in the current field. If your security level is less than the Write security level, DataEase prevents the cursor from entering the field. The default Write security level is Low 3.

The seven DataEase security levels (in order of decreasing security) are:

High, Medium 1, Medium 2, Medium 3, Low 1, Low 2, Low 3

NOTE: The document-level security settings specified on the Document Properties dialog take precedence over field-level security.
Each SQL engine type requires that you have the appropriate server permission in addition to the necessary DataEase security level.

Summarize

When you click on Summarize, DataEase displays the controls that let you specify statistical operators to derive the current field value.

How to Define a Summary Field

1. Draw the Field area on the screen by clicking and dragging the Field cursor on a record in the document.

2. DataEase displays the Field Definition dialog.

3. Choose the Summarize option located near the Field Name on the Field Definition dialog.

4. DataEase displays the Summarize options on the Field Definition dialog.

5. Specify the Summary Level (document), Summary Field (field), and Summary Type (statistical operator).

6. When you are finished specifying the Summary controls, click OK.

NOTE: After you create the SUMMARY PRICE field, you can delete the PRICE Subform.

K Summarize Dialog

Summarize on Level

displays the names of the current document and all documents that are nested on the current document. Choose the document that contains the field you want to summarize.

Summarize on Field

displays the names of all the fields that appear on the highlighted document.

Summary Type

lists all the statistical operators available for the highlighted field.

The table below summarizes the statistical operators that can be used to create a Summary field.

Report Summary Fields

In addition to using a Summary field in a form document, you can use a Summary field in report document.