I guess what is confusing me - is that I can use a âSplit Textâ column, to separate the Values into separate items - which works with a relation.
But that same Split Text column - isnât available to choose from when setting up a Query. Would be ok if I only needed to filter on one value - but I need to filter on Location OR Category.
Thought maybe I can set up two relations on the same table - rel>location and rel>category - then combine them somehow - but donât think that is possible.
Any other suggestions welcome -
Thanks,
Andrew
I worked it out by adding extra columns in my view table. Was hoping not to do that as I will have several venues - so not perfect, but it works!
Makes for an unwieldy Query column too - but where there is a will, thereâs a way.
You are still using âincludesâ as opposed to âis included inâ. Did you make that change the first time around, before you split everything up?
Sorry, I havenât taken the time to fully absorb you situation yet. I guess Icm not fully understanding why you need to split anything.
No need to apologise - really appreciate your help and you help loads on this forum.
âis included inâ does not work for me. I know that looks for the first value in the second - but I donât get the expected results. The test site I created shows this (I have added a second collection so you can see the results of using âIncluded inâ). I agree - I would have thought it should be âIncluded inâ
All I am looking at doing is to return all rows from my âScheduleâ table that -
- Return rows from the Schedule table - where the Category field matches any of the Categories typed into the category field in the âviews tableâ OR return rows from the Schedule table - where the Venue field matches any of the venues typed into the venue field in the âviews tableâ
I am sure this is possible - it must be - I just feel like I may be barking up the wrong tree.
Spent the whole day more or less trying different things and just canât get there!
(Should say - it is working if I add extra columns to my view table to separate out the options - but that is not ideal)
Coming in late here, but just based on that description Iâd do something like the followingâŚ
Assuming that the two columns in your Views table contain joined lists of Categories and Venues respectively
In your Schedules table:
- Create a Single Value column that takes the Category column from the Views table and applies it to all rows. Call that svCategories.
- Create a Single Value column that takes the Venues column from the Views table and applies it to all rows. Call that svVenues
Now create an if-then-else column:
- If Category is included in svCategories, then true
- If Venue is included in svVenues, then true
I might be way offâŚ
Thanks Darren
I tried that - but - and I may be misinterpreting your suggestion - but no joy I am afraid.
Am sure there must be a simple way of doing this - and that I am missing something. Works nicely if I only need to search on single values - the problem only comes up when I have, for example, âVenue1, Venue 2â in the Venue field.
Yeah, it should be pretty straight forward. A bit hard to say where itâs going wrong without seeing it in front of me.
What went wrong with my suggestion?
I mean, how did the actual result differ from what you are after?
Just as a side note, if it were me Iâd probably take a different approach. Instead of joined lists and âis included inâ (which Iâve always considered a bit fragile), Iâd be using split text and relations. And Iâd probably use IDâs instead of plain text strings.
Thanks - I had the checkboxes all nicely set up in Schedule table - (I had to use a split text column too - to make it work - splitting the single values into separate items - is that correct?)
That was all fine. But I just then had two checkbox columns but couldnât work out how to relate that back to my views table. Wondering if it is getting over complicated for what I need.
Going back to basics - I have attached a screenshot of my two tables -
Detailed schedule - this has lots of rows (or will have lots of rows) - which can be âtaggedâ with a Category column and a Location column. A row may pertain to Venue 1, or Venue 2 - or both Venue 1 and Venue 2. It may also be âtaggedâ as a Header row, or to a particular supplier.
Views - this has a friendly name of the view - then ideally column for Category and a column for Location. The idea being that I can create a list of views, with a query column - that can then be used in a collection to return only rows that have the necessary tags.
Does that make sense?
Detail schedule table
Views table
This works perfectly until I enter more than one item in a tag column (as you can see from Venue 1, Venue 2)
Okay, now I see.
Sorry, my earlier suggestion was based on the assumption that your Views table was a single row helper table. My fault for not bothering to read the whole thread. So yeah, scratch that.
And now I understand your earlier comment about needing an OR in the Query column.
My immediate thought right now would be to use two Query columns - one that checks for Location matches, and then one that checks for Category matches. And then use each as the source of two separate collections (assuming your end goal is to use the results in a Collection). I guess the problem with that is applying further filtering could get a bit messy. So having a result in a single Query column would be better, yes? I think it should be doable - Iâll replicate that and have a fiddle.
Thanks so much Darren
Thatâs exactly right. Iâd use the results of the query in a collection - but it would need to be one collection so that all the info is together. I wouldnât want to have separate collections for venue and category.
It works perfectly until I want to enter two values / tags in one column.
After I first set it up - I just assumed Iâd need to use a split text column to separate them - but for some reason i canât choose a split text column as part of the query (whereas you can if itâs a relation)
Iâm really just trying to replicate Airtable Views - where by you can set multiple filters etc on one view.
Thanks so much
Just one thing⌠you can of course use an OR in a Query column, but I assume that it doesnât work here because you have other conditions to match which must be AND?
Can you show me what your Query column looks like without the extra conditions for Category & Venue?
Not sure how I can thank you for this.
Screenshot attached of query column.
It looks at the detail schedule and gets the value of location and category and matches against the same in the views table using âthis rowâ
Surely Iâd need to use OR as I want either category OR venue to show up?
oh, so youâre only filtering by those two columns?
I thought it was more complicated than that.
All you should need to do is flip those conditions around (which is what I think Jeff suggested earlier).
So:
- Location is included in This row â Location
OR
- Category is included in This Row â Category
And that should do it. Let me just confirm with the setup I started playing withâŚ
2 Likes
Just one questionâŚwhen you say âor both Venue 1 and Venue 2â, are you saying that a single row can be tagged with more than one venue? If true, that may have been part of my misunderstanding. I was under the assumption that your schedule table could only be tagged with one single location and/or one single category. Your screenshot isnât showing the scenario of multiple, so I want to be clear if itâs possible or not.
If it is possible to have multiple in one row, then @Darren_Murphyâs example may not work in all casesâŚfor which I have a different solution.
If you can have only one Location and/or Category in a row then what @Darren_Murphy showed is exactly what I was trying to explain.
3 Likes
Honestly - thank you both so much! Canât believe you went to the trouble of making a video @Darren_Murphy !!
So close -
This is very nearly working. A lot of the time, my Category or Venue columns in the schedule table will be empty. With Darrenâs solution shown in the video - rows with a blank category or venue were also returned - which isnât what I want. But I can get round that by having an If then column automatically filling in the column with an arbitrary values (such as âblankâ) which wonât get picked up by the Query,
But as @Jeff_Hager hinted at, it doesnât work if I have multiple values in a location or category column in the view table. It is very likely that I will need it to filter on more than one venue - (Venue 1 OR Venue 2 for example) and include all rows that match.
I assumed a split text column would allow the Query column to search on either - but it does not show up as an option for âthis rowâ
OK, so what I would do it treat everything like tags. In both tables, create a template column that joins the Location and Category columns together. So in both tables, you should have a comma delimited list of both locations and categories all together in one template column. Then create a Split Text column to split it into an array in both tables. Finally create a relation that links both Split arrays together. That should be it.
2 Likes
This should eliminate the need for your IF Blank column. May or may not have to address the cases with a hanging comma, but letâs see how it works first.
2 Likes
It worked! Thanks SO MUCH both if you.
The hanging commas donât seem to be an issue.
I have published it here if you would like to take a look : https://copy-of-schedule-6g15.glide.page
Only thing I need to work out now is how to make the âAll entriesâ work - but am sure I can manage that. It is getting late in London now!
Thanks again, Will do more testing tomorrow.
Andrew
2 Likes
