I have a classic setup of a table of Notes and another table of Categories. Notes can optionally belong to one or more Categories.
I’m currently using the In-app Filter feature to add filtering to my list view of Notes, and it works fine. But for the UX of this app, I’d love to have category filtering be prominent and available with a single tap.
I set this up with a Choice Component (thanks to this video), but I can’t figure out how to make the filtering work as I’d expect. It works fine if I only allow one selection, but if I allow multiples, they are exclusionary rather than inclusionary. I.e. if I choose “Home” and “Media”, I only see notes that belong to both of this categories, where I’d like to see the notes that belong to either.
What would be ideal would be a “Chips” display option for In-app Filtering (which works the way expect), but I have a feeling there’s a way to manually build that functionality that I just haven’t figured out yet.
I know the choice component gives you a comma delimited list, but is that the same in notes when they have multiple categories?
If so, I personally prefer using a Split Text column in both tables to create an array. Then create a relation linking both split text columns together. Then you can source your collection from the relation.
Is it working correctly that if you select both Home and Office, you only want to see items that contain both? I assumed you wanted more of an OR condition when any item can contain either Home or Office or Both.
A relation isn’t overly complicated once you understand it. Think of it like a Parent and Child relationship. Let’s assume we have a Parents table that lists all of the parents…and we have a Children table that lists all of the children. You could have an array column in that Parents table that lists all of the children names or IDs that belong to each parent. Alternatively you could instead have an array in the Children table that lists all of the Parent names or IDs that belong to each child.
If you create a relation in the Parents table, you are taking a value in that row and looking for a matching value in any row of a different table. With the Parent/Child example, you could take the child array in the parent table and try to find matches to the child in the children table, or if you set it up the opposite way, you could take the parent and look for matches in the parent array in the child table.
Basically, relations just look for matching values in both tables. The result in the relation column is a list of all rows where a match was found when comparing those two columns in each table. It’s important to realize that a relation returns rows and not a specific column value from each of those matching rows. The values you see in a relation column are irrelevant. If you see something, that means it found matching rows in the other table. If it’s empty, then it did not find any matches.
Relations are like mini subsets of data from a related table. Treat them as if they are temporary tables with a subset of rows that contain all columns from that related table.
And you’re right, the “OR” matching you describe is what the In-app Filter feature does, and that would be preferable. I just can’t figure out how to replicate it.
I did have a Relation column set up in Notes, relating to Categories, from the start of this project. I’m just not sure how I can tweak it or add a new one to enable the functionality I’m after.
I really just wish the In-app Filter feature had a Chips display style.
The relation should be located in whichever table is the source of that screen. In other words, the table where the choice component is writing the selections. That’s because you want to take those choices, use a split text column to convert it to an array, and use the relation to find the matching notes that have the same categories as the categories in the split text array. The screen table is your parent table. The notes table would be your child table. The collection should be sourced from the relation instead of a table.
If you can show screenshots showing how you have it set up, I can point out where it’s going wrong.
Again, very helpful, thank you! The only problem now is that if I don’t choose any Categories, I don’t see any Notes. I somehow need no selection to translate to no filter, i.e. all notes.
I worked around this for now with visibility filtering of a filtered and unfiltered collection (if UserCategoryChoice is empty, show the unfiltered collection), but then I have to duplicate all my actions, etc.
Ok, that changes things. In some cases I would suggest switching to a query, but it seems limited in the types of comparisons you can do. A relation is nice because it does an OR comparison when comparing arrays, so only one of the array values needs to match. However, a Query seems to only allow an AND comparison, so all values need to match (or be “contained” in the array). So that rules out using a Query.
Maybe it’s better to go with an approach more along the lines of what Robert may have suggested (haven’t watched the video). What you could do is switch focus to the Notes table and add a Single Value column to bring over the comma delimited list of choices into the Notes table. There you could split it into an array. There are a few handy array type columns, such as the Array Contains Element column, but it only allows you to search for one elements instead of multiple. So that kind of rules out working with array entirely since Glide doesn’t provide adequate tools to do comparisons like this.
For absolute pure simplicity, I think I would incorporate some javascript code. It’s counterintuitive for a no-code environment, but it will prevent a mess of multiple columns and overly complex logic for something so simple. For this, you don’t need a Relation or Split Text column.
First create a Single Value column in the Notes table that brings in the comma delimited list of choices and populates them on every row.
Next create a javascript column with the following code. set the P1 parameter to be your note categories comma delimited list. Set the P2 parameter to be your single value column of comma delimited choices.
// Trim and split both lists into arrays
const list1 = p1.toString().split(",");
const list2 = p2.toString().split(",");
// If second parameter is empty, return true
if (list2.length === 0) {return true;}
// Check if at least one match exists
return list2.some(val => list1.includes(val));
Finally, set the collection source to the Notes table and filter is where the javascript column is true. The javascript will return true if no choice selections were made or if there is at least one matching category.
This sounds great, but I’m stuck on the Single Value column. My Categories are in their own table so the user can edit/delete/create them. When I try to reference this table in the Single Value column in Notes, I get an error that the data type is not supported.
If I understand your solution, ideally this column would contain a comma-separated list of only the Categories that are in use, so it would be great to pull them from the Notes table somehow. Might there be a way to concatenate my Category column into a de-duplicated list?
The Single Value column should be pulling the comma delimited list from the column in the table where the choice component is writing you choice selections. If I’m understanding correctly, the Categories table shouldn’t be a part of this except for being the source of your choice component choices.
You are using a big table which has some limitations when using computed columns. If you only have a handful of categories on that table, then it’s probably overkill and should be using a regular glide table instead. Big tables are for storing large amounts of data.
The single value column should only contain the choices selected from the chips.
That gets tricky since a note can have multiple categories, and on top of that the Notes table is also a big table, so you would probably run into similar issues with using computed columns. How many note rows would you have? If it’s not a lot, maybe a regular Glide table would be better. To answer your question though, you might be better off creating a relation in the categories table that looks for matches in the notes table and then filter you choice component to only show categories where the relation is not empty.
Thanks Jeff, you’re doing an amazing job of guiding me through this.
I hadn’t even realized that I was using Big Tables because I initially allowed the AI agent to set this app up for me. I probably would not do that again!
But the category filter choice is written to the Users table, which is not a Big Table, so I was able to follow your instructions and create the JavaScript column, and it appears to work!
BUT… I’m unable to filter by it. It doesn’t show up in the list of columns that I can choose from in the filter section.
I even tried creating an If → Then → Else column to duplicate the true/false results, but that column also does not show up in my options for filtering.
I think that’s another side effect of the fact that Notes is a big table. Normally that would work with a regular Glide table. I have never used big tables, but I know others have worked out some workarounds. However, I’m not sure if there is a workaround in this case other than switching to a regular glide table.
I think that’s the problem with relying on the AI Agent to build an app. I think the APIs that are used to connect between the agent and the builder only allow you to do certain things with big tables, such as creating tables from scratch or adding columns to the table. Those connections between the agent and regular glide tables is not possible. So that’s probably why glide pushes you into using big tables when using the AI Agent. It can only work properly with big tables.
(Now that I’ve manually converted my entire Notes table from Big to Glide)
This forced/gave me the opportunity to refactor a bunch of cruft that had built up in my Notes table, so I’m not totally mad about that.
I’m somewhat comfortable with JavaScript and extremely cavalier with ChatGPT so now I’m collapsing a few different multi-column workflows into JS columns.
