FIltering sense check

[Andrew_Davies]

Hello all

I have set up filtering on a table and have it working nicely - but it takes A LOT of computed columns. If any experts have time, I would love their opinions / advice on whether they have a better / more efficient way.

I don’t want to use the inbuilt filters as they filter for all logged in users - and I prefer to use the choice component.

I have a “Detail” table - with lots of rows. Each row can have multiple tags applied and separately, multiple locations. Tags and locations are set in two separate text columns, using Row IDs of separate (Tags and Location tables)

I would like to be able to filter the table as follows:

• No tag / location selected - show everything
• One or more tags selected, but no locations selected - show every entry that contains one or any of the selected tags
• One or more locations selected, but no tags selected - show every entry that contains one or any of the selected locations
• If tags and locations are selected for filtering - then only rows that contain any of the selected tags AND any of the selected locations are returned.

This is how I have set up the tables.

Separate tables for Locations and Tags - with Row IDs

Detail table

Columns for description / Date / Author etc
Columns for tags as follows

1. Tag IDs (Multiple)
2. If 1 is empty - then *
3. Split Text of 2
4. Multiple relation of 3 to Tag table Row ID
5. Look up Tag name from relation in 4

Columns for locations

1. Location IDs (Multiple)
2. If 1 is empty - then *
3. Split Text of 2
4. Multiple relation of 3 to Location table Row ID
5. Look up location name from relation in 4

Query table (most likely in the user table)
Tag IDs to filter by (Set using a multiple choice component in the UI)
Location IDs to filter by (Set using a multiple choice component in the UI)

Computed columns for tags

1. All tag IDs (Joined List of all available tags - from tags table)
2. Add a * to 1 (Template column)
3. If 1 is empty - then 2. Otherwise 1.
4. Split text 3
5. Relation - where 4, matches IDs in Tags split text on the detail table
6. Look up row IDs of 5

Computed columns for locations

1. All Location IDs (Joined List of all available tags - from Locations table)
2. Add * to 1
3. If 1 is empty - then 2. Otherwise 1
4. Split text 3
5. Relation - where 4, matches values in Location split text on the detail table
6. Look up row IDs of 5

FINAL QUERY
Get all rows from the detail table.
Filter by Row ID is included in - this row> item 6 (Tags) AND Row ID is included in - this row>item 6 (Locations)

Appreciate there is a lot here, and as I say it is working as I want - but keen to hear others thoughts.

Thanks
Andrew

[Jeff_Hager]

@Andrew_Davies

I don’t want to use the inbuilt filters as they filter for all logged in users - and I prefer to use the choice component.

I agree that building your own filters is more robust, but why do you say this about the built in filtering? I assume you are talking specifically about in-app filters on a collection? Do you have proof of in-app filters affecting other users? I wouldn’t think that should be the case.

[Andrew_Davies]

Morning Jeff

Apologies - you are quite right.

The in-app filters on a collection are user specific - not by app.

I only checked from within the Glide builder (using Viewing as) - but I have just done a slightly more thorough test!

Essentially - what I am trying to do with this I guess, is filter on two multiple arrays. ie if array 1 contains X AND array 2 contains Y - then show the result. Would love to know if there is a better way - but I can’t see it if there is!

Thanks again,
Andrew

[Darren_Murphy]

@Andrew_Davies

Essentially - what I am trying to do with this I guess, is filter on two multiple arrays. ie if array 1 contains X AND array 2 contains Y - then show the result. Would love to know if there is a better way - but I can’t see it if there is!

I haven’t studied your original post, but isn’t that just a single if-then-else column?

• If Array1 doesn’t contain X, then null
• If Array2 doesn’t contain Y, then null
• Else true

[Andrew_Davies]

Thanks Darren

I am actually querying a table of data so don’t think that helps.

(ie - user specific columns in the users table, with queries / relations etc, that return rows from another table)

[Jeff_Hager]

You should be able to set up IF columns in the table you are trying to show. Then just set up one filter to show or hide rows based on those IF columns. I think if you avoid trying to do it with a query or relation, you can just use IF columns in the table and a filter on the collection…this should be pretty easy since you are already storing filter values in the the user table.

[Andrew_Davies]

Ahh - got you. Thanks Jeff.

But I am afraid I am after Rows with any tag included in the filter to be returned. “Contains” only works I believe if all values match. In the attached screenshot, I am searching for (Tag 1), (Tag 2) as an array - and need any row with Tag 1 OR Tag 2 to be returned. Back to my original example, I also filter against location - so in fact it would be:

(Tag 1 OR Tag 2) AND (Location whatever)

[Jeff_Hager]

Show how your IF is configured.

[Andrew_Davies]

[Jeff_Hager]

Yeah, not how I expected that to work. With a Relation column, you can relate array to array and it does not require everything to match. You could create a relation to link the two columns, then change your IF to check if the relation is empty.

[Andrew_Davies]

Thanks Jeff - glad I am not going crazy. Will go back to using relations.

Really appreciate your time and help.

Andrew