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! Go to 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]), {.....})
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.
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.
Excusing me for not reading the whole post. But this reminds me of my filtering issues from June, which I solved here:
Best
Bob
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |