App formulas and initial values

Sometimes, you want calculations to run in the app while the user is interacting with the data in a form. To help you achieve that goal, you can specify an App Formula for specific columns. An App Formula is an expression that AppSheet evaluates whenever a user changes a value in a form (while creating a new record or editing an existing record).

Each time such a change occurs in the form, the App Formula of every affected column in the record is evaluated and the column is assigned the result of the formula evaluation. 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. 

In AppSheet's Editor, you can add App Formulas to any column when you go to Data>Column Structure.



Click on the edit icon (seen above) and navigate to the App Formula field to enter the formula you need for your app. The formula itself is any valid AppSheet expression that matches the type of the column. You can learn more about the different kinds of expressions supported by AppSheet.

App Formulas vs Initial Values

You can also specify an Initial Value for every column. This is a constant or an app formula. The difference is that this expression is used to assign initial or default values for a column in a new entry. If the expression is not a constant, it may be a formula that depends on other columns in the new entry. Its value will be recomputed as the new entry is filled in until the end user explicitly assigns/overrides the value of the column. At that point, a value has been assigned and the "initial value" definition is no longer relevant for this entry.

In other words, an Initial Value expression acts just like an App Formula, but it has two differences : (a) the column is still editable by the enduser, and (b) once edited, the column is no longer automatically updated with continued recomputation of the expression.

App Formulas used 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 vs 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.


Have more questions? Submit a request


Article is closed for comments.