I’ve invited you to a baserow.io workspace showing a workaround for dealing with nested lookups for now until Lookup of Lookup fields appear as JSON and cannot be used in many functions (#1027) · Issues · Bram Wiepjes / baserow · GitLab is fixed. Thanks for surfacing this bug and sorry you encountered it!
The workaround is as follows:
If you ever want to write formulas doing anything more complicated than simply referencing a lookup of a lookup (or deeper) then you should (Example Workspace I invited you to):
- At each level create a formula field which aggregates the lookup field using a function like the
joinaggregate function. - Then at the next level lookup the aggregated lookup field, and not the lookup field directly.
- Finally you will have to deal with processing an aggregated value in your formula instead of a normal lookup.
Secondly, to go back to your original requirement of filtering on a lookup field. Is your requirement that you only want to see Nugget rows which have a ‘Care Teams’ Focused Group? If so an alternative solution that doesn’t involve creating all these intermediary aggregated lookup fields is (see Second Example I invited you):
- Instead of using the
filterfunction, instead just aggregate the lookup JSON like so:
join(totext(field('Focused Group (Lookup)')), ',') - Now your formula field is no longer an array type (which can’t be filtered) but is instead a text type which can be filtered. It is still however a JSON blob of data, but that’s OK (still a bug!) because we can still filter on it to only select rows with ‘Care Teams’
- Now you can apply a filter to the column saying “contains ‘Care Teams’”