Apples & Oranges: select items using an advanced category filter. Is this possible?

Hey all, some tables to set the stage (with an arbitrary food theme):

Basic stuff right? Next step: a ‘recipe maker’, i.e. narrowing down possible food items by the selected category or categories. Like this:
image

UPDATE for clarity: selecting “fruit” as category should provide select list “apple” and “orange” (in ‘select food item’), selecting “pastry” should provide select list “donut”, etc.

I’ve been fiddling around to make the second column work, but no luck so far. I’d really appreciate any help/suggestions, if this is possible at all of course (is it?).

Some considerations:

  1. To start off with, I’d happy if ‘select category’ would only allow 1 entry (per row).
  2. If it allows multiple entries, however, it would be preferable if this narrows done possible food items rather than expanding them: an ‘AND-filter’ rather than an ‘OR-filter’.

Here’s a link to the doc, if you want to copy it and play around with it.

Finally, this thread seems somewhat related, but not quite.

Thanks!
Kevin

@mr_motus

Maybe this will help, as a starting point. You can customize it to your specific requirements.

1 Like

Wow, thanks for the trouble. I will play around with it for a bit and report back later. Cheers!

Here’s my report: it works great :grin: (at least when selecting just 1 category). The solution seems so simple, but I’d never have come up with it!

Currently, selecting more than 1 category leads to a bigger selection. E.g. selecting “fruit” & “green” leads to a list with all fruit items AND all green items, rather than everything that is fruit AND green. This is easy to fix if the number of selected categories is fixed (e.g. 2):

Filter([Food item DB],[Select category].Nth(1).In(Categories) AND [Select category].Nth(2).In(Categories))

This, of course, breaks when the number of selected categories is greater or lesser than 2. I’m struggling to find a way that works regardless of this. I figured FormulaMap() might be of use here, with Sequence(1,[Select category].Count()) as a way to iterate through every index of the list. But this might be a bridge too far. Thoughts?

Here’s my take on it. A bit more complicated than I’d want to but it appears to work for all cases.

I initially arrived at the same formula as @Ander, but as you @mr_motus observed it breaks for more than 1 category. This is because Contains is inherently an OR function.

The solution for this needs to make use of two nested FormulaMaps, but the problem is we only have access to the innermost CurrentValue.

So to be able to access both values we need to use columns across both tables.

So in the doc below, first Pre filter finds all items for each selected category. This will give duplicates.

Then Recipe Cat Count counts the occurrence of each Food item item in each recipe.

Effectively, a Food item will be filtered for all categories if and only if it appears in Pre filter a number of times equals to the number of selected categories. That is basically what Filtered Food does.

3 Likes

@Dalmo_Mendonca

Nice work! :muscle:

I was pursuing that same solution, but didn’t quite get it all put together. Good to know that at least I was headed in the right direction!

Dear @Dalmo_Mendonca,

Impressed about your creative approach and sharing it in the community. :hatching_chick:

Amicable,
//JP

1 Like

@Dalmo_Mendonca, that is very impressive! I’m still trying to wrap my head around what exactly is going on here, but it definitely works. Thank you so much, this will be a very nice quality of life improvement for me!

EDIT: A MODE.MULT function would’ve been nice by the way: [Pre filter].Mode.Mult() would’ve sufficed then…

1 Like

It would all be easier with a ContainsAll function!

Glad to help. I’d love to see your progress with this later on.

I was playing with something similar, thought I’d paste the full recipes text in a column, then use the natural language package to autocategorize it. Wasn’t sure on how to do the search, now your idea of ‘Maker’ helped me too!


Btw, I’d like to see some dev explan why this worked:

image

As the color shows, the parser identifies CurrentValue as a row of Food item DB inside the Filter scope, but that way the formula makes no sense.

It only makes sense if CurrentValue was the current category being evaluated in FormulaMap. Given that it actually worked, I can only infer that that’s what happening, although the colors show otherwise.

This only adds to the confusion regarding scope that some members have pointed out.


Thanks! I’m only inspired by you and others before me :wink:

2 Likes

Good point! Didn’t even consider ContainsAll() anymore. I will add it to my request thread. I should have something ready to share in a few days. To be continued…

@shishir @mallika I still think this is a bug

Dear @Dalmo_Mendonca,

See the post below, your wish came through as ContainsAll() is now available :slight_smile:

4 Likes