App for that, or Excel magic? Grocery inventory w/ recipe matching
August 18, 2016 8:28 AM   Subscribe

I'm looking for an app/program/spreadsheet/whatever that stores a grocery inventory with quantities, and a list of recipes (or just named sets of ingredient quantities). It would rank the recipes by % of ingredients on hand. If any part of this makes you think, "you could do that bit in [program] with/by [method]", I appreciate any pointers! Because I'll make it if I can't find it, but I'm not sure where to start.

I suspect this exact feature set isn't in any extant app or program so I'm happy to make something from scratch, but I'd be delighted to be proven wrong! I have Windows & Mac computers and an Android phone. I have Excel and some reeeeaaallly rusty knowledge of it, and I took a Java programming class once upon a time. So I don't really know anything, but I can teach myself if I have an idea of where to focus.

I'm aware of Supercook, but I'm deadset on matching inventory to only recipes I've selected/manually entered. Keeping track of quantities is also really important (I don't mind manually adjusting them after making recipes). I don't need cooking directions or scheduling. Super bonus points if there's a way to put in allowable substitutions in the recipes! It'd also be awesome if there was a way to give weight/priority to certain ingredients that I want to use first.
posted by Baethan to Computers & Internet (5 answers total) 3 users marked this as a favorite
I think you could make (or at least prototype) this in Excel. The core could have list of all known ingredients with several columns to the right. There would be one column that contains ingredients on hand, and columns for each recipe. For each recipe, you calculate a score with a =SUMPRODUCT([groceries],[recipe])/SUM(recipe).

That would at least give you the scores. Then you wrap a bunch stuff around that to make it usable and pretty.
posted by paper chromatographologist at 9:11 AM on August 18, 2016

Best answer: If you are fine with Excel, it won't take much more for you to be fine in Microsoft Access. It can seem daunting, but this is the exact type of problem that databases are meant to solve. A spreadsheet will get unwieldy quickly and formulas can break, etc. A table in Access is like a tab in Excel. A simple query is like when you use the filter option in Excel. A query with a join is like when you write a formula in Excel that references both a cell in the tab you are looking at and also a cell (or row or column) in another tab.

Don't put everything in a single table. I think you need at least four tables:
Recipes: Each one listed once by name
Ingredients: listed by name and measurement. This means if you have 5 cups of flour, you put "cups" in here (not 5, that comes later). If you have 3 apples, you just put "unit" or "count".
Match: a table that lists each recipe and each ingredient and then another column for an amount. This amount has to be based on the unit of measure you used on the ingredients table. Now you just put 5 in here for 5 cups and 3 in here for 3 apples. One record would look like Apple Cobbler, Apples, 3 and then the next would be Apple Cobbler, Flour, 5 and then Apple Cobbler, Cinnamon, 2.5. Don't use fractions, always use decimals.
Inventory table: This is a list of all ingredients and amounts you currently have, again using the same unit measure from the ingredients list. You probably want all ingredients listed here and filled in with 0 for amount.

Then you would write a query that would calculate how much of each amount on the Match table you have on the inventory table and then sum it up by recipe. That would be a complex query, but it is possible if you have a tutorial to walk you through the basics.
posted by soelo at 10:14 AM on August 18, 2016 [2 favorites]

Su Chef claims to do this, I think. As does Super Cook. I haven't tried these, I google searched for "recipe match app". Those are the first 2, but there are others.
posted by freezer cake at 2:00 PM on August 18, 2016

Best answer: I made an Airtable account some months ago and got prettttty prettttty into it (thanks, targeted Metafilter ads!). it could be used in a similar way to MS Access as suggested by soelo above. web-based, and they released a beta Android app a while back (maybe it's a full release by now?).

originally I wanted to format and populate a database that allowed me & my partner to (a) easily reference/update what ingredients we had at home, (b) input recipes manually to keep a list of our favorites, (c) calculate the cost of those recipes, based on what we had to buy, and (d) generate a grocery shopping list based on prioritizing the ingredients we needed. I got most of the way there (before getting lazy with inputting recipes!).

I kicked off using the "grocery list" template and then tweaked it up so that we could manually enter ingredients (and units) for recipes, and calculate the cost of making each recipe based on what ingredients I'd mark as 'have' (what was currently in the kitchen cabinet) and what was left to be bought. one can easily sort by, say, the final "cost" field, to reorder into a list like you described.

so my tables (Airtable "bases") are like: Ingredients; Ingredient Categories [for separating out my shopping list by supermarket area]; Recipes; Per-Recipe Line Items. In the Ingredients table, you could calculate and manually enter healthy/whatever substitute foods & quantities on an individual ingredient line, then work some magic there...

Airtable seems to have expanded the past couple months, so I can't exactly find how I outfitted the original template -- because my brain most definitely did not come up with it on its own. The explanation of this format might be in their Support/Q&A/Tips'n'Tricks, or I might have just Googled some tips on grocery/recipe inventories and adapted them to Airtable formatting.
posted by cluebucket at 8:59 PM on August 18, 2016

Response by poster: cluebucket, THANK YOU! If nothing else, their mention of ingredients-recipes as an example of a many-to-many relationship has started to clarify how I can go about setting this thing up. soelo's suggestion got me playing around in openoffice Base, and I'm sure it would've worked eventually... but for an abject beginner such as myself, Airtable is much friendlier. And okay, prettier.

Honorable mention to paper chromatographologist for a simple (if limited) solution that I totally could've implemented right away. But it's not fun if you haven't cried in frustration, right?

Hopefully I'll be able to share something workable soon for anyone who stumbles across this!
posted by Baethan at 7:59 AM on August 19, 2016

« Older how 2 marmite   |   If Tim Leary were taking patients... Newer »
This thread is closed to new comments.