Thanks @nathanaelb!!
Re Step A: how do I make this happen if the categories “n” of my ideal filter do not go up one by one?
How is the data being added to your spreadsheet:
- User generated?
- Text input? Number input? Choice?
- What exactly is the behavior in the app?
Hi, I have worked out a solution for this for it to display all fitting events inside a “User Profile” tab.
Let me explain it in details, hope it helps for @StephM and I’m open for suggestions from @nathanaelb.
Firstly, I have some choices for the “min age” and “max age” that would require event organizers to add when they add events to the page. This is based on StephM’s requirements above.
Then I have some random data that I generated, which includes event, min age and max age (sorry about the names, I have it randomly generated).
I use Arrayformula to generate the corresponding months (in number) for each min age and max age. Basically if the choice is months, take the number, if the choice is years, multiplies it by 12.
Then in Glide, I use a simple Template column to have a display value for each event (concatenate min age and max age).
In my user profile page, I add an “Age choice” column that is setup as below. The value displayed is the months choice (with years or months), but the recorded value is only the number.
Then, I have an inline list that is set up as below.
And here is it in full flow.
I think this approach can simply the calculation @dharmel must do in the Sheets as welll, hope it helps.
1 Like
@nathanaelb all data is inputed manually (by myself, through the google sheet) at the moment; I may introduce user generated data at a later stage, but not now. Just trying to get a quick MVP out asap.
Not sure what you mean by app behaviour but here is a screenshot of my activities tab (filter at the top) and GS used to populate that tab.
Can you try my approach?
@ThinhDinh
By using a years-to-months table and a choice component when the organizer submits his event, I was wondering if you could populate your “Min age months” and “Max age months” column directly, thus avoiding the formula in GS. What do you think?
1 Like
Yeah you can definitely do that, that’s the only calculation I did in the sheets if I’m not wrong.
Good morning @StephM.
User Behavior A
On screen, you have a list of items only (your “Activities” screen). You would like the user to click on the funnel symbol in the upper right-hand corner (the in-app filter feature in the builder). The user sees a drop-down menu with values of one chosen property (a column, in your case your “ideal world filter”. The user selects one or multiple options in the list. When the user taps “Done”, the list is filters based on the options selected. Notice that the choice the user is making is related to the class/event directly: what “age category” (your ideal world filter) does the item belong to.
If this is the behavior you are looking for, see this post above (I’m sending you back to the same posts).
User Behavior B
You have a screen with a list of items (in the builder this happens to be an inline list) and above this list you have a choice menu (in the builder this is a choice component). Notice already that in order to filter the list onscreen, the user will be using this choice menu, not an in-app filter (this is what I meant when I asked you what the behavior in the app was). You would like the user to indicate his age in the choice menu (the parent will indicate the child’s age). Based on this selection, the list below the menu will be filtered and will show only those classes/events whose minimum age is below the age of the child and whose maximum age is above the age of the child. Notice that the choice the user is making is related to the age of the child (not the class/event).
If this is the behavior you are looking for, see this post by @ThinhDinh.
I like @ThinhDinh’s approach: the choice menu is prominent and can barely be missed (it does add a little bit of visual clutter compared to the in-app filtering method). Importantly, you can add other menus to filter along other properties (which you cannot do with in-app filtering).
1 Like
@StephM
My recommendation since you are doing things by hand in GS and want your MVP out quickly: continue doing things by hand, don’t try any complicated setups. Once you get tired of copy-pasting and you see that you are repeating the same steps manually over and over again, that is where you can automate transforming the data in GS and the GDE.
Hello again @nathanaelb!
User Behaviour A is what I am going for, so I am going through your original post above.
I’ve just set up the user input form to enable providers to add items (ie. their activity) directly through the app and I am now about to set up the GS to be able to collect user input. But before I go any further, I need to understand something:
Re Step A of your previous post: how do I make this happen if the categories “n” of my ideal filter do not go up “one by one”? ( 0 months, 3 months, 6 months, 9 months, 12 months, 18 months, 24 months, 2.5 years, 3 years, 3.5 years, 4 years, 5 years, 6 years, 7 years, 8 years, 9 years, 10 year, 11 years, 12 years).
Can this formula be simply reworked =arrayformula(sequence(1,F2-E2+1,E2,1)) or will we need to take another approach?
Hi @StephM,
Maybe the following will help you move forward.
Try the following formula in cell C2:
=
if(B3<2,arrayformula(sequence(1,B32-A32+1,round(A3),1)/2),
if(A3>12,12,
if(AND(A3>=2,B3<=12),sequence(1,B3-A3+1,A3,1),
if(and(A3>=2,B3>12),sequence(1,12-A3+1,A3,1),
if(and(A3<2,B3>=2,B3<=12),{arrayformula(sequence(1,3.5-A32+1,round(A3),1)/2),sequence(1,B3-2+1,2,1)},
if(and(A3<2,B3>12),{arrayformula(sequence(1,3.5-A32+1,round(A3),1)/2),sequence(1,11,2,1)})
)))))
(Where it says “32”, it should say “3*2”. There should be a multiplication sign between the 3 and the 2.)
It’s not optimized but it might be enough. To skip 7, 9, 10 and 11, do it in the lookup table (I called it matching table in my screenshot).
If you don’t understand what I mean with by “lookup table”, ignore this point for now. Set up your filter with the numbers. Then you’ll be saying “Instead of 0, I would like to see “0-6 months””. That’s your lookup table. 
Thanks @nathanaelb and @ThinhDinh! You guys are super helpful! I ended up taking bit of pieces of your posts, but decided to keep doing most things manually for now.
The issue I am having now with my filter is that the age ranges are not displayed in the right order. Any idea how to solve this?
@StephM
In your screenshot (ie. in the layout / properties / features screen), you need to adjust “Sort”. Adjacent to the column with your ranges, you might have to create a column you could call “Ranges rank” and rank your ranges from 1 to 14. Then sort along that property with ascending.
I’m curious: did you use that formula I indicated above?
1 Like
I did not try the formula yet, as I am just trying to set things up manually for now. But will definitely do once I get on with automation and let you know for sure.
What I did do: I added a tab in GS with age category rankings from 1 to 14 and added a relation in GDE “filter rank relation” to assign a ranking to each item of my “age filter category” array. (I also created a lookup table in GS as per your suggestion above, the result of which is the “age filter rank” array in GDE). (So basically did the same thing using 2 different approaches).
Problem is: I am not able to “sort” my filter by any of these properties, ie. these properties do not appear in the “sort” drop down menu… Any idea?!
Thank you!!!
