Lookup Fields/Formulas should be filterable/colorable/aggregatable in the grid view

Filters to do

Formula Text

• is / is not
• contains / doesn't contain
• contains word / doesn't contain word
• is empty / is not empty

Formula Number

• is / is not
• contains / doesn't contain
• higher than / higher than or equal
• lower than / lower than or equal
• is even and whole
• is empty / is not empty

Formula Rating

• is / is not
• contains / doesn't contain
• higher than / higher than or equal
• lower than / lower than or equal
• is even and whole
• is empty / is not empty

Formula File

• filename contains
• has file type
• files lower than
• is empty / is not empty

Formula Duration

• is / is not
• higher than
• higher than or equal
• lower than
• lower than or equal
• is empty / is not empty

Formula Single Select

• contains / doesn't contain
• contains word / doesn't contain word
• is / is not
• is any of / is none off
• is empty / is not empty

Formula Multiple Select

• contains / doesn't contain
• contains word / doesn't contain word
• has / doesn't have
• is empty / is not empty

Formula Boolean

• is
• is empty / is not empty

Formula Date

• is / is not
• is before
• is on or before
• is after
• is on or after
• contains
• doesn't contain
• is empty
• is no empty

Formula URL

• is / is not
• contains / doesn't contain
• contains word / doesn't contain word
• is empty / is not empty

Formula Link Row

• has / doesn't have
• has value equal / doesn't have value equal
• has value contains / doesn't have value contains
• has have value contains word / doesn't have value contains word
• has empty / doesn't have empty value
• has value length is lower than.

Lookup Text

• has empty value / doesn't have empty value
• has value equal / doesn't have value equal
• has value contains / doesn't have value contains
• has value contains word / doesn't have value contains word
• has value length is lower

Lookup Number

• has empty value / doesn't have empty value
• has value equal / doesn't have value equal
• has value contains / doesn't have value contains
• has higher than / doesn't have higher than
• has higher than or equal / doesn't have higher than or equal
• has lower than / doesn't have lower than
• has lower than or equal / doesn't have lower than or equal

Lookup Single select

• has empty value / doesn't have empty value
• has value contains / doesn't have value contains (in progress by @bramw)
• has value contains word / doesn't have value contains word (in progress by @bramw)
• has / doesn't have
• has any off / has none off

Lookup Multiple select

• has empty value / doesn't have empty value
• has value contains / doesn't have value contains
• has value contains word / doesn't have value contains word
• has / doesn't have
• has any off / has none off

Lookup Boolean

• has empty value / doesn't have empty value
• has / doesn't have

Lookup Date

• has / doesn't have
• has before / doesn't have before
• has on or before / doesn't have on or before
• has after / doesn't have after
• has after or before / doesn't have after or before
• has is within
• has day of month
• has value contains / doesn't have value contains
• has empty value / doesn't have empty value

Lookup Duration

• has empty value / doesn't have empty value
• has higher than / doesn't have higher than
• has higher than or equal / doesn't have higher than or equal
• has lower than / doesn't have lower than
• has lower than or equal / doesn't have lower than or equal

Lookup URL

• has empty value / doesn't have empty value
• has value equal / doesn't have value equal
• has value contains / doesn't have value contains
• has value contains word / doesn't have value contains word
• has value length is lower

Lookup Button

Problem Statement

Currently lookup fields and formulas with the array type are not filterable, colourable or have any aggregates applied over them.

Benefits and risks

Benefits

• All other field types let you filter and it is an important way of working with Baserow

Risks

• Filters applied on lookups need to work over JSONB arrays which might be complex (or easy) not sure.

Proposed solution

• Unknown

UI/UX Design Required

• None (No UI/UX work required for this feature)

Priority/Severity

• Medium (This will bring a good increase in performance/productivity/usability)

Specifications:

Every lookup field is a BaserowFormulaArrayType where the subtype is the type of the looked-up field. For example, if we're looking-up a text field in the related table, the resulting formula will be of type array with formula_array_type=text

The idea here is to make lookups filterable by enabling the available filters for the formula-array-type, and to includeall the rows with at least one linked row matching the filter in the results.

For example, let's say we have tableA with a text field Name and 2 rows with a and b as values for that field.

In tableB, we have a link field to tableA and 3 rows:

• row1 with a link to a
• row2 with a link to b
• row3 with a link to a and b

In tableB, we should:

• be able to select all the filters available for the formula_array_type, In this case, all the filters available for the text field type
• filter rows in tableB if the field in tableA match the filter value for at least one of the linked rows. In this example if, if filter_type="equal" and value="a", the results should include row1 and row3

Acceptance criteria:

• We should use the existing filter types (not adding new ones) and implement the right common logic to handle arrays of the various types properly.
• Every field type that can be looked up should be filterable, although this can be split into multiple MRs if it makes sense. Filter types not implementing the NotViewFilterTypeMixin should return a row if any of the looked-up values match the filter value.
• each new type/subtype added should be tested
• nice to have: at least one test should fail if a new subtype is added but it's not covered by a test (see test_ensure_all_multi_step_filter_type_and_operators_are_tested)