Hey People,
I’m porting my recipe book from sheets to Coda.
Ingredients table has ingredients and info about them.
Recipes table has all the recipes and one of the columns is an ingredient.
I use formulas to pull and calculate details based on ingredients eg. Lookup(Ingredients ,Ingredients.Name , thisRow.Ingredients).[GBP / ml]*thisRow.Amount
As you can see it works perfectly fine if Ingredients column is a raw text.
But I want that column to be a relation pulling/allowing only ingredients already added in their table. The moment I change they column type lookup breaks and all lookup values go to zero.
The doc you’ve shared isn’t editable so it’s not possible to inspect the formulas, relations etc. Could you create a copy of the doc and share that? (So that folks can make changes/suggestions without messing about with the original doc)
The Coda recommendation is to use Filter instead of Lookup and that may give you the results you are after?
From your description it is not clear whether you want to report existing relationships, or whether you want to create new relationships. Formulas, both lookup and filter will only report on existing relationships between the two tables.
If that is what you want to do, the reason your formulas fail when you change the column type, is you are no longer comparing apples to apples. When you make the column a relation, you will see that the text nie has a little bubble around it. This means that the column no longer contains simple text, but contains a reference to another table.
One way to restore the formula to working is to add .totext() at the end of the relational reference.
Not able to see the document, and being on my mobile, this is my best guess.
Hello @Landsil ,
Actually, you have to work a bit different. The problem with your lookup formula is that, when the ingredients table is a relation column, you are comparing two different types: a text value with an object.
As said in this thread, it is good practice to use filter rather than lookup, but both work.
To do what you want to do: make an extra column in your ingredients table and call it, for example, IngredientsObject. Use a formula to fill the column, the formula is thisRow().
Now you can lookup/filter matching the ingredients from the relation column with the IngredientsObject from the Ingredients table.
I also altered your Drink Amount column:
This looks silly for some people, but if you hoover over the the two Name objects, you will see that one is CurrentValue.Name and the other one is thisRow.Name. It is a matter of selecting the correct one from the offered options when you start typing Na…
I realize the above might sound a bit cryptic, so I will share the doc with the changes - make a copy to check out the formulas and setup:
