REFINE

The REFINE function takes a list of items and filters, excludes or sorts them based on rules.

Details

Rules are evaluated in groups, so resulting items after evaluating a group are used as input to evaluate the next group.

Syntax

REFINE(items, table, [rowsSelector, colsSelector, out])

Argument name	Description
items Required	Item list to refine.
table Required	Table with the refining rules.
rowsSelector Optional	Selector of rows. All rows by default.
colsSelector Optional	Selector of columns. All columns by default.
out Optional	Also includes the results in a list.

Table syntax

Column name	Description
answer Required, Multiple	Rule to evaluate. It can be a formula.
group Optional	Group to which the answer belongs. If not specified, all rules will belong to the default group.
mode Optional	retain Filter the current list retaining only the matching items. sretain (default) Filter the current list retaining only the matching items that appear in any other matching sretain rules of its group. order Do not filter items, only assigns a weight to the items. exclude Exclude the items.
weight Optional	Assigns points to items that will be used to order them. All items have a weight of 1 by default.
<other columns> Required	Any other column is treated as items holder.

Examples

Example 1

Say we have a list of these three items [red, blue,redblue]. They could be for example shirts: one red, another blue and, another red and blue.

rules1

REFINE(items, rules1)

• If likes_red and likes_blue are true, returns [red, blue, redblue]

• If likes_red is true, returns [red, redblue]

rules2

REFINE(items, rules2)

• If likes_red and likes_blue are true, returns only [redblue] because it is the only matching item that appears in all matching sretain rules of its group (the default group in this case).

• If likes_red is true, returns [red, redblue]

rules3

REFINE(items, rules3)

• If likes_red and likes_blue are true, returns [redblue, red, blue]. Note that redblue is returned first because it appears in both rules so its weight is higher.

• If likes_red is true, returns [red, redblue]

rules4

REFINE(items, rules4)

• If likes_red and hates_blue are true, returns only [red] because redblue will be excluded.

Example 2

List items = [p1, p2, p3, p4, p5, p6, p7, p8]

rules

REFINE(items, rules)

• If a1 and a3 are true, returns [p1, p2]

• If a1 and a4 are true, returns [p3, p4]

• If a1 and a2 are true, returns [p1, p2, p3, p4, p5, p6, p7, p8]

• If a1, a2, and a3 are true, returns [p1, p2, p7, p8]

Example 3

List items = [p1, p2, p3, p4, p5, p6, p7, p8]

rules

REFINE(items, rules)

• If a1, a3 and a5 are true, returns [p5, p6, p7, p8]