New to expressions and formulas? Try Expressions: The Essentials.
New to column constraints? Try Column Constraints.
You can also learn more about other types of expressions.
Every input in a form is checked for validity based on its type. For example, a column of type Number won't accept
Hello as an input. A Valid_If column constraint is a type of expression used in situations where the validity of the input requires richer data-dependent logic.
A Valid_If constraint is a condition expression that determines if the user input for the associated column is valid. For example, the Quote Calculator sample uses a Valid_If condition to ensure that the Cost Per Hour must be less than $20.
Here are examples of commonly-used Valid_If constraints:
- Does the value of the column satisfy a simple condition? For example, comparing the value with a constant (e.g.,
[_THIS] > 5), or comparing the value with another column (e.g.,
[_THIS] > [ColumnA]).
- Is the value of the column in a list? This produces a dropdown menu as described in the article Dropdown from Valid_If.
Invalid inputs can prevent saving data or progressing to the next page in a form. For data changes made through forms or Quick Edit columns in detail views, Valid_If constraints are only enforced for visible inputs. This is because:
- There are many ways columns can be hidden or omitted from the view (e.g. excluded from a slice, excluded from column order, or on a different page in a branching form), so it's often unclear whether blocking the user was intended or expected.
- If a user is blocked by a hidden column failing validation, the user generally has no way to fix it and doesn't understand why they're stuck. If the app creator didn't intend them to be blocked there, the user may not understand why either, leading to lost productivity.
Valid_If conditions can still reference values of hidden columns, so if you do want to block the form due to hidden values, just attach the expression to a visible column.
For updates made through actions, Valid_If conditions are enforced in two cases:
- If the column being updated becomes invalid, the update is blocked.
- If the update causes a different column to become invalid, the update is blocked.
This means an action won't be blocked due to preexisting invalid values that are unrelated to the action itself.
Valid_If and Dependent Dropdowns
Dependent dropdown menus are a common design pattern in apps that capture input. For example, consider an app like the Lead Tracker sample, that asks for a Lead Region (America, Asia, Europe), then for a Country within that region. This actually requires relatively complex logic, but AppSheet tries to make it simple. Dependent dropdown menus are driven by a separate lookup table.
In the sample, there is a separate Regions lookup table with two columns, Region and Country, which serves to describe the allowed combinations of regions and countries. Here is the table data used in the sample.
The Lead Region column has a regular Valid_If constraint:
Regions[Region]. Therefore, when a new entry is being added, the input for this column shows three choices: America, Asia, and Europe.
Likewise, the Country column specifies a similar Valid_If constraint:
Regions[Country]. However, because it follows the Lead Region column, and because both specify columns from the same lookup table, Regions, AppSheet recognizes the intent and implements a dependent dropdown menu.
Internally, AppSheet creates an expression to capture the allowed set of values for the Country column. The expression must say (in English!):
- Look at the Regions table.
- Filter the rows to make sure the Region column of the table matches the value in the Lead Region column of the row being edited in the form.
- Extract the Country column from those filtered rows.
- Eliminate any duplicates--these are the allowed countries!
- Recompute this list each time Lead Region is changed.
Strictly for the expression aficionado, the full underlying AppSheet expression would be:
IN( [_THIS], SELECT(Regions[Country], [_THISROW].[Lead Region] = [Region]))
While most app creators will never need to express something this complicated, you could in fact provide this expression as a Valid_If constraint. It's useful to know for advanced use cases. For example, instead of using an equality condition, an app creator could use inequality or richer expressions to build very expressive dynamic dropdown menus.