Unable to filter on lookup fields?

[michael]

Is it expected behavior that a Lookup field cannot be used in a filter?

[olgatrykush]

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.

[michael]

Thanks @olgatrykush!

Here’s what I tried:

1. I had a “Lookup” field called Focused Group. This is the field I want to filter on.
2. I renamed this field to be called Focused Group (Lookup) (just for clarity)
3. I created a new “Formula” field called Focused Group (Formula)
4. The formula for the new field is just —> field('Focused Group (Lookup)')
5. I tried to filter on field('Focused Group (Lookup)') but it was still greyed out

Did I misunderstand your instructions?

Formula Field

Filter

[olgatrykush]

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.

[michael]

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.

[olgatrykush]

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…

[michael]

@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.

[michael]

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!)

[nigel]

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 ?

[nigel]

Ah, I believe I can now replicate. Is your Focused Group a lookup, of another lookup field? So a lookup which is 2 deep?

[michael]

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)

[nigel]

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):

1. At each level create a formula field which aggregates the lookup field using a function like the join aggregate function.
2. Then at the next level lookup the aggregated lookup field, and not the lookup field directly.
3. 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):

1. Instead of using the filter function, instead just aggregate the lookup JSON like so:
   join(totext(field('Focused Group (Lookup)')), ',')
2. 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’
3. Now you can apply a filter to the column saying “contains ‘Care Teams’”

[michael]

@nigel

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’

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 !