String filter in dashboard - contains several values

Ask the Community
#1

It’s possible to filter dashboard data based on a string field, using the contains method and including several values in that contains?

For example,
Filter: list_of_ingredients (string that has all the ingredients of a product)
Want to: list of ingredientes contains: ‘potatoes’ ‘olive oil’ → which in sql would be something similar to → lower(list_of_ingredients) like ‘%potatoes%’ AND lower(list_of_ingredients) like ‘%olive oil%’
Result: all the products whose ingredients include, between others, potatoes and olive oil

Thank you!!

1 Like
#2

Hi Marta,

Unfortunately, the dashboard filters have not supported multiple value inputs for “Contains” operator yet. We might add it in the near future, so the filters would be more flexible for you.

That said, here are some workarounds for your case, depending on how your data is stored.

Case 1: If many products share the exact same recipe from the list_of_ingredients.
→ In this case, I would suggest you try the “IS” syntax for the ‘list_of_ingredients’ filter, and select the values you want from the drop-down. This drop-down list should show all distinct values of 'list_of_ingredients'.
This should take much less effort for you in this situation.

Let me give you an example with my “Countries & cities” dashboard’s filter:

drop-down
drop-down905×236 10.4 KB

Case 2: If each product has its own unique recipe from the list_of_ingredients.
→ Since the filter conditions on a dashboard are currently linked with each other by the AND operators, you can: add a filter where ‘field_1’ contains ‘string_a’, and then another filter with ‘field_1’ contains ‘string_b’ (and so on). This should get you the result where ‘field_1’ contains both ‘string_a’ and ‘string_b’ inside it.

Example for it here:

multiple-contains-conditions
multiple-contains-conditions906×194 5.95 KB

Please let me know if this helps!

#3

Hi again!
thanks for the answer, but unfortunately those workarounds do not help us out in this case.

The thing is that in my use case the field in the filter has several ingredients as shown in the picture:
image

and I would like to filter by several values that are included in the string.

For instance I would like to filter writing: “aceite de coco puro” and “zanahoria” so the dashboard shows just the recipes that contains those two string as part of the list of ingredients.

We had to discard:

  • one field per ingredient (ingredients are infinite)
  • filter by “is” and several words → as we always have the whole ingredients string in that field.
  • several filters of the same (We would have to include at least 6 of those but have a look how many filters do we have right now, could be crazy)
    image
    image1864×345 35.6 KB

that’s why contains: ‘potatoes’ ‘olive oil’ would be perfect for us :slight_smile:

1 Like
#4

Hi @Marta_Garrido,

Thank you for replying with great details, we really appreciate it! We have got the context & drawbacks you are having with these workarounds.

I’m sorry that currently the Contains operator in our Text filter hasn’t supported multiple string values inside yet. However we do have plans to support this, and will start working on it soon.
Will get back to you when there are any updates on it!

Thank you so much for your patience and understanding, Marta!
All the best to you and the team.

1 Like
#5

Wow! Great to here that :smiley:

Thank you very much Vu Duong!

#6

Hi again!

We have been testing how it would work when that feature it’s ready :star_struck: :crossed_fingers:.
And we have a suggestion to complement it. Once that’s ready, our aim is to have the list of unique options listed in the filter but then using “contains” those chosen options are looked for/filtered in the whole string.

To do so, we have created a master table with the unique values and other column with the whole string (so each row will create as many rows as unique values has in the whole string - for the unique values column - and the whole string in the other column for all of them).
Then we have include the table in Holistics with the relationship needed with the main table (whole string of this aux table 1-M to the whole string in the main table). Then we have use the unique values in the filter but then we filter the reports using the fields that contains the whole string. The problem is that when using “contains” the list of possible values are not displayed. Could be an optional check or so to be able to display them in “contains” filter too?

I understand that other use cases, if you use contains is to look for whatever so that’s why the list was not contemplated as needed, but in this would be very necessary :slight_smile:

Thank you very much!