Contains vs. equals

[Dana_Selzer]

I have a field that notes all the week(s) a particular ingredient is included in recipes. The ingredients roll up to provide a weekly shopping list. I’d like to be able to identify the recipes each ingredient is a part of for specific week. Since that field can contain multiple weeks, I’m having trouble creating a formula. I need a formula that will let me identify a record if it contains a particular value, not equals. Is it possible?

To clarify, here is an example:
Olive oil is commonly used in several recipes every week. When I write a formula asking to return the name of the recipe based on where olive oil is used in week 5, I get nothing because olive oil’s “week assigned” field also includes 1, 2, 3, 4. An if/then statement won’t work because I need “contains” rather than “equals”.

[W_Vann_Hall]

I’m not entirely sure of your base structure, um, based on your post alone, but if your need is to identify a record where a certain ingredient can be found in any week, you’d want to use an IF() statement along the lines of

IF(
    FIND({Ingredient Name},{RolledUp Weeks}),
    ...

and so forth.

If you’re trying to match records where an ingredient is found in a specific week, it gets more complicated; you’ll have to ‘tag’ the ingredient prior to the rollup with a week ID and then use the FIND() function on the combined [tag:ingredient] construct. (For instance, garlic needed in Week 5 might be tagged as '5:garlic' and your IF()/FIND() statement would be

IF(
    FIND('5:garlic',{RolledUp Weeks}),

etc.)

Setting up such a tag system is usually both easier than it sounds and trickier than you think. If you’re in need of such complexity, give me a little more detail about your base — or, even better, post a shared link to a suitably innocent copy — and I’ll see if I can rough something in for you.

[Dana_Selzer]

Thanks so much. Your response actually helped with another issue I was having. Since I learn best by playing around and checking the result, give me a little time to work with your suggestions and I’ll get back to you if I get in over my head. Which is likely :winking_face:

[Dana_Selzer]

I think I need some more expert help. Here’s the background on what I’ve got and what I’m trying to do. I’ve created a base with three tables. The Master Menu Table is a repository of menus. The User’s Weekly Menu table is where I am pulling menus from the Master Menu Table to create weekly menus. The Shopping List is where the weekly shopping list is held. More details:

Master Menu Table: This table holds my master menus and is where ingredients and their amounts are listed so they can be rolled up to a weekly shopping list. I chose a multiple select field for the weeks that are assigned so I wouldn’t have to enter menus that are repeated from week to week. The goal of the Master Menu Table was to have recipes entered only one time. The fields with multiple select years and weeks solved one problem (not having to enter the weekly recipes over and over again), but it created several others, the biggest of which is I had to create separate columns for each week’s ingredient amounts so they could be rolled up. I’m sure there are formulas or a better underlying structure that would simplify lots of this.

User’s Weekly Menus: The purpose of this table is to provide a place to draw from the master menus to create the weekly menus. Recipes are pulled in from the Master Menu table. Over time, I will have enough recipes in my Master Menu Table to start recycling the recipes in different combinations in the User’s Weekly Menu table. Already the breakfast recipes are the same from week to week.

Shopping List: This food list is where I am rolling up the weekly totals from the columns in the Master Menu Table. On the far right is the column where I would like to just bring in the current week’s menus but because of the multiple select field where the year and weeks are assigned, it’s pulling in all the weeks. When I tried to write an If/then formula to bring in the current week (example: 6), it brings in those that are ONLY assigned to week 6, not those that contain week 6 (because of the multiple select field). I haven’t figured out a way to list ONLY the current week’s menus that are related to the ingredients on the shopping list. (The initial problem I posted about.) I want to be able to do this so if I choose NOT to make the meatloaf that week, I know which ingredients to disregard on the shopping list.

When I first read your responses, I thought perhaps I needed to move away from the multiple select field for the year and week assigned, but to do so creates a need to recreate/duplicate some of the recipes each week and I don’t want to do that. I would greatly appreciate any help or advice you could provide. Thanks!

(https://airtable.com/invite/l?inviteId=invvghwLFOF156sw7&inviteToken=bb2a86188dc0ffbc18105444998de17...)