Define app formulas and initial values as described in the following sections:
Sometimes you might want column values to be computed automatically in the app while the user is interacting with the data in a form. You can make a column computed by configuring it with an app formula or an initial value expression.
Columns with app formulas are useful for values that should always be computed (they appear read-only to app users). When a form is opened (or when a row is modified through some other mechanism), all app formulas in the row are recomputed and their corresponding column values updated. As the user makes changes within the form, app formulas within the row that are affected by those changes will continue to update.
For example, if a record has a
Price column and a
Tax column, the
Tax column might have an app formula
[Price]*0.05 to compute a 5% tax automatically. If the user changes the value of
Price, the value of
Tax will automatically update because it depends on
Columns with initial values behave much like columns with app formulas, with two main differences:
- They are not automatically read-only, so users have the option to override the computed value.
- They are only computed when adding new rows, not when updating existing rows.
The app formula and initial value can be any valid AppSheet expression that matches the type of the column. For more information about the different kinds of expressions supported by AppSheet, see Expressions: The Essentials.
App formulas for virtual columns
App formulas are also used to define virtual columns. These columns do not actually exist in the spreadsheet but are only computed within the context of the app itself.
App formulas versus spreadsheet formulas
App formulas are distinct from spreadsheet formulas. The two mechanisms are complementary and both add value to your app. Your spreadsheet is a very powerful tool to run calculations. Those calculations run only when data syncs back to the spreadsheet.
App formulas are commonly used to calculate the value of a column when the user makes updates to the app. When the changes are synced back to the spreadsheet, the computed value is saved to the corresponding spreadsheet cell.
To view and edit an app formula and Initial value:
- Open the app in the editor.
- Go to Data and select the table you want to view or edit in the list.
If you are using the legacy editorGo to Data > Columns and expand the table you want to view or edit.
- Edit any of the column properties displayed.
- Click Edit to view the full set of properties for a specific column.
- Expand the Auto Compute section.
- Update the App formula and Initial value fields.
- Click Done.
Save the app.