Need help with a filter problem

I’m stuck. I have a staff sheet and a project sheet. Each staff member can choose up to 6 projects that will be displayed on their public profile. Each time a user picks a project from a choice list, it will fill the columns “current project 1” to “current project 6”, creating an array. On the profile I only want to show the projects they are currently working on, not all of the available project options. I thought I had found a solution, but no.

I added a team member column to the projects sheet to which I manually added the different users (should be filled automatically). Then I used a “Team includes Screen > Name” filter. But having to fill this column in manually is going to create a lot of mistakes.
Then I thought I could do a lookup column, but I can’t look up an array. I would have to choose a specific “current projects” column to look it up. There’s probably an easy solution, but I can’t wrap my head around it.

In a perfect world, users would also be able to filter other members by projects (detail screen).

Trying to wrap my head around this, probably some screenshots would help.

If you’re using a choice component for this, you can either use a relation as the source value, or filter the choices accordingly so users can only choose ones they are working on.

Not sure that this is the best approach. Having numbered columns is often (but not always) a sign that you have a design flaw. In this case, I think I would just have a single User Specific boolean column in your Projects table, and use it like a “Favourites” column. You could hide the associated component once a rollup count exceeds 6. And it would be simple enough to create a relation between your Users and Projects tables for displaying a list on your users profiles.

1 Like

Yeah, maybe you’re right. I’ll try a boolean and see how far I can get with that approach.

I’m currently using choice components for each project, and I looked at the relation option, but it’s the same problem as the lookup option - it only lets me choose a specific column, not an array. Anyway, maybe Darren is right and my setup is not ideal.

One part that you might get stuck with is creating the relation between your Staff table and your Projects table. Assuming that…

  • You go with the User Specific boolean column in your Projects table (lets call that usc/my-project)

Then in your Staff table:

  • create a template column that contains just the word true, with no replacements (tp/true)
  • then create a multi-relation that links Staff → tp/true to Projects → usc/my-project

You can then use that column in an inline list, and each user should only see their own projects.

I want all users to be able to see the projects on the other users’ profiles (it’s an employee directory for a remote team). If person A works on projects ABC, I want person B to know that, so they can contact this person in case they need information on these projects.

Ah, I see.

Okay, in this case the User Specific column approach will not work.

Two possible alternatives immediately come to mind:

  • Option 1:
    • This is the more traditional approach, and quite simple to implement
    • With this option, you introduce a 3rd table: ProjectUsers
    • This table includes columns for both ProjectID and UserID
    • Although it’s very easy to setup, it is expensive in terms of row count, as you need one row per Project/User combination
  • Option 2:
    • The 2nd option involves the use of the trebuchet method
    • With this option, you create another column in your Projects table (AssignedUsers)
    • This column would contain a joined list of all users assigned to each Project
    • This would be a much more elegant solution, but if you haven’t used trebuchet before, then there is a bit of a learning curve.

Trebuchet sounds interesting, but I’m a newbie and have never used it. However, I’m not assigning projects to users. They are supposed to indicate what projects they are working on when setting up their profiles (selecting from a project list).

That’s okay. The solution is still the same.


How? I had previously set up the team column, which contains the names of the people working on the project, but I added them manually. Will the trebuchet method allow me to collect the names automatically? I can’t find any info in the documentation.

Not automatically, but you could set it up so that users could add/remove themselves from projects - which is what you want, yes?

You won’t find anything in the Glide docs about trebuchet, as it’s an advanced technique developed by a couple of our community heroes - @Robert_Petitto & @Lucas_Pires. It takes a bit to get your head around it, but if you’re willing to learn then it opens a lot of doors.

Watch this video for a good introduction:

And there are one or two other tutorials available, you’ll find those by searching.


Tank you, will check it out!

Sorry to be bothering again, but are you sure this is the only way to do it? I mean all I want to achieve is that person A chooses several items from a set list, and person B should be able to see person A’s selection. Shouldn’t be this complicated. :thinking:

I like pictures, so if you have screenshots, I think that would help a lot to visualize what your goal is.

Let’s back up to you original problem. I think I’m having trouble understanding. So what is your end goal. Are you trying to filter the 6 choice components with a subset of projects, or should the choice components show all projects? Depending on you answer to that question, are you then only trying to show a subset of projects on the the user’s profile page? I guess what I’m getting at…I understand the part about having an array of projects in the staff sheet, but why are you trying to have a list of users in the project sheet, unless you are trying to show the list if users involved in a project when viewing the project details.

I’m just trying to get a better idea of what you want. I would think that if you have an array in your staff sheet, then you can use that to build your relation to your project sheet and that would be it…you could then add an inline list to the profile screen and set the source as the relation. No need to filter anything.

I have a list of 9 projects stored on the sheet “Projects”. Users are supposed to indicate in their profile which projects they are involved in. I am currently using choice components to have them choose their projects, but a checklist would look nicer. Anyway, I am displaying the projects as an inline list on each member’s profile in the employee directory. But without some kind of filter the list will show all 9 projects. I need a solution that will only display the projects the person is actually working on. And I want everybody else to see this person’s project selection when they visit the profile. I am currently using an additional row in the project sheet, which I named “Team”. I added the team members manually. My filter says “Team includes Name”. It works and shows only the selected projects, but I don’t want to have to add the names manually. I’ll upload pictures in a bit.

I cannot really set the source as a relation when the source is an array as it will ask me to choose between “current project 1”, 2 or 3 and so on.

I build relations that link array columns to a column in another sheet all the time, so I’m not sure I understand why you say that you can’t. Can you show a screenshot of your array column as well as the settings for a relation column that show that the array is not an option?

Ok, will do, but it will take a couple of hours. For now here’s a screenshot of my current filter and the filtered inline project list. Would be nice if a relation could solve this. That trebuchet stuff is a bit too advanced for me. :crazy_face:

I think this should be really easy. All you need is a relation in your Staff sheet that uses the array and links to the project sheet. Then source the inline list from that relation and you are done. No filters needed.


Once you get that part working, then you can use maybe a simplified version of this method below to allow the user to select projects more easily. Something that would build a joined list of selected projects and then a button to save that selection to the user’s record in the staff sheet. But lets get the relation part working first.