Trying to make a Dynamic Search Filter for my user...

Jordan_Sidfield · 05-08-2020 01:43 PM

Hello!

I have a big database of songs with many different columns such as genres, themes, and vocal range etc. that I would like my users to be able to filter and search through more easily. I tried using a form, with Enumlist columns so users can input multiple genres or other types of variables into their search. But I’m having trouble getting the form to then LINKTOFILTEREDVIEW. It just either brings me to a blank page, or the regular data without filtering any info selected at all. Please help!

I’ve been using this expression in the behavior LINKTOFILTEREDVIEW(“By Songs”, AND([Genre] = [_THISROW].[Genre], [Tempo] = [_THISROW].[Tempo], [Vocal Type]= [_THISROW].[Vocal Type])). The intention would be a user selects their desired genre, tempo and vocal type (such as “Contemporary”, “Ballads” and “Tenor”) and then they would get a filtered view of only the songs with those matching categories.

Am is using the wrong expression? I am only testing with a few columns as well there is other columns I’d like them to be able to filter through with their search. I hope someone can help me.

I used this sample app to try and mimic what it is doing with its own search filter function, but with the ability to make multiple selections.

Thanks in advance!

Austin

Depending on your number of columns, you can use the usersettings to filter slices down.
You would have them start/go to the settings view, make their selections from your desired filters, then have a view based on a slice that is filtered based on usersettings.
Here is my example of one that I have for my app. I use a table called Filter that is just the single users row of filter values.

Jordan_Sidfield

Okay would that go in the Behavior for my Filter View Target? I have about 12 different columns I’m trying to use for the filter (I know that’s a lot but the point of my database is to be comprehensive hahaha), but I’m testing just a few of them in the coding right now. Or do I need to make a slice? For some reason the sample app didn’t need to make a slice it was just able to use the LINKTOFILTEREDVIEW function but it doesn’t seem to want to work for me. It just keeps bringing up either nothing or showing my regular table view without any filtering done at all.

Austin

So you definitely need a table to hold your filters. The structure would be:
UserEmail, Filter Field 1, Filter Field 2, etc. Use a security filter to single this table down to only the Current User so [UserEmail]=USEREMAIL().

If you are going to be using 12 columns I recommend the slice method.
1.Create a slice,
2.Add your conditions to the row filter,
3.Create a view using the slice as the source,
4.Have a view/form where the user can edit their filter(I have mine as a detail view with quick edit fields in a dashboard so my users can edit their filter at the same time as they view my filtered table view),
5.On save from your filter form send them to the filtered view or just if you’re using the quick edit fields just let them click back to the filtered view.

I recommend defaulting the filter to values that will either show 0 results or show every result just for usability purposes.

Jordan_Sidfield

Okay I’ll try to use the slice function, but how do I set my conditions so that it knows how to filter for the “Row Filter?” What kind of expression should I input, or do I just input all the Column Names? This is so much more complicated than I was expecting and I’m not very tech savvy tbh so all of these expressions aren’t feeling super inherent to me. Thank you for all of your help Austin!

Austin

The expression for 12 columns of filtering will probably be some ungodly mess of an expression. Especially depending on the type of each column. You will likely just want to AND() all of your columns filters together. Doing this will definitely require a strong understand of logic statements. Bahbus’s sample app could be an easier solution to implement.
Some adivce:If the value of one column affects the logic of other columns then you probably want to re-structure your filter table. Avoid using columns that are Do you want to search by Name? Yes/no. What name do you want to search by? Re-format that to What name do you want to search by? Leave blank if you do not wish to search by name. This adds extreme complexity requiring Nested AND()s and OR()s.
The 2 most likely expressions you will need to AND() are below
If the column is a numerical value then: ANY(Filter[Column Name])<=[Column Name]
If the column is a text value then:
IF(
ANY(Filter[Column Name])=“Default Case”,
true,
ANY(Filter[Column Name])<=[Column Name]
)

Bahbus

Hey Jordan,

Take a look at this sample app I made. It will probably give you some ideas on how to implement what you want.

Jordan_Sidfield

Is this possible to perform with only two tables or will I need to add a third table like yours? I’m just working with two tables at the moment, “Audition Source Database” and “Filters”. I’m not using IDs, but is that something that is really necessary? Is this possible with just using the columns from “Filters” to search for the common values in “Audition Source Database”? For example: While in the “Filters” form or whatever table is deemed necessary, a user can select “Ballad” from column [Tempo], then “Tenor” from column [Vocal Type], or multiple selections like “Contemporary” and “Pop” from [Genre], and it then produces a list of the relevant songs from Audition Source Database?

Will I need to make a third “Search” table to perform this function similar to what you have or is there a way to make it work with two tables at all? And without needing to add “IDs” to all of my songs. If I have to add ID’s that could be an issue unless there is a way to auto-generate them for what I’ve already input in the database (I have over 1000 different songs at the moment).

This looks very close to what I’m trying to do though but with an added layer of complexity, given that I have your version of the “Tags” split up into multiple columns of different sizes. Most of them I have set as “Enum Lists” since I want my users to be able to select multiple functions per Filter column. I liked that I was able to have the drop boxes appear and select multiple options like in the Form version of the table. Is there a way to keep that?

Bahbus

You don’t necessarily need the third table. My Search table is there only so I can have the view setup the way I do, because I didn’t want to use a Form. And I always try to build my apps as if I’m not going to be using it, so I separate the data as much as possible for better/easier validation, because other people are stupid.

The direction you are currently going in is a simpler direction anyway. You have your search terms in separate columns and they are being compared to separate columns in your database. The only thing of interest to you would be how I use the INTERSECT() function. And that is only if you need to compare lists to lists.

You can specify dropdowns or buttons. I just like buttons better for multiple selections.

Jordan_Sidfield

Hey Bahbus! Thanks again for your help! Your app is soooo much closer to what I was trying to get but I’m still getting stuck on how to make the formula work. When I input the Intersect formula its been giving me error messages every time.

I’m trying to make it simpler for myself now by just focusing on searching by one column entry. How do I make a formula that takes the inputs from my Filter Columns such as [Genre] so it returns rows from the other table that contain the selected [Genre]. This whole thing is really just not my forte so I’m just really grateful to you and to the community for helping me figure out how to solve this problem.

The Slice is using the main database as the source table, similar to your articles one. I have a second table of filters, which lists out all the filters that can be found in the database table.

When I put INTERSECT([_THISROW].[Genre], Filters[Genre]) it gives the error message “both lists must be of the same type.”

How can I restructure this to make it do what I’m trying to accomplish? Then how can I add on the other filters? There are so many places that have search filters which make it so easy for people to sort through databases and options I’m just looking for something similar.

Bahbus

INTERSECT() requires both arguments to be a LIST. [_THISROW].[Genre] is probably not a list, though if [Genre] is apart of a song database, it might be helpful if it was a list as songs can theoretically be apart of multiple genres. Otherwise, you’d want to use IN().

MJMJR159

Can you just use the built-in filtering from the search tool native to AppSheet? When you clock the magnifying glass icon, the search bar opens. The right side of the search bar has 3 lines. Click that and the filtering options by column appears.

Steve

Filtering from the search bar is a brand new feature. This topic predates this new feature.

Trying to make a Dynamic Search Filter for my users to sort through data!