Hi – I have a fairly simple app to collect some basic health data (“Readings”) over time. Ultimately, I want this to be multi-user but, for now, I’m focused on just collecting my own data. I have a basic listing page, a dashboard page, and a scorecard page each driven by the same “Readings table” in Google Sheets. I would like to have a user defined filter that I can apply to each page. The filter would represent the “time window” (last day, last week, last month, all-time, etc) for just the current user. Essentially, a “dynamic data filter” across the entire app. Any ideas on how best to implement this?
Sure, if you want the filters easy applied across the whole app, you want the filter conditions set on the user profiles sheet. But to edit these, you may want an edit form that shows just these filters and not have that be a part of the edit user profile screen. For that reason, I’d create a new sheet called filters. Make each column be user specific, and you’ll only have one row on this sheet. Create an edit form to edit these filter settings. Then use these as filter conditions on the displayed data… likely by using them as filters in queries, and then your data viewed will be the results of the queries. You’ll realize you can’t access the filters sheet from other sheets… which brings us back to the first thing I said… it needs to be in the user profiles sheet. To get it there, add a lookup column for each column of your filters sheet. This way users can set the filters easily with an edit form, but you can easily apply the filters to the data anywhere because it’s found (via lookup) in the user profiles sheet.
Single Value → Whole Row is a better approach here.
Thanks for the critique! I used to only come to the forums to ask questions, or complain about something that didn’t seem to work right But recently I started testing my own understanding of Glide by trying to answer other people’s questions, and learn from the experts like yourself.
I checked out what you meant… wondering if I could save a bunch of columns this way. I found a few things you’re probably aware of.
- The data in a lookup of a sheet with a single row, isn’t usable in a filter because it’s an array (albeit of one). Which means my method exactly as stated, doesn’t even work.
- When applying a filter to a list, I see that the nested data within a whole row single value isn’t available to use. Which means your method still needs more columns.
The two options I then see are:
- SV of each filter setting from the filter sheet, same as I described but using SV rather than lookup. 2. What you said; use SV of whole row but you still need a lookup column for each filter setting, reading from the SV whole row.
Is there a “better” between these two options? #2 requires one more row, but is there a speed advantage to lookups from a whole row SV rather than single column SVs?
Yes, I’ve noticed your recent contributions, and that’s great
We can never have too many people helping out answering questions, and one thing you might find - at least I did - is that answering questions helps accelerate your own learning curve.
What I was getting at with my comment is the following:
- Imagine you have a single row Helper Table with a bunch of User Specific columns.
- You want to be able to set values in those columns from anywhere in the App
- The easy way to do that is to create a Single Value → First → Whole Row column in your User Profiles table that targets your Helper Table.
- That column acts like a single relation, and because it’s in the User Profile table, you can use it from anywhere in the App to set one or more values in any column of the Helper Table.
Now that I’ve typed that out, I realise that I’m describing a slightly different use case to your filters example.
So yes, if I’m using a Helper Table to drive custom filtering, what I will generally do is create one Single Value column in my data table for each of the filter criteria, with each fetching the associated value from the Helper Table. And then build additional logic in the data table to combine the filter conditions where necessary.
When it comes to custom filtering, there are many different approaches, and which is best often comes down to a matter or personal preference. The good news is that with the new In App Multi Filtering that’s about to go live, we won’t have to create complex custom filters as much as we have in the past
Ah, I see. Yes you were considering a different aspect… writing!
In the case I was describing, part of the point of this filters helpers table was to have a separate sheet, for which I can have an edit form that’s specific for the filters. A lot of apps I see, just have choice and switch components that write data directly… and how you describe would be perfect for writing directly from anywhere. I’m very update cost conscious, so I avoid using direct writes in lieu of edit forms as much as possible.
Since switching from google sheets based apps to glide tables apps, I have had a hard time writing to where I want from time to time… and I think learning this today will definitely help me out in some of those pinches! Like you said… try helping here and end up learning something
The new multi filters built in will be a great simplification in many apps. Though I know I’ll still have many cases for manually creating things like this, predominantly for user settings (including app-wide filters).
Thank you @MatthewS and @Darren_Murphy for you insights. I will definitely try your suggestions, but I was also wondering if you could point me to sample apps that might help me better visualize these concepts. I really appreciate your help!
Sure… I can draft something up…
Here you go
Hi @MatthewS – Thank you so much for the thoughtful videos and description of your solution. These were incredibly helpful and got me back on track
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.