OK, this is a weird, out-of-the-box use case, but I have a client looking for a solution and I want to see if Fibery is up to the task. And since the problems involved here are closely related to some other challenges I have run into around field proliferation, entity proliferation, storing temporary values, in-line (in Rich Text) Field Values and Calculations, etc. I thought it worth bringing up here. I’m very curious if people have thoughts on how I can approach the following with current Fibery functionality, and whether any of the challenges connect with needs that other folks also have in their work.
Problem Definition
- Need to store and maintain Recipes that relate to Ingredients
- Ingredients include Pricing
- Ingredients need to be scaled for each recipe
- There are “production runs” or “bakes” where X number of units of a given recipe are made, and thus there must be scaling for Recipe which then requires scaling of Ingredient for each recipe for each bake (e.g. 5x of recipe A, which includes 4 cups of unit X, 2 cups of Unit Y, 2 tablespoons of unit Z, so all need to be scaled).
Because of the need for different quantities and scaling for each recipe, and the fact that many recipes would share some ingredients, the only two ways I saw to do this were to
- Have a new Field for every Recipe’s scaling value (a cumbersome approach), or to
- Have a new “Scaled Ingredient” sub-Database Entity for every Recipe which uses a given Ingredient
The latter seems acceptable if I can automate creation of these sub-entities, although it still seems clunky.
Also desirable is a reasonably printable, Rich Text-style output of each scaled recipe + ingredients for a given “bake”.
My Current Solution
I have begun prototyping this with the minimum needed fields, but am not finished. So I am not 100% certain that this will work yet, or precisely how, since I haven’t completed it yet. But this is what I’ve come up with as a mental model. Let me know if you see a better way, or an issue with what I’m planning!
Databases and basic relationships
- 4 databases
- Bake (Production Runs)
- Recipe
- Ingredient
- Scaled Ingredient
- Bakes have multiple recipes, recipes have multiple bakes
- Recipes have multiple Ingredients
- Recipes have multiple Scaled Ingredients
- Recipes have base Quantity and Scaled Quantity Fields
- Ingredients have multiple Scaled Ingredients (relation)
- Scaled Ingredients Lookup some values from Ingredients (e.g base unit and measure, cost per unit)
- Scaled ingredients have “New Quantity” field for scaling
Process
- You start by setting up Ingredients with name, unit, base quantity, and cost
- Add Ingredients to Recipe
- A Button then loops over Ingredients (collection) and creates Scaled Ingredients and connects them to the current Recipe (relations)
- Then set the New Quantity of each Scaled Ingredient for that Recipe (manually in each Scaled Ingredient in the collection)
- Now create a Bake and add one or more Recipes and a scaling value for each.
- To avoid having to have a “scaled recipe” database, I’m thinking of just using generic fields in a Bake “Scale Value for Recipe 1”, but this is fragile.
- It’s also only acceptable, where a similar approach for Ingredients is not, because there are assumed to be relatively few Recipes in a given Bake, but this may not always be the case.
- Alternatively there could be a “Scaling for Current Bake” field in the Recipe database that just gets reset each time, but then you can’t maintain any record of what was done in the past as far as quantity of each recipe per bake.
- A button loops over the Recipes pulling the ingredients and scaled values into a Rich Text and appending for each one into the field on the Bake itself
- Ideally this process can also calculate the total quantity of each ingredient by checking the parent Ingredients of each Scaled Ingredient and adding together the New Quantities for all with a common Parent.
- The value of this would ideally go in a field somewhere and not just Rich Text, but this seems to run into the original problem: where do you put values that are only going to be needed this once? Do I have to create a whole new Type for this?
What Could Be Better
How do you deal with values which exist for X number of Entities (e.g. you don’t know how many Ingredients will be in any given Recipe) but should belong to the Parent? That, I think, is the principle challenge here.
I’d love a better solution to a proliferation of special sub-database entities and/or fields just for the largely temporary function of scaling (although the scaled data would ideally be maintained, too). I think it might be doable entirely within a formula, but it seems complicated and potentially fragile, especially for someone else to maintain. At least the sub-ingredients are understandable for most people, even if a bit clunky.
Also a better, more flexible way to turn the data into printable text would be really nice. I haven’t attempted a version of this with Coda yet (pending!), but my expectation is that in-line values and formulae, etc. should allow me to do this a bit cleaner, without having to redo everything in the Rich Text if I just change one scaling value, etc.
Some past, related discussion
I think this would solve it by having a Value I could set for a Relationship uniquely for each Recipe to each Ingredient’s Scaling Value and each Recipe’s scaling value for a Bake:
I have begun testing this, and I have a basic adaptation, but now need to figure out multiple recipes (steps) per bake (occurrence), among other things. But it’s certainly promising as an alternate way to approach the problems! Thank you again, and I’ll report back soon with further experimental results.