Enumlist Dashbaord Filter - Multiple Selection

Jojo_Stella
Participant IV

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?

Solved Solved
0 24 2,670
1 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]), {.....})

View solution in original post

24 REPLIES 24

Hello, try this in the expression of a slice:

IN(
[EnumListColumn], datatable[ID]
)

tell me if you understood me please.

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โ€ฆ

Are you looking for something like this?

Yes!

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

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.

yes I already corrected my answer

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

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).

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.

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?

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
Participant V

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.

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โ€ฆ

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]), {.....})

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.

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.

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.

It doesnโ€™t throw an error, just wonโ€™t calculate without exactly 3 selected.

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.

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!!

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.

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
Participant III

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

Best
Bob

Top Labels in this Space