Enumlist Dashbaord Filter - Multiple Selection

[Jojo_Stella]

Hey there! Trying to make a slice for a dashboard filter.
The column in the filter table is an Enum list. Trying to search a selection of user selected genres against a table of bands who’s [Genre] Column contains the previously mentioned Filter Table[Genres]

My slice expression is:
CONTAINS([Genre],Any(Filter Table[Genre]))

It works, but won’t register more than one selection? it ignores everything after the comma/ delimiter…

Is there a function similar to ANY() which allows multiple selection, as ANY() only allows ONE selection from a list of many? Or a means for the CONTAINS() Function to consider all of the selected Enumlist options? I need a Selection of Many from a Selection of One OR Many if possible…

I tried turning the string into a list with:
CONTAINS([Genre],SPLIT(Filter Table[Genre],","))

But that did not work either… I also searched through the forum for a similar issue to no avail…

Would anyone have any suggestions?

[FREDY_ORTEGON]

Hello, try this in the expression of a slice:

IN(
[EnumListColumn], datatable[ID]
)

tell me if you understood me please.

[Jojo_Stella]

Hey I tried this one as well, but it returns the Column as exact. So it includes the delimiter as a part of the string and tries to match the string as exact, instead of part of.

For my use case I need to be able to for example select Metal and Funk

Which would look like:
Funk,Metal

In the filter table.

Your expression would look for “Funk,Metal”

Currently my expression understands it’s viewing a list of items… just only calculates the FIRST item…

[FREDY_ORTEGON]

share.getcloudapp.com – 12 Nov 19

share.getcloudapp.com/eDuLZpXe

Shared with CloudApp

Are you looking for something like this?

[Jojo_Stella]

Yes!

[FREDY_ORTEGON]

ok, From what I understand then your slice expression should be:

IN([Genre],Filter Table[Genre])

when “[Genre]” is a column of you datatable and “Filter Table[Genre]” is a enumlistcolumn

[Jojo_Stella]

Hhhhmmmm… I’ll try it, though my setup is reversed. Filter Table[Genre] is my enum list, and Bands[Genre] is my data table I’m filtering rows out of.

[FREDY_ORTEGON]

yes I already corrected my answer

[Jojo_Stella]

Gonna try this real fast, and let you know results!

[Jojo_Stella]

FREDY_ORTEGON:

IN([Genre],Filter Table[Genre])

Okay so The result was that this time it did what it was doing before BUT it also EXCLUDED any bands who’s genre contained extra text OTHER than the first selected genre.

Instead of seeking artists who’s genre contains part of the string…

For example if I select Emo and Funk, I want every band who has either Emo or Funk in their Genre to pop Up. But I can’t make a static list of genres because they are dynamic (the contained genres change and there are too many bands to spend time standardizing the [Genre] column in the bands Table, though we’re working on utilizing some RPA to solve that issue in the future).

[Bahbus]

I believe you’ll need a much more complicated expression. If Filter Table[Genre] is a column of EnumList containing the selected options a user is searching, there is no current function that will work like that. There is no check to see if multiple item list has things in another separate list. Which may be important in the long run since many bands use multiple genres. Even if the band only has 1 genre, if you don’t standardize it, it will create other problems. Not to mention that EnumLists don’t always behave the way you think they will. In the future, I would do one of the following:

-Make a standardized list of genre’s, use them a reference to the band’s genre through valid_ifs, and slowly go through and update the bands with those from the standardized list. This way everything stays the same and working.

~OR~

-Let the bands use whatever they want as their genre, use the band’s genres as a reference to build options for the EnumList. The upside is bands can be themselves. Downside is that you may end up with too many very specific genres that don’t help the user or just make filtering for them impossible.

As for getting this working now, send us pictures of the settings for Filter Table[Genre], Bands[Genre], and maybe a line or two of sample band data.

[Jojo_Stella]

I think I may have to go with a bit of both approaches… Like build up a standardization through using the band’s genre as a reference… and any bands that use weird unique genres such as “Insert genre Here”, or weird symbols (It’s a huge problem actually hahaha)… Those will have to be hand searched and added to the standardization over time…

ALL of that being said… At this time I am using a preset non dynamic list for testing and still having the same issue… What is interesting to me is that the ANY() Function works exactly as intended here… it SEES the first selection (according to alphabetical order) as ONE string to match among all the [Genre] Column rows… So is there a function that works the same as ANY() but instead of selecting one from many the Function could be any number from the list? So like… conceptually Enum is to ANY() and Enumlsit is to ?

Thoughts?? Am I making sense?

[Jojo_Stella]

Dashboard View:

Code Snip (Highlighted relevant sections)

If the user were to unselect Emo in the genre filter in the dash they would get entirely different results. Which is not what I’d like… I’m going after searching each value between the comma delimiter as a possible value. So Any Band containing Emo anywhere in the Genre, and any band containing Funk anywhere in the Genre.

[Bahbus]

There sadly is not a function that does any of that.

That…is a big ass expression.

Anywho, what I really needed to know before I could help further, is what data type is the column for Band Genre? Is it just text or long text? Because that is the whole problem with the previous attempts.

[Jojo_Stella]

Ahhh my bad, I really appreciate your time in helping me here. That Column is also an Enum List… Though I haven’t tried experimenting with the column type…

[Bahbus · Accepted solution]

Ok, so that is probably where we’re stuck.

Based off of the screenshots, EnumList currently won’t work, because (and I’m guessing here) that I’m willing to bet that since there isn’t one standard delimiter to work with, only certain bands have their genres breaking into their individual values and other times where the genre is being stored as one whole string. This was one of the problems you were going to run into in that I mentioned earlier.

Now, that isn’t to say that EnumList isn’t going to be the plan though. It just needs to be standardized on both sides in order to work. However, as you have mentioned, it’s going to be a lot of work to standardize all of it.

So here is what I would do.
First, clone the app as it is right now as Version 2. This will where you can work on the hard part. More on that later.

On version 1, switch the band’s Genre to Text. From what I see, a majority of the bands used either commas or slashes to delineate. Then change the expression to
OR(CONTAINS([Genre], INDEX(Filter Table[Genre], 1)), CONTAINS([Genre], INDEX(Filter Table[Genre], 2)), CONTAINS([Genre], INDEX(Filter Table[Genre], 3)), {.....})
The {…} part you can delete or use that space to keep going with the expression. I would choose a maximum number of filters (maybe 3 or 5 sounds reasonable), and then come up with a message somewhere to users that only the first X selections will actually be filtered. Let me know if that works temporarily.

Version 2:
A new table consisting of just genre options. 1 genre per line. And I would stick to the most easily recognizable ones and not ones where people go “wtf does this even mean?” Then both genre columns can be EnumList of base Ref pointing to the new table. Both of these Valid_Ifs would also be just [Genre]. That will ensure both the filter list and the band’s listed genre’s will be from the same data. By default, AppSheet won’t do anything with it until you attempt to edit the band, at which point it will yell at your about the genre’s not being valid and won’t let you save it. Then your expression would be

OR(IN(INDEX(Filter Table[Genre], 1), [Genre]), IN(INDEX(Filter Table[Genre], 2), [Genre]), IN(INDEX(Filter Table[Genre], 3), [Genre]), {.....})

[Jojo_Stella]

This might just WORK! I’ll try it real fast. This seems like exactly what I was looking for.

I’d like to try and if possible keep it leaned towards the first version and search for instances of the strings. We add thousands of bands every month and keep about 1/3 of them, and it would be near impossible to build it as you described in the second version because of the inconsistencies… Also our agents do not edit the band data. It’s edited through the back end by a whole separate system and schedule… So appsheet bringing up invalidation rules shouldn’t be an issue there.

[Jojo_Stella]

Bahbus:

OR(CONTAINS([Genre], INDEX(Filter Table[Genre], 1)), CONTAINS([Genre], INDEX(Filter Table[Genre], 2)), CONTAINS([Genre], INDEX(Filter Table[Genre], 3))

You’re a hero @Bahbus!
Thank you so much. Now I just have to make it so the expression doesn’t require 3 selections and can be between 1 and 3 items.

[Bahbus]

If it throws an error if you don’t have exactly 3, you can add a check to COUNT Filter Table[Genre] using an IF or perhaps a SWITCH.

[Jojo_Stella]

It doesn’t throw an error, just won’t calculate without exactly 3 selected.

[Bahbus]

Ah, yeah. That makes sense. We’re throwing AppSheet a null value anytime we ask for the index that doesn’t exist so it probably just cancels the expression or something. I’m surprised it doesn’t throw any kind of error at you actually.

[Jojo_Stella]

FINAL SOLUTION:

IF(
COUNT(Filter Table[Genre])=1,
CONTAINS([Genre], ANY(Filter Table[Genre])),
IF(
COUNT(Filter Table[Genre])=2,
OR(
CONTAINS([Genre], INDEX(Filter Table[Genre], 1)),
CONTAINS([Genre], INDEX(Filter Table[Genre], 2))),
OR(
CONTAINS([Genre], INDEX(Filter Table[Genre], 1)),
CONTAINS([Genre], INDEX(Filter Table[Genre], 2)),
CONTAINS([Genre], INDEX(Filter Table[Genre], 3)))
)
)

Gonna add one more if function to allow up to five genre tags. but this works gorgeous.

I wanna thank you so much for your time and help @Bahbus!!

[Bahbus]

You are welcome. I did something kinda-ish similar with the whole EnumList vs Text thing except I was using two different delimiters. Mine was just slightly easier because I could subtract any and all null values at the end, so I didn’t need the IFs and COUNTs.

Going forward, I can actually come up with a number of solutions to simplify/optimize the main app code. But its going to take time. If my previous option 2 is completely out of the question, then I would suggest either taking all the band’s genre data and attempt to find patterns for genre types that you can always use no matter who fills in the data, OR attempt to build a database who sole purpose is to read in the genres and then “decide” what genres you have that fit closest. The built in machine learning might be able to help? I’ve never used it.

[Jojo_Stella]

Yes, I agree there can be some optimizations absolutely. Currently my goal is to just get certain things working, put a few of my agents in there, and begin optimizing in another version and update the “Stable-In use” version from the “Hot” version as I go.

Yes, I’m thinking some machine learning/ RPA to over time build standardization. However, unfortunately even then it’s not a perfect solution. Many artists add genres that aren’t even text… They use alternate keyboard characters or online tool generators to try and be unique and quirky… So their genre ends up in my table as something like … An upside down cross surrounded by two hearts and separated by 3 - 6’s lol…
Just weird things like that… THOSE, I’m guessing will have to be done by hand unless I can manage a way to use RPA to navigate to an example of the artist’s audio and then utilize machine learning in my workflow to analyze snippets of audio.
This approach too would need to be standardized… like seek out 1 or 2 possible common repositories where the artists might show their music such and Bandcamp and Soundcloud (easier to automate the webpage)… But even then not every artist has music on those platforms.

So while there’s a lot I can do to standardize the Genres, in the end Data visibility is pretty low overall… BUT could get pretty damn close to near perfect.

[Bob_Haizmann]

Excusing me for not reading the whole post. But this reminds me of my filtering issues from June, which I solved here:

Change row filter condition for a slice based on user input Questions

I’m filtering a slice based on user input within a dashboard as follows: AND(ANY(Filter[Observation Type]) = [Observation Type], ANY(Filter[Description]) = [Description], ANY(Filter[Work Status]) = [Work Status], ANY(Filter[Start Date]) <= [Date and time], ANY(Filter[End Date]) >= [Date]) This expression works fine. But I want the user to have a choice whether or not to filter on “Description”. He would choose Yes or No to “Do you want to filter on Description?”. If he chooses NO the expre…

Best
Bob