New to expressions and formulas? Try Expressions: The Essentials.
You can also learn more about other types of expressions.
For more information on dereferences, check out this video:
You can use a Dereference expression to retrieve the value of a column in a referenced record. Do this by writing a Dereference expression in the form [Column Containing Reference].[Column in Referenced Table].
The Order Capture sample app includes a typical Dereference expression. The Order Details table uses the Dereference expression [Product].[Price] to retrieve the product's price from the Products table.
In this expression, [Product] Is the name of the Reference column in the Order Details table that refers to the Products table.
[Price] Is the name of the column in the Products table that contains the product's price.
You can use Dereference expressions when computing values. For example, in the Order Capture app, there is a formula that multiplies [Product].[Price] with another column called [Quantity].
Dereference expressions like these may be used throughout AppSheet. This includes:
- Initial Values
- App Formulas
- Virtual Columns
- Column Constraints (Valid_If, Show_If, Required_If)
Double Dereference Expressions
You cannot dereference a dereference.
For example, you might have a three level hierarchy consisting of a Customer record, a child Order record, and a grandchild Order Detail record. The Order Detail record contains a Reference to the Order record. The Order Detail record contains a Reference to the Order record.
From the Order Detail record, you can access the value of a column in the Order record using a Dereference expression as described above.
From the Order Detail record, you cannot directly access the value of a column in the Customer record by chaining dereference expressions. That is, you can't dereference from the Order Detail record to the Order record, and then dereference from the Order record to the Customer record in a single expression.
However, from the Order Detail record you can indirectly access the value of a column in the Customer record. Do this as follows:
- Create a new virtual column in the Order Detail record that will serve as a Reference to the Customer record. For example, you might call the virtual column CustomerRef.
- In the AppFormula of the new virtual column enter an expression that copies the value from the Reference column in the Order record. Consider the Order Capture sample app. In it, the AppFormula in the new Order Detail virtual column would be [Order Id].[Customer Name], where [Order Id] is the name of the Reference column in the Order Detail record that refers to the Order record,and [Customer Name] is the name of the Ref column in the Order record that refers to the Customer record. This expression copies the value of Customer Name Reference column to the new virtual column in the Order Detail record.
- Ensure that the type of the new virtual column is Ref and the Referenced Table is the Customer table.
In the Order Detail workflow template, you can now refer to column names in the Customer table be dereferencing the new virtual column. For example, if the new virtual column is called CustomerRef, you can retrieve the customer's email address using this expression. [CustomerRef].[Email]