Filter for something included in an array

[Thibault_Milan]

Hi everyone,
I’m trying to figure out how to do something a little bit tricky here.

So, I have a user table with a column where a formula return all the accessible Zones delimited by a ,

Then in my data editor, I do a split by , and then a relation with the zones names to get them all

Now I have a list of zones (which have multiple entries) and I would like to just display for the current user the zones that are in his Accessible Zone relations list. But is included in doesn’t support array or relations. Any idea?

Why on earth am I doing this?

I’m doing all of this because we can’t combine AND and OR in a view filter. I would like to do Agency = user.agency AND active = true and [ restrictedTo is empty OR restrictedTo includes user.businessUnit]. So I did a formula using FILTER and a JOIN to create a comma-separated list of the zones a user can have access

[Jacktools_Net]

Create a joined list column based on your relation and use “is included” on this joined list.

[Thibault_Milan]

Already have the join list (which I create in Google Sheet) but seems not working

[Jacktools_Net]

try to created it in glide.

[Thibault_Milan]

Well I use this formula to create the join list.

=JOIN(",",FILTER(Zones!$C$2:$C,Zones!$A$2:$A=$F2,Zones!$D$2:$D=TRUE,Zones!$H$2:$H=""),IFNA(FILTER(Zones!$C$2:$C,Zones!$A$2:$A=$F2,Zones!$D$2:$D=TRUE,SEARCH($G2,Zones!$H$2:$H))))

I don’t think I can do the same in Glide. Ok I can do the join but then I’ll need two column where I have my two FILTER formulas which add more unnecessary columns

[Jacktools_Net]

Right, but you can use your “Accessible Zones” Relation to create a Joined List Column in Glide. And I think on this joined list column you can filter with “is included” or “includes”

[11183]

@Thibault_Milan try not to use filtering, but use relation column as a SOURCE for Inline list.

[Thibault_Milan]

Hey Thanks for you reply and welcome here you first post .

Well unfortunately I NEED to filter the data (that’s what I’m trying to achieve here, read the “Why on earth am I doing this” to get more context. Relation can’t be filtered when you are building a column or use them as source source for inline list.

[ThinhDinh]

In your first screenshot, is that Accessible Zones tied to an email of the user?

[Thibault_Milan]

Accessible Zones is based on the business unit of the employee.
Zones can be accessible to all (no restriction) or accessible to one or more business units.

So in the Zones I have a Restricted to field which is a comma-separated value :

Then in the data editor I’m splitting them because I wanted to be able to display the zone’s list filtered by the user’s business unit but … seems it can’t be done on an array (the result of the split).

So I went the other way around. Creating a Google Sheet formula that list in the user’s row all the zones he have access to, hoping to be able to filter by it when displaying the zone’s list, but same issue

[Darren_Murphy]

@Thibault_Milan

I’m doing all of this because we can’t combine AND and OR in a view filter. I would like to do Agency = user.agency AND active = true and [ restrictedTo is empty OR restrictedTo includes user.businessUnit] . So I did a formula using FILTER and a JOIN to create a comma-separated list of the zones a user can have access

There is no need to resort to GSheet formulas for this, it should be perfectly achievable by using if-then-else columns to construct two template columns, and then build a relation using the resultant template columns. Using this technique, you shouldn’t even need a filter on the Inline List.

[Thibault_Milan]

My understanding is a if-then-else column can’t use complex situations so I cannot build something like A and B and [ C or D ] But I would love to understand how I could achieve this.

I could probably to IF c THEN true ELSEIF d THEN TRUE ELSE FALSE → intermediary column
but I still can’t have a A AND B AND intermediary column

[Jeff_Hager]

For something like that, you would have to structure it like the following

IF not A Then False
ElseIF not B Then False
ElseIF C Then True
ElseIF D Then True
Else False

[Manu.n]

Hello @Thibault_Milan
If it’s boolean (0/1) values ​​you can use a math formula
example
A and B and [C or D]

A * B * (C + D)

[Thibault_Milan]

Though about that but it’s not boolean values (unfortunalty :p)

[Darren_Murphy]

@Thibault_Milan

My understanding is a if-then-else column can’t use complex situations so I cannot build something like A and B and [ C or D ] But I would love to understand how I could achieve this.

I could probably to IF c THEN true ELSEIF d THEN TRUE ELSE FALSE → intermediary column
but I still can’t have a A AND B AND intermediary column

If you can share a GSheet with some sample data, I’ll take a look at it for you.

[Nugget_Kelly]

Thank you for this comment!!!