Creating Default Field Values

Article
10/23/2006

If you want Visual FoxPro to fill the contents of a field automatically as you add a new record, you can create a default value for the field. The default value is applied whether you enter data through a form, in a Browse window, a view, or programmatically, and remains in the field until you enter a new value.

You create default values either through the Table Designer or through the language. You can specify default values for any data type except General.

To assign a default value to a database table field

In the Database Designer, select the table.
From the Database menu, choose Modify.
In the Table Designer, select the field you want to assign the default value to.
In the Default value box, type the field value you want displayed in all new records (enclose character fields in quotes).
Choose OK.

-or-

Use the DEFAULT clause of the CREATE TABLE command.

For example, you might want your application to limit the amount of merchandise a new customer can order until you've had time to complete a credit check and determine the amount of credit you're willing to extend to that customer. The following example creates a maxordamt field with a default value of 1000:

CREATE TABLE customer (cust_id C(6), company C(40), contact C(30), ;
                maxordamt Y(4) DEFAULT 1000)

If your customer table already included the maxordamt column, you could add a default value for the column with this command:

ALTER TABLE customer ALTER COLUMN maxordamt SET DEFAULT 1000

Using Default Values to Speed Data Entry

You can use default values to speed data entry for your application's users, enabling them to skip a field unless they want to enter a different value. For example, if your business primarily deals with domestic customers, you may want the country/region field in the customer table in a database to be filled with the name of your country/region automatically. If you're entering a customer record for an international customer, you can overwrite the name of your country/region with theirs.

Tip If one of your application's business rules requires that a field contain an entry, providing a default value helps to ensure that a particular field-level or record-level rule will not be violated.

If you remove or delete a table from a database, all default values bound to that table are deleted from the database. Stored procedures referenced by the removed or deleted default value remain even after the default value has been removed.

When you don't specify a default value, a blank value (as defined for each data type) is inserted unless SET NULL is on. This preserves backward compatibility with any existing FoxPro code you might have.

You can use .NULL. as a default value if you want the field to use null values. Whether SET NULL is on or off, if you use .NULL. as a default value, Visual FoxPro inserts .NULL. for all commands except APPEND BLANK.

Allowable Default Values

You can specify default values that are either scalar values (such as "a number") or expressions that evaluate to a scalar quantity. You can also specify any valid Xbase expression that returns a value consistent with the data type for the field.

Visual FoxPro evaluates expressions for data type when the table structure is closed. If the data type doesn't match the associated field type, Visual FoxPro generates an error. If the expression is a user-defined function (UDF) or contains a UDF, it is not evaluated.

When you create the default value through the language, the CREATE TABLE or ALTER TABLE commands will generate an error if the data types do not match. If the expression is a UDF or contains a UDF, it is not evaluated at CREATE time and no error is returned.

When Default Values Are Applied

Default values are evaluated (if necessary) and placed in the appropriate fields when the APPEND, APPEND BLANK, or INSERT commands are issued.

When you assign values with the APPEND FROM or INSERT - SQL commands, Visual FoxPro assigns default values to any fields not explicitly assigned. The APPEND FROM and INSERT - SQL commands also respect default values. However, when either of these commands is issued, defaults will not overwrite existing values in fields. If appended or inserted fields contain values, the existing value is retained as the record is appended or inserted and the default value is not applied.

Using Default Values to Auto-Populate NOT NULL Fields

Default values are particularly useful to automatically populate fields that do not allow null values. When you add a new record, default values are applied first, then each field is checked in definition order for missing information. This ensures fields designated as NOT NULL have the opportunity to be populated with default values before the NOT NULL constraint is applied.

Specifying an Input Mask

By specifying an input mask, you define the punctuation, spacing, and other format attributes of values as they are entered into the field. The values are then stored in a uniform manner that can reduce data entry errors and make processing more efficient. For example, adding a mask to a numeric field storing telephone numbers helps the user to quickly fill out the field because the punctuation and spaces are already provided by the mask.

To provide an input mask

In the Table Designer, enter the mask in the Input mask box in the Display area.

-or-
Use the DBSETPROP( ) function to set the InputMask property.

For example, the following code specifies an input mask for a date:
```
DBSetProp("orders.postalcode","field","InputMask", "99999-9999")
```