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.
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.
You can also create criterion, which will return records from data source, which belong to values listed in given array. Criterion form: field_name = array(values)
Example:
=LOOKUP(SYSUSERINGROUP, GROUP = ARRAY('Administrators', 'Designers') - this formula will return only values of users, who are in Administrators or Designers group
Remark:
You can list in array maximum 1000 elements.
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
– average(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 and 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 columns
In the case of the table - data from every returned record will be put in one row of the table section. In the case of the Spreadsheet - section cells will be filled with data from a single record, which satisfies a query (if there are many records which meet conditions, the first one will be returned).
Syntax:
lookup(source, query)
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
Returned type:
Record
Examples:
=lookup(products)
=lookup(products, category = section1.A1)
=lookup(products)
=lookup(products, category = section1.A1)
2) 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)
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
Returned type:
Series of values
Examples:
=lookup(sales, region, average(price))
=lookup(sales, region, average(price))
3) 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)
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
If you want values in the list to not repeat, use Unique formula:
lookup(source, query, unique(field))
Returned type:
Array of texts
Examples:
=lookup(products, price>100, product_code)
=lookup(sysuser, isactive and age > 18, email)
=lookup(sales, region = 'western' or region = 'northern', type)
=lookup(products, price>100, product_code)
=lookup(sysuser, isactive and age > 18, email)
=lookup(sales, region = 'western' or region = 'northern', type)
4) 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)
lookup(master_section)
Parameters:
master_section
(type: Identifier) The name of the “master” section, which contains list of elements
Returned type:
Record
Examples:
=lookup(section1)
=lookup(section1)
0 Comments