Basic Database Tools

Schema Menu Reference : Field Editor option : Add

Add

Use the Add option from the Field Editor dialog box to create a new field in the selected table. When you choose this option, a window similar to the one shown in the following figure appears.

Figure 28. Field Editor Add option window

The Field Editor Add option window contains the following fields:

Field-Name — Specifies the field name. Names can be up to 32 characters long and can consist of alphabetic characters, digits, and the characters $, &, #, %, -, and _. Field names must begin with A–Z or a–z. You cannot use ABL keywords as field names. Field names are not case sensitive.

Format — Specifies the length of a field and the way the data is shown on the screen and in printed reports. The Field Add Option window automatically supplies a default format for each data type, but you can change it. You can also override formats you define in the Data Dictionary by using the FORMAT option with the Format phrase. For more information about formatting, see OpenEdge Getting Started: ABL Essentials.

A description of the default format for each of the data types follows:

Character — The default format for a character field is x(8). The x represents an alphanumeric character position and the 8 indicates the number of characters to display. The following table describes the different symbols you can use to define a character format.

Table 11. Character format symbols
Symbol	Description
x	Represents any character.
n	Represents a digit or a letter. A space is not allowed.
a	Represents a letter. A space is not allowed.
!	Represents a letter that is converted to uppercase during input. A space is not allowed.
9	Represents a digit. A space is not allowed.
(n)	A number that indicates how many times to repeat the previous format character. For example, !(5) is the same as !!!!! and represents 5 characters that are to be converted to uppercase when entered.
fillchar	Represents any character or characters you want to fill a display. For example, if you display the value abc with a format of x(3)*, the displayed value is abc*. To use X, N, A, !, and 9 as fill characters, you must precede the character with a tilde (~). To use a left parenthesis ( ( ) as a fill character after a non-fill character, you must precede it with a tilde (~). See OpenEdge Getting Started: ABL Essentials for more information about fill characters.

The following table shows examples of the different formats of a character field.

Table 12. Character display format examples
Format	Value in field	Display
xxxxxxxx	These are characters	These ar
x(9)	These are characters	These are
x(20)	These are characters	These are characters
xxx	These are characters	The
AAA–9999	abc1234	abc–1234
(999) 999–9999	6172754500	(617) 275-4500
!!	ma	MA

Trailing spaces are truncated in character fields. If a character field contains only one space, it is truncated to a null value. You can use the TRIM function to truncate leading and trailing spaces.

Integer, INT64, and decimal — The default display format for an integer or an INT64 field is –>,>>>,>>9. The default display format for a decimal field is –>>.>>9.99. The following table describes the format characters for numeric display formats.

Table 13. Decimal and integer format characters
Character	Description
( )	The Data Dictionary displays parentheses if the number is negative. If you use one parenthesis (left or right), you must use the other.
string1	A string made up of any characters except plus (+), minus (-), greater than (>), less than (<), comma (,), digits (0–9), letter z (z or Z), asterisk (*), or period (.).
+	The Data Dictionary replaces this character with a plus sign if the number is positive, and a negative sign if the number is negative. You can also use only one plus or minus sign or CR, DR, or DB, or one set of parentheses in a numeric data format. The position of the plus sign in the number format can also hold a digit if the number is positive.
–	When you use this character to the left of the decimal point, the Data Dictionary replaces this character with a minus sign if the number is negative and a space or null if the number is positive. When you use this character to the right of the decimal point, the Data Dictionary replaces this character with a minus sign if the number is negative and a space if the number is positive.
>	The Data Dictionary replaces this character with a digit if that digit is not a leading zero. If the digit is a leading zero, the Data Dictionary replaces this character with a null and moves characters to the left one space to the right, if you are using top labels. If you are using side labels, the Data Dictionary left-justifies the characters.
,	The Data Dictionary displays this character as a comma unless it is preceded by a > or Z. If the comma is preceded by a > and the > is replaced by a leading zero, the Data Dictionary replaces the comma with a null. If the comma is preceded by a Z and the Z is replaced by a space, the Data Dictionary replaces the comma with a space.
9	The Data Dictionary replaces this character with a digit, including cases where the digit is a leading zero.
Z	The Data Dictionary replaces this character with a digit. If the digit is a leading zero, Z suppresses that digit and puts a space in its place.
*	The Data Dictionary replaces this character with a digit. If the digit is a leading zero, the Data Dictionary replaces that zero with an asterisk (*).
.	This character represents a decimal point and is displayed as a period (.).
<	The Data Dictionary uses this character in conjunction with > to implement floating-decimal format. The < character (up to 10) must appear to the right of the decimal and be balanced by an equal or greater number of > characters left of the decimal. The Data Dictionary displays a digit in a position formatted with < when the corresponding > is a leading zero (and the stored value has the required precision).
DR, CR, DB	The Data Dictionary displays these characters if the number is negative. If the number is positive, the Data Dictionary displays spaces in place of these characters. The Data Dictionary does not treat these characters as sign indicators when you specify string2; the Data Dictionary considers them part of string2.
string2	A string made up of any characters except plus (+), minus (-), greater than (>), comma (,), any digit (0–9), letter z (z or Z), or asterisk (*).

When specifying a numeric data format, you must use at least one of the following characters: 9, Z, *, or >.

The following table shows examples of the different formats for a numeric field.

Table 14. Numeric display format examples
Format	Value in field	Display
9999	123	0123
9,999	1234	1,234
$zzz9	123	$123
$>>>9	123	$123 Note: This display value is right-justified if it has a column label, and left-justified if it has a side label.
$–>,>>9.99	1234	$1,234.00
$>,>>9.99	1234	$1,234.00
#–zzz9.999	–12.34	#–12.340
Tot=>>9Units	12	Tot=12Units
$>,>>9.99	–12.34	????????? Note: There is a negative sign in the value –12.34, but the display format of $>,>>9.99 does not accommodate that sign.
$>,>>9.99	1234567	????????? Note: The value 1234567 is too large to fit in the display format of $>,>>9.99
>>,>99.99<<<	12,345.6789	12,345.68¹²
>>,>99.99<<<	1,234.5678	1,234.568 ³
>>,>99.99<<<	123.45	123.45⁴
>>,>99.99<<<	12.45678	12.45678⁵

¹ Floating-decimal display format. The < character must follow the decimal point and be balanced by an equal or greater number of > characters. The full value is displayed if it can fit in the display format. If the value is too large to fit in the display format, the decimal portion of the value will be rounded so that the value can fit.

² Floating-decimal display format. The < character must follow the decimal point and be balanced by an equal or greater number of > characters. The full value is displayed if it can fit in the display format. If the value is too large to fit in the display format, the decimal portion of the value will be rounded so that the value can fit.

³ Floating-decimal display format. The < character must follow the decimal point and be balanced by an equal or greater number of > characters. The full value is displayed if it can fit in the display format. If the value is too large to fit in the display format, the decimal portion of the value will be rounded so that the value can fit.

⁴ Floating-decimal display format. The < character must follow the decimal point and be balanced by an equal or greater number of > characters. The full value is displayed if it can fit in the display format. If the value is too large to fit in the display format, the decimal portion of the value will be rounded so that the value can fit.

⁵ Floating-decimal display format. The < character must follow the decimal point and be balanced by an equal or greater number of > characters. The full value is displayed if it can fit in the display format. If the value is too large to fit in the display format, the decimal portion of the value will be rounded so that the value can fit.

If you use the European Numeric Format (-E) startup parameter, the Data Dictionary interprets commas as decimal points and decimal points as commas when displaying or prompting for numeric values. However, always enter formats in the Data Dictionary as described in the following table. For more information about formatting in procedures, see OpenEdge Getting Started: ABL Essentials.

Date — The default date format is mm/dd/yy. Date formats specify a two-digit month and a two-digit day. You can use a slash (/) or a hyphen (-) as a separator. You can specify a year with two or four digits.

When -yy is set at 1950, the default, the Data Dictionary determines if the two-digit value in the date is greater or less than 50. If the date value is greater than 50, the Data Dictionary writes the date for the twentieth century. If the date value is less than 50, the Data Dictionary writes the date for the twenty-first century. For example, if you start the Data Dictionary with -yy 1950, years 50-99 are treated as 1950-1999, and years 00-49 are treated as 2000-2049.

The following table shows some date display format examples.

Table 15. Date display format examples
Format	Value in field	Display
99/99/99	8/10/2001	08/10/01
99/99/9999	08/10/2090	08/10/2090
99-99-99	08/10/1993	08-10-93
99-99-99	08/10/2090	???????? Note: The value 8/10/2090 is too large to fit into the display format. If a field's value does not fit in the display format, then the Data Dictionary displays each integer as a ?.
999999	08/10/1997	081097
999999	08/10/01	081001
99999999	08/10/2001	08102001

DATETIME — Stores date values as year, month, and day. Stores time values as hours, minutes, seconds and milliseconds. The following table shows a DATETIME display format example.

Table 16. DATETIME display format examples
Format	Value in field	Display
99/99/99 HH:MM:SS.SSS	8/10/01/11:09.32.213	08/10/01/11:09.32.213

DATETIME-TZ — Stores date and time values as offset from the Coordinated Universal Time. The following table shows a DATETIME-TZ display format example.

Table 17. DATETIME-TZ display format example
Format	Value in field	Display
99/99/9999HH:MM:SS.SSS+HH:MM	8/10/2003/11:09.32.213+02:00	Same as value in field

Logical — The default logical format is YES/NO. You can define any strings to represent those TRUE/FALSE values. If you define your own logical values, the false value cannot begin with "y" or "t", and a true value cannot begin with "n" or "f". If input is coming from a file and you have defined a format for a logical field that is something other than TRUE/FALSE or YES/NO, you can still use TRUE/FALSE or YES/NO as input to that logical field. The following table shows logical display format examples.

Table 18. Logical display format examples
Format	True	False
yes/no	yes	no
Yes/No	Yes	No
true/false	true	false
shipped/waiting	shipped	waiting

Label — Specifies the label that represents the field in windows or in printed reports. The default value is a question mark (?). If you leave the question mark and do not supply a label, the Data Dictionary uses the field names as the label. If you replace the question mark with a space, the Data Dictionary uses no label for the field. Labels can be up to 30 characters long. The following table shows field label examples.

Table 19. Field label examples
Format	Label you define	Label displayed
Name	?	Name
Curr-bal	Unpaid Bal	Unpaid Bal
Sales-rep	space	–

You can override the labels you define in the Data Dictionary by using LABEL, NO-LABEL, or COLUMN-LABEL options in a Format phrase, or NO-LABEL in a Frame phrase. The COLUMN-LABEL is used only for fields that do not have SIDE-LABEL. See OpenEdge Getting Started: ABL Essentials for more information about these phrases.

Column-label — Specifies that you want to use a different label when the data are listed in columns. If you do not specify a column label, the Data Dictionary uses the label specified in the Label field. If you do not enter a label, the Data Dictionary uses the field name. You can override these labels by using NO-LABEL or COLUMN-LABEL options in a Format phrase, or NO-LABEL in a Frame phrase.

If you want the column label to have more than one line (stacked), separate each line of the label with an exclamation point (!). For example, if you want the label for the Curr-bal field to be Unpaid Balance, with the word "Unpaid" displayed above the word "Balance," enter Unpaid!Balance for the column label. If you want to use the exclamation point as one of the characters in a column label, you must use two exclamation points (!!). Any spaces to the right or left of the exclamation point become part of the label.

Initial — Defines the initial value for the field. Each data type has an initial value. When you create a new record for a table, each field contains this initial value. You can change a field's default initial value when you define the field. The following table lists the default initial values for each data type.

Table 20. Field initial values
Data type	Initial value
BLOB	Unknown value (?)
Character	Null string (displays spaces)
CLOB	Unknown value (?)
DATETIME	?
DATETIME-TZ	?
Integer	0
INT64	0
Decimal	0
Logical	no (false)
Date	Unknown value (?) (displays spaces)
Raw	Null string (displays spaces)
RECID	Unknown value (?) (displays spaces)

You can use a question mark (?) as a special character to represent an Unknown value. This lets you handle data even when some critical item of information is not yet known. If you put a single question mark in any field, the Data Dictionary treats the item of data as an Unknown value (?).

You can also use TODAY as the initial value for a date field. When you create a new record, the Data Dictionary fills in the current date as the initial value for the field.

Component of View — Displays information only. It tells you whether the field is used in an SQL view. When you use a field as a component of an SQL view, you cannot delete it.

Component of Index — Displays information only. It tells you whether the field is a component of an index.

Position — Displays the order number.

Data-Type — Determines the kind of data values the field can store. The following table describes the available data types.

Table 21. Data types
Data type	Description
BLOB¹	Binary Large Object. A collection of binary data stored as a single unit in a database management system. BLOBs are mainly used to store large objects such as image, sound or video files. BLOBs are a column value in a record. The BLOB column is occupied by a locator that points to a separate assigned area in the database where the BLOB resides.
Character	Contains data of any kind. Character data can include uppercase and lowercase characters and can consist of alphabetic characters, digits, and the characters $, &, #, %, –, and _. Although the Data Dictionary allows character field display formats of up to 255 characters, restrict the format length of a character field to the input/output line width of your terminal (typically 80 characters) by specifying the appropriate length in the Format field.
CLOB²	Character Large Object. Similar to a BLOB, the CLOB differs in that it consists solely of character data. There are two types of CLOBS. A DBCODE PAGE CLOB has all of its records in the dbcodepage and uses the dbcollation for comparisons.
Date	Contains dates from 1/1/32768 B.C. to 12/31/32767 A.D., inclusive. You can specify dates in this century with a two-digit year, such as 8/9/03, or a four-digit year, 8/9/2003. Dates in other centuries require a four-digit year. If you enter dates without the year, the Data Dictionary assumes the current year. If you supply spaces as the value of a date field, the Data Dictionary stores that field as an Unknown value (?) and displays it as spaces.
DATETIME	Maps to SQL "timestamp" data type. Stores date values as year, month, and day. Stores time values as hours, minutes, seconds and milliseconds. DATETIME value limits for dates are 1/1/32768 B.C. to 12/31/32767 A.D. DATETIME value limits for time are 00:00:00.000 to 23:59:59.999.
DATETIME-TZ	Maps to SQL "timestamp with time zone" data type. Stores date and time values as offset from the Coordinated Universal Time.
Integer	Contains only whole numbers. They can be positive or negative, from –2,147,483,648 through 2,147,483,647, inclusive. If you enter spaces as the value of an integer field, the Data Dictionary stores the value of that field as 0. The default initial value for the field is 0.
INT64	Contains only whole numbers. They can be positive or negative, from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807, inclusive. If you enter spaces as the value of an INT64 field, the OpenEdge RDBMS stores the value of that field as 0. The default initial value for the field is 0.
Decimal	Contains decimal numbers up to 50 digits. You can use up to 10 digits to the right of the decimal point. If you enter spaces as the value of a Decimal field, the Data Dictionary stores the value of that field as 0. When you choose decimal as the data type, the Data Dictionary enables the Decimal field. You specify the number of decimal places in the Decimal field.
Logical	Contains logical values such as yes/no or true/false.
Raw	Contains the value of a field from a non-OpenEdge database that has a data type with no equivalent in the OpenEdge development environment. When you want to work with Raw data, you generally work with the data at the byte level. A Raw value cannot be displayed.
Recid	Contains the record ID.

¹ This data type is currently not available on supported on the ODBC DataServer.

² This data type is currently not available on supported the ODBC DataServer.

BLOB — If you specify a data type of BLOB or CLOB, the Data Dictionary prompts you for more information.

When you assign the BLOB data type to the field you are adding to a table, the Blob Field Attributes dialog box appears, as shown in the following figure.

Figure 29. Blob Field Attributes dialog box

The Blob Field Attributes dialog box contains the following fields:

Field Name — Enter the name of the field.

Area — Scroll through the list of available areas of the database to which you can assign the BLOB.

Max Size — Enter the maximum size of the BLOB. Specify any number between 1 and 1073741823 bytes.

You can enter the size of the BLOB in bytes, kilobytes, megabytes, or gigabytes. However, if you enter only numbers, the Data Dictionary assumes you've entered the BLOB's size in bytes.

Order — Define the BLOB's order or accept the default value.

Desc — Provide a description of the field.

CLOB — When you assign the CLOB data type to the field you are adding to a table, the Clob Field Attributes dialog box appears, as shown in the following figure:

Figure 30. Clob Field Attributes dialog box

The Clob Field Attributes dialog box contains the following fields:

Field Name — Enter the name of the field.

Area — Scroll through the list of available areas of the database to which you can assign the CLOB.

Max Size — Enter the maximum size of the CLOB. Specify any number between 1 and 1073741823 bytes.

You can enter the size of the CLOB in bytes, kilobytes, megabytes, or gigabytes. However, if you enter only numbers, the Data Dictionary assumes you have entered the CLOB's size in bytes.

Order — Define the CLOB's order or accept the default value.

Case Sensitive — Specify either yes or no.

Code Page — Specify a code page for the CLOB. Make a selection by choosing the default database code page or scroll through the list of available code pages to make another selection.

Collation — Specify database collation.

Desc — Provide a description of the field.

Extent — Defines the extent of an array field. Most fields represent a single value. However, array fields contain multiple elements. For example, the Mnth-shp field of the time table is an array field. It contains 12 elements, one for every month of the year. The extent is the number of elements contained in an array. If you define a field with an extent greater than 0, that field is an array field.

Decimals — Defines the decimal places for a field. When you define a decimal field, you must define the number of digits to the right of the decimal point. For example, Max-credit has been defined for two digits to the right of the decimal point to accommodate dollars and cents. If the field is not a decimal, you cannot enter a value.

Order — Specifies the default display order of a field. By default, the Data Dictionary numbers fields in the order you enter them, by increments of 10. This defines where the Data Dictionary lists the field in the field selection list in this table. You can set the order for this field in the table. This display order is not related to the order in which the data are stored in your database. In addition, you can override the Data Dictionary display order of fields in your procedures by naming the fields in the order you want to display them.

The default order numbering is in increments of 10 to let you insert fields in between. The numbers do not have to be contiguous, nor do they have to follow in even-numbered increments. For example, if you decide to add a field called Category to the customer table, and you want it to appear by default on your window between Cust-num and Name, you might assign 15 as its order value. If you want to change the order number increments to 20, you can do so by choosing the Reorder Fields option of the Schema menu. From the same menu option, you can also change the order values to reflect the alphabetical order of the table names.

Mandatory — Specifies if the field is mandatory. If you define a field as mandatory, it cannot contain the Unknown value (?). However, it can have a space as its value. The default value for mandatory is no. If you accept no for this field, you indicate that the Unknown value (?) is an allowed value for the field.

Case-sensitive — Specifies if the field is case sensitive. The default value is no. Because case-sensitive fields depart from the OpenEdge standard, they are not recommended. However, if you require strict adherence to the ANSI SQL standard or if you are using a DataServer that supports case sensitive fields, you might have to define all character fields as case sensitive. Once you define a field as case sensitive, you can redefine it, unless it is a component of an index. If a field is a component of an index, you cannot change its case sensitivity unless the index is undone.

You can index case-sensitive fields and group them with case-insensitive field components in an index. With case-sensitive indexes, JOHN, John, and john are all unique values. However, they do not sort next to each other. All uppercase letters sort ahead of all lowercase letters. Define case-sensitive variables to hold values moving to and from case-sensitive fields.

Valexp — Specifies the validation criteria for the field. You can enter up to 63 characters on each of the four lines. For messages that exceed 252 characters, you can specify an include file enclosed in brackets (use the syntax: filename.i). There is no default value.

Note: You should use field validation for backward compatibility for procedure-driven applications. For event-driven programs, use field ASSIGN triggers instead of field validation.

When a user enters a value for a field, you might want to test it to make sure it is a valid entry for the field. The Valexp option lets you define a test or validation expression. The expression must be logical; that is, it must be a valid ABL expression that produces a true or false result.

For example, the validation expression for the Cust-num field in the customer table is cust-num > 0. When the user enters a customer number, it is validated against valexp. If the number is greater than 0, valexp is true, and the validation succeeds. If the number is less than 0, the validation fails. The text is then displayed in the Valmsg option.

When you write procedures, you can override any validation expressions you define in the Data Dictionary. If you define validation for an array field in the Data Dictionary, the validation only works if you update the entire array. The validation does not work if you update the array elements individually.

Valmsg — Defines the validation message for a field. You can enter up to 63 characters on each line. You cannot define (and do not need) a validation message unless you specify a validation criteria for a field. If the result of Valexp is false (the validation fails), OpenEdge displays the text specified in Valmsg. For example, the validation expression for the Cust-num field in the customer table is cust-num > 0. When the user enters a 0 for the customer number, OpenEdge displays this message:

Customer number must be greater than zero.

Because ABL treats the message you define as constant (literal) text, it cannot contain references to the number of the fields or variables. If you want to use fields, variables, or expressions in validation messages, use this VALIDATE option in a Frame phrase.

Help — Defines help information for the field. For certain fields, users might be unsure of the kind of data they need to enter. Therefore, you can specify a help message to indicate what information to enter. OpenEdge displays this message whenever users are prompted for input to the field. For example, the State field in the customer table displays this help message:

Enter standard state abbreviation.

Desc — Describes the field. You might want to document the purpose of a field just as you might supply a description for a table. OpenEdge does not use this option when running procedures; it is strictly to help you document your application.