Most fields in a table consist of constant values (either text, numeric, currency, or date). These values can only be updated manually by editing, or by importing from an external source.
However, there is another type of field that is based on formulas. Formula Fields allow you to enter a function for a data column. This function provides an output for that column based on other field type values in the same row.
| For example, if you create a field called “Average” with the formula: ([num1] + [num2] +[num3])/3, the data for that column will be the average value for three numeric fields in each row. |
There are four types of formula fields: Text, Numeric, Currency, and Date. For text-based formulas, you can manipulate values of each data type in various ways to create a customized or formatted output.
To create a formula field, click the drop-down for the table in which you want to add a formula field. Then as you click the Manage Fields menu item as shown, a screen will open listing all the fields currently used for that table.
Then click the Add Field button in the upper-right corner add a formula field. A pop-up window will open where you can enter the Field Name and Field Type. In this example, we’ll type in “Average” as the Field Name. Then in the drop-down for Field Type, we’ll select “Formula – Numeric” as shown.
A new text area called “Formula” will then appear in the pop-up where you can enter in the numeric formula you would like to use. (Example formulas are listed in the next section.)
The table's other fields will be listed below the formula box. You can use these fields as parameters in the formula. You can just double-click on one to add it to the formula.
After the formula is set up, click the Save Changes button to close the pop-up window. The new formula field should appear in the listing.
You can add more formula fields simply by repeating the steps above.
To see how this function works, switch to View Table in the menu (assuming you have a default table set up). Edit the table as necessary to get the relevant fields to show up. Make sure you have a form added in the Manage Forms page to enable editing for numeric values.
Then click Add in the upper-right corner of the View Table page to add a sample row of data. Enter in values for num1, num2, and num3 in the spaces provided, and then click Save.
The table will then show the average for three numeric values as shown below: