Need help with a filter problem

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.

2 Likes

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.

2 Likes

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.

image

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.

Does my “current project array” have to be in the project sheet? Because currently it is in the staff sheet and when I try to create a relation column in the staff sheet, it gives me this, cannot select an array.

OK, let me ask this. Do you have an actual array, or are you just calling it an array? If it’s actually an array, the data editor will only show one column with all the values in the separate columns combined. Also, are you working with google sheets or glide tables?

If you place all of your columns next to each other in a google sheet and sequentially number them, then they will combine and show in the data editor as a single array column. Your screenshot is showing gaps with other columns in between your project columns, so I’m suspecting that you do not actually have an array column.

If you are using a glide table, then it will not automatically generate that array column. You then have to create a template column that joins all of the project columns together with some sort of delimiter, like a comma , or what I prefer…a pipe |. Then you use a Split column to split that template column on the delimiter to create an array.

That’s why I asked to see a screenshot of your array column. I wanted to verify that you in fact do have an array column.

And to answer your question…no you do not want to add anything to your project sheet. What I’m describing should only happen in the Staff sheet.

2 Likes

I set up the numbered columns in the google sheet, then this was combined in the Glide sheet, so I guess I’ll have to create that template column now…:grimacing:
Here’s a screenshot of my current “array” in Glide and the according columns in Google.

Well, that seems correct. I feel like that should work. I’ll try to test it out myself later. So even if you scroll all the way down your list of columns when creating the relation, you still don’t see the array? Maybe it’s at the bottom of the list? You’ll still see the individual columns as options, but usually you should see the array intermixed in there.

It works! :partying_face::balloon:

That was great. Thank you!

1 Like