Lookup

Description of formula:
Retrieves data from a list, data table or alias from app documents (if you want to get a single value in a cell, use LookupValue formula).

Search criteria
To find relevant data you should specify a query. The formula returns values from all records, which fulfill search criteria. If you do not specify any query, formula returns all records from the data source. Query consists of conditions in the form of:
field_name operator value
where:
field_name – a name of a column or an alias
operator – you can use one of the comparison operators: >, <, >=, <=, =, !=. Text values are compared in an alphabetical order.
value – you can enter a value directly, specify a cell address or calculate using a formula. The type of the value must be the same as field type.
You can join many conditions using and (&&) and or (||) operators. Query with and operator is satisfied only if both conditions are met. Query with or operator is satisfied when at least one condition is met. You can also use negation operator not (!) to reverse condition’s meaning. If you have more complex query, group conditions using brackets to define the order of evaluation.

Grouping data
You can also group retrieved data using special formulas called aggregates. Pass selected aggregate as a third parameter, instead of returned field identifier. You can use one of these formulas:
sum(field) – Sums values from the given field from all records in group
avg(field) – Calculates arithmetic mean of values from the given field from all records in group
count(field) – Counts records in group
min(field) – Gets the smallest value from the given field among all records in group
max(field) – Gets the largest value from the given field among all records in group
sumsqr(field) – Calculates the sum of squares of values from the given field from all records in group
All fields from data source used in section (except the field in aggregate formula) are the grouping criteria. Records, which have identical set of values are joined into one group. For each group the value of a specified aggregate is calculated.

Where can you use Lookup formula?
1) Table section
To fill section with data:
– enter Lookup formula in the „Filter” section’s property
– use field from data source in at least one of the section’s columns
Data from every returned record will be put in one row of the table section.
Syntax:
lookup(source, query)
Parameters:
source
(type: Identifier) Name of the list, data table or app, from which you want to retrieve data
query
(type: Logical) (optional parameter) Conditions which returned records should fulfilled
2) Spreadsheet section
To fill section with data:
– enter Lookup formula in the „Filter” section’s property
– use field from data source in at least one of the section’s cells
Section cells will be filled with data from a single record, which satisfies a query (if there are many records which meet conditions, the last one will be returned).
Syntax:
lookup(source, query)
Parameters:
source
(type: Identifier) Name of the list, data table or app, from which you want to retrieve data
query
(type: Logical) (optional parameter) Conditions which returned records should fulfilled
3) diagrams
To put data on a diagram:
– add new series
– enter Lookup formula in the “Data formula” series property
Syntax:
lookup(source, query, values_X, values_Y)
Parameters:
source
(type: Identifier) Name of the list, data table or app, from which you want to retrieve data
query
(type: Logical) (optional parameter) Conditions which returned records should fulfilled
values_X
(type: any) Values on the X axis - a name of a column from list or data table or an alias from app
values_Y
(type: Number) Values on the Y axis - a name of a column from list or data table, an alias from app or aggregate
4) Dropdown or Autocomplete cells
To create a list of values, which user will be able to select in a cell:
– set cell type to “Dropdown” or “Autocomplete”
– enter Lookup formula in the “Items” cell’s property
Syntax:
lookup(source, query, field)
Parameters:
source
(type: Identifier) Name of the list, data table or app, from which you want to retrieve data
query
(type: Logical) (optional parameter) Conditions which returned records should fulfilled
field
(type: any) Values to display in a dropdown list - a name of a column from list or data table or an alias from app
Field used for autocomplete cell must be of type Text. In dropdown you can use a field of any type, but value selected from list by user is always treated as a text.
If you want values in the list to not repeat, use Unique formula:
lookup(source, query, unique(field))
5) master-detail
Master-detail is a combination of two sections – "master" Table section, which contains a list of elements and "detail" Spreadsheet section, showing detailed information about currently selected row in "master" section. When user selects another row, values in "detail" section are automatically updated to display data related to the chosen element.
To create master-detail:
– add a Table section and fill it with data using Lookup formula (like in point 1)
– add a Spreadsheet section and enter Lookup to “master” section in the “Filter” property
– in at least one of the section’s cells use field from data source given in the Lookup formula in „master” section
Syntax:
lookup(master_section)
Parameters:
master_section
(type: Identifier) The name of the “master” section, which contains list of elements
In “detail” section you can use only those fields, which have been used in “master” section. Even if you do not want to show each field in “master” section, put all needed data in the table and then hide relevant columns.
Returned type:
array of values of any type
Examples:
=lookup(products, price>100, product_code)
=lookup(sysuser, isactive and age > 18, email)
=lookup(sales, region, avg(price))
=lookup(sales, region = 'western' or region = 'northern', max(price))
=lookup(products)
=lookup(products, category = section1.A1)
Have more questions? Submit a request

0 Comments

Article is closed for comments.
Powered by Zendesk