Filtering by columns with multiple values

Our apps main function is to share company announcements with our team members. But an announcement should only be visible if at least one of the signed-in users audiences matches the audience for that announcement.

At the moment I have an array column on both the audience and announcement sheets but i see array columns can only be filtered by is empty or is not empty. What type of column should I use to match the two?

Would something like this work? Announcements should only be visible if:

Announcement audience is included in signed-in user’s Audiences.

With Audiences being a template column that joins user’s Audiences, let’s say:


With A1 being Audience 1 and so on.

1 Like

Hey! thanks for the quick response!

Gave this a try but it doesn’t seem to work. I suspect because the template column is treating all the values in the cell as a string that needs to match exactly with the string the announcements audience cells.

I tried to add images to help but it will only let me embed one :frowning_face:

You can upload the images to an Imgur album then send us the link of all images.

1 Like

No problem. I’ve attached below and removed all the extra columns I was tinkering with so you can see how it works. Since I’m a total n00b I’ve added my reasoning to the data design in italics in case they could be improved.

For now the only audience type I’m working with is staff badges. This sheet shows all the badges a staff member holds in an array column. You can see the highlighted user, Rupecht, has a badge in complaints, FI evolve and Account Management.
The number of badges a staff member can pick up can be infinite. Also only junior managers can add a badge to a staff members list. So I’ve made this a separate sheet for legibility and planned to allow only junior managers to edit the sheet as row owners but I suspect this might end up causing visibility problems. Imgur

The Announcements sheet holds all the messages that will be pulled from Slack posts. Again theres an array column that shows all the intended audiences for that announcement. Rupecht, should only be able to see the first and third messages on the list when he’s signed in since only these message relate to his badges.

The App User Info sheet holds the user profile data for each individual team member. I suspect some columns need to be added here to match user’s badges to the audience announcement but I don’t know which type. I’ve tried a few different things and the documentation isn’t clear which column types work together or can use the ‘includes’ filter.
I originally tried to use the People info sheet as the user profile but the information there is meant to be used as a internal staff directory. But adding row owners here ended up hiding all the directory entries except the signed-in user. If theres a way to do user profile from People info it might simply the data structure..


I think you need five columns:
In Announcements

  1. A join column to join the badges of the Audience relation
  2. A Split column to separate the badges of the join column

In Staff Badges
3. A join column to join the badges of the user
4. A Split column to separate the badges of the join column
5. A relation from user’s split to the split column in announcements.

1 Like

YES! Getting warmer. Although you can only use join with a multiple relation columns so I had to add in a few extra steps.

I also did the badge-announcement relation in the App User Info sheet. Making that happen isn’t very intuitive but it at least correctly identifies the messages available to the user [IMAGE REMOVED BY ADMIN]

The last step is figuring out how to make the data filter into the user inbox. Most the useful columns above arent showing as a filter that can be used Picture on Imgur


Filter messages by Row ID is included in the Joined List of Message IDs!

Thanks so much for your helo @ThinhDinh and @profechef!!


Happy to hear that you got it ! :tada:

:warning: I personally wouldn’t share the emails address of my users here. Friendly reminder, this forum is public and people may access whatever you post in this topic without having to login.

1 Like