Is it expected behavior that a Lookup field cannot be used in a filter?
Hello @michael! As you have discovered, filtering on a lookup field type does not work. A workaround is to create a new formula field based on the lookup, and then perform the filter on the formula field.
Thanks @olgatrykush!
Hereâs what I tried:
- I had a âLookupâ field called
Focused Group
. This is the field I want to filter on. - I renamed this field to be called
Focused Group (Lookup)
(just for clarity) - I created a new âFormulaâ field called
Focused Group (Formula)
- The formula for the new field is just â>
field('Focused Group (Lookup)')
- I tried to filter on
field('Focused Group (Lookup)')
but it was still greyed out
Did I misunderstand your instructions?
Formula Field
Filter
Hello @michael! Sorry, my bad, for some reason, I was sure that in Baserow filtering works on the formula field type but it turned out that not
What you can do is use the FILTER function. It âfiltersâ a range of data based on specified criteria. Let me show you an example of how it works:
Letâs say we want to filter and show only fields that contain the letter âaâ in the column âLookupâ, and by using this formula we can achieve the needed results:
filter(field('Lookup'), contains(field('Lookup'), 'a'))
Unfortunately, this solution is more complex but hopefully helps for now. I can also confirm that we plan to add the possibility to filter on the Lookup and Formula field types in the future.
Thanks @olgatrykush !
Must be something with my datatypes because it still isnât working for me. That âFocused Group (Lookup)â field Iâm trying to filter on is actually a âSingle Selectâ field but it is carried through a few tables as a âLookupâ.
I also tried it with a different / simpler function and I get the same issue.
Thinking I could outsmart that issue with arrays
, I converted it to text
but still couldnât filter on it.
Hey @michael, just to clarify, you tried to convert that field into text, and then use the formula âfilterâ and it didnât work for you, right? Because itâs the right way
I tried to recreate a similar scenario, and it worked for me. Maybe I missed out on some details in your caseâŚ
@olgatrykush I guess there is something different with your Lookup field and mine.
The formula for my âLookup to textâ field is just: totext(field('Focused Group (Lookup)'))
But the contents are very different. Yours just says Monday
, Tuesday
, etcâŚ
My totext
is showing up as JSON.
Actually⌠I guess it doesnât really matter because even if I get this to work, I still wonât be able to use the Filter function of the Grid on Lookup or Formula fields (though it sounds like that is coming in the future at some point!)
Hm, iâm also not able to replicate JSON being produced which is a bug Iâd love to track down and fix. Are you self hosting or using baserow.io @michael ?
Ah, I believe I can now replicate. Is your Focused Group a lookup, of another lookup field? So a lookup which is 2 deep?
Yeah⌠itâs actually multiple levels deep. So we set the âFocused Groupâ on the âPersonaâ table using a âSingle Selectâ, then I carry it down a few levels so I can use it on the âNuggetsâ table. Our schema looks something like this. (and yeah, using baserow.io)
Iâve invited you to a baserow.io group showing a workaround for dealing with nested lookups for now until Lookup of Lookup fields appear as JSON when used in formulas (#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 Group I invited you to):
- At each level create a formula field which aggregates the lookup field using a function like the
join
aggregate 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
filter
function, 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ââ
Blobs for the win! Iâm now able to filter my UX Nuggets based on the Focused Group, which is exactly what I needed to do. Thanks @nigel and @olgatrykush !