Help filtering a rolled-up list

Hi! I’m having trouble with filtering a list of lists.

I have 3 tables:

  • One with food products that lists the ingredients and nutrition facts for that food
  • Another with instances of each food, how much I ate, calculating the nutrition facts, logging the date eaten.
  • Last one which summarizes all the foods eaten in a day and totals the nutrition facts, shows the ingredients I ate that day, etc.

What I’m trying to do is be able to filter the Daily Meal Tracking table by individual ingredients, so I can see all the days I ate foods with “Black Tea” for example. Or “Black Tea” and “Pasteurized Cream”. I’d also like to get rid of the blanks and consolidate duplicate ingredients (is there a way get unique values, not just count them?

Hopefully the doc sharing below works, sorry I had to share my entire doc, because I have everything in one place:

Hey, @Sarah_Arminta.

I think this Doc bellow can help you:
Playground Doc - Search with Regex

And to get rid of the blanks, you should remove the .split() from your formulas.

1 Like

What a great resource! I was able to get the search working, thanks! But it’s ugly. I removed the split(), but now I still have undefined and empty lists mixed in with everything. Any ideas to clean things up?

I was able to clean up the individual ingredient columns with some filters:

thisRow.[Foods Eaten].[Fruit Ingredients].Filter(CurrentValue.ToText() != "").ToText()

Then I cleaned up the full list with this:

Lower(thisRow.[Drink Ingredients]+","+thisRow.[Dairy Ingredients]+","+thisRow.[Fruit Ingredients]+","+thisRow.[Vegetable Ingredients]+","+thisRow.[Other Ingredients]+","+thisRow.[Spice/Sweetener Ingredients]+","+thisRow.[Meat Ingredients]).Unique().Split(",").filter(CurrentValue.IsNotBlank()).BulletedList()

And the search still works! Thanks for your help :grinning:

Hi, @Sarah_Arminta
I redid your Doc in a way I usually do mine.
Please, check if it makes any sense to you.

1 Like

Oooh, yeah I like it. Then you can filter with the control, nice!

I think I’ll work on putting ingredients in their own table, like you have. Thanks for the example! :pray:

1 Like

I published a doc with various search setups here if you want to copy it to see how they all work.

1 Like

Awesome @BenLee!! Thank you so much! :grinning:

1 Like

hi @BenLee, thanks for sharing this doc again.
I had a look at the Regex() and at the Find() solution, but the only formula I could find on these pages is this one.


On both pages this is the same. I wonder how the Regex & Find solution work, can you elaborate on this (even in the doc)?

Thanks!, Christiaan