A dereference expression gets the value of a column from a specific row of another table. A dereference expression makes use of references between tables, using the value of a column of type Ref
in this table to find a specific row in another table and get a value from one of its columns.
The following sections describe dereference expressions in more detail:
New to expressions and formulas? See Expressions: The Essentials.
Get started
Watch a video
Watch this video to learn how to join data from multiple tables using dereference expressions and the LOOKUP()
function.
Quick Tip Friday - Joining Multiple Tables
Explore a sample app
The Order Deliveries sample app includes a typical dereference expression.
In the Orders
table, the Customer ID
column is a reference to the Customers
table. The Orders
table uses the dereference expression, [Customer ID].[Email]
, to retrieve the customer email from the Customers
table based on the customer ID.
This dereference expression does the following:
- In the
Customers
source table, find the row with a key column value that matches theCustomer ID
column of this row. - From that row in the source table, get the value of the
Email
column.
These steps are repeated to get the value of the Phone
and Address
columns.
Dereference a column value
A dereference expression is of the form:
[ref-column].[value-column]
Where ref-column
is the name of the column of type Ref
in the this table, and value-column
is the name of a column of the other table.
The configuration of a column of type Ref
identifies the table in which referenced rows are to be found:
The value of a Ref
column should be the key column value of a row in the Ref
column's source table.
Dereference a list
A column value of type List
or EnumList
with a base type of Ref
can be dereferenced to produce a new list of the values from dereferencing each individual reference, an operation called a list dereference.
A list dereference is performed by enclosing the Ref
list column name (such as, Related Orders
) and the column name of the desired column value (such as, Order Date
) each in square brackets ([
, ]
) and placing them adjacent to each other: [Related Orders][Order Date]
The result will be a list of Order Date
column values from the rows identified by the list in the Related Orders
column value.
See also Build list dereferences.
Chained dereference expressions
You can dereference a dereference.
For example, you might have a three-level hierarchy consisting of a Customer
row, a child Order
row, and a grandchild Order Detail
row. The Order Detail
row contains a reference to the Order
row. The Order
row contains a reference to the Customer
row.
From the Order Detail
row, you can directly access the value of a column in the Customer
row by chaining dereference expressions. That is, you can dereference from the Order Detail
row to the Order
row, and then dereference from the Order
row to the Customer
row in a single expression.
[Order Id].[Customer Name].[Email]