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.

 

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)

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)

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)

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))

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)

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))

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)

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)

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:

Record

Examples:
=lookup(section1)