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
andb
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 thetext
field type - filter rows in
tableB
if the field intableA
match the filter value for at least one of the linked rows. In this example if, iffilter_type="equal"
andvalue="a"
, the results should includerow1
androw3
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
)