|
Creating a Formula Field
To create a formula-field, you define an expression that computes for a specific value based on the values of other fields. See Creating Expressions for more information.
Note that when you are creating several similar fields you can save time by using the first definition as the starting point for subsequent definitions. Refer to Duplicating Fields for details.
Steps:
-
Go to Table Design.
-
Select Add from the Table Design ribbon, and select Formula Field from the drop-down.
-
On the General tab, enter an appropriate name for the calculated field in the Name box, and then click on the green check icon
to accept. -
From the Type drop-down list, select the appropriate field type. The available types are Character, Date/Time, Numeric and Memo. For a description of each option see Changing a field's type.
-
For numeric fields, specify the decimal accuracy in the Decimals box. Enter the number of decimal places that you want to display for this field.
-
From the Format drop-down list, select the desired setting.
-
Enter the desired column width in the Display Width box. The Display Width setting affects only the appearance of the data on screen; the underlying data is not affected. If the column width of a character field or a date field is not wide enough to accommodate a field value, the field value is truncated on screen. If the column width of a numeric field is not wide enough to accommodate a field value, the field value displays as pound signs (####).
-
Specify the desired alignment for the calculated field by selecting one of the following:
-
Left: Select for a left-aligned field.
-
Right: Select for a right-aligned field.
-
Center: Select for a center-aligned field.
-
Select the Hidden check box if you don't want the calculated field to be visible in the Table window. Note: Hidden fields can be easily viewed by selecting Hide/Display fields from the View group of the Table tab.
-
Click on the Formula tab. The Formula tab displays all available fields, operators and functions that you may use in your calculated field expression. When you click on an item, a short description of the item appears in the status bar at the bottom of the dialog. Double-clicking an item inserts it into the calculated field expression.
-
Enter a calculated field expression in the Expression box at lower half of the panel You can choose fields, operators and functions to add to your expression from the lists or you can type the expression manually. You can clear the Expression box by clicking the Cancel button. Note: For detailed information on creating expressions, including examples, see Creating expressions.
-
Choose Accept to accept the formula field definition, otherwise choose Cancel. You are brought to Table View, with the new formula field displayed at the right-most column.
Field names may be up to 62 characters in length and may contain uppercase and lowercase characters, spaces, and punctuation marks. However, periods (.), exclamation points (!), accent graves (`), and brackets ([ ]) may not appear in any part of the name. As well, names may not begin with spaces or underscores (_). If a name is entered with leading spaces, the name is accepted but the leading spaces are trimmed.
Note: The format settings available are determined by which field type you specified in step 5 above. For example, if you select the Date/Time field type, the Format options available are General, Short Date, Long Date, Short Date & Time, Long Date & Time, and Time. If you select the Numeric field type, the Format options available are General, Thousands, Currency, Percentage and Time span. If you select the Character or Memo field types, only the General Format option is available. For a description of each of these format options see Changing a field's type.