Filter function
The Filter function finds records in a table that satisfy a formula. Use Filter to find a set of records that match one or more criteria and to discard those that don’t. The formula is evaluated for each record of the table. Records that result in true are included in the result. Besides the normal formula operators, you can use the in and exactin operators for substring matches.
Syntax :
Filter(Table*, Formula1 [, Formula2, … ] )
- Table – Required. Table to search.
- Formula(s) – Required. The formula by which each record of the table is evaluated. The function returns all records that result in true.
The example below , returns records where OnOrder is greater than zero:
Search function
The Search function finds records in a table that contain a string in one of their columns. The string may occur anywhere within the column; for example, searching for “rob” or “bert” would find a match in a column that contains “Robert”. Searching is case-insensitive. Unlike Filter and LookUp, the Search function uses a single string to match instead of a formula.
Syntax :
Search(Table*, SearchString, Column1 [, Column2, … ] )
- Table – Required. Table to search.
- SearchString – Required. The string to search for. If blank or an empty string, all records are returned.
- Column(s) – Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. However, the column names must be static and cannot be calculated with a formula. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.
The example below , returns records where the string “choc” appears in the Flavor name, independent of uppercase or lowercase letters :
LookUp function
The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria. The formula is evaluated for each record of the table. Records that result in true are included in the result. Besides the normal formula operators, you can use the in and exactin operators for substring matches.
Syntax :
LookUp(Table*, Formula [, ReductionFormula ] )
- Table – Required. Table to search. In the UI, the syntax is shown as source above the function box.
- Formula – Required. The formula by which each record of the table is evaluated. The function returns the first record that results in true. You can reference columns within the table. In the UI, the syntax is shown as condition above the function box.
- ReductionFormula – Optional. This formula is evaluated over the record that was found, and then reduces the record to a single value. You can reference columns within the table. If you don’t use this parameter, the function returns the full record from the table. In the UI, the syntax is shown as result above the function box.
The example below , searches for a record with Flavor equal to “Chocolate”, of which there is one. For the first record that’s found, returns the Quantity of that record:
