A formula field is a calculated value using other fields in the same table. The formula (expression) can use values from other fields, constants, or functions. For example, the formula [@field:Field1] + [@field:Field2] adds the values of two fields.
Note: Formula fields can only operate on their own record. You can’t reference a value in another record or another table.
Follow these steps to add a formula field to a table:
- Open your app, and then click Tables on the sidebar menu. (To learn how to create data tables, see Tables and Views).
- Point to the table associated with your DataPage, click Design.
- At the last row of the Name list, enter a name for the field.
- In the DataType list, select Formula.
- On the right pane, under the Options section, click Edit.
- A Formula dialog box appears, enter your formula or expression.
Tip: You can also insert built-in functions or incorporate fields of the record from the dropdown lists.
- After you have finished, click Verify Formula to confirm that the formula is valid.
Note: “Valid formula” is displayed if the formula or expression is valid. Otherwise, an error message is displayed.
- By checking the unique attribute checkbox, the result of the formula will be assigned the unique constraint, thus preventing duplicate values. Unique formula fields can have an empty formula or blank value. When a new record is being added or an existing record is being edited, if the formula field’s calculated value turns out to be non-unique, the insert or update is entirely rejected.
- Click Apply to close the dialog box, then click Save.
You can now see the new formula field in Datasheet view. Its values represent the computation of the expression that you entered in Table Design.
Facts about Formula Fields
- Each formula can contain up to 10,000 characters.
- Can be passed and received as a parameter. To receive Formula field value as a parameter, use the following syntax: [@InsertRecordFieldName].
- Available for export/import as a value in CSV, Excel, or MS Access formats.
- Can be exported as a formula in Caspio XML format and imported back as a formula.
The following are not compatible with formula fields:
- SQL subqueries.
- Aggregate functions such as SUM and AVERAGE.
- Nondeterministic functions such as GetUTCDate and Rand; however, Rand(n) is supported.
- Other formula fields.
Watch the video below to see how to create formula fields in Caspio: