to solve it like in your screenshot you have to count the appearance and than divide it by the cost. If you want to apply it to our first example with multiple items in one row, than it gets waaay more complicated. I made you an example for both in the doc.
You just filter the table for the item and add a count() formula in the end:
[Main table Solo].filter(Menu=thisRow.Menu).Count()
Calculate cost per item
Than you filter the pricing table for the price and do the math
[Menu table Solo].Filter(Dish=thisRow.Menu.ToText()).[Cost total]/thisRow.Appearance
Of course you can also do it in one formula/colum, but it easier to explain in two
You could use the lookup() OR the filter() formula here. The main difference is, that filter allows multiple criterias and lookup just one. I usually always go for the filter() even I just need one, because I don’t know if I will need more later – I better be prepared
This is more complicated as you have to iterate through the values (e.g. with with FormulaMap()). There are multiple ways where and how to do that. I think the easiest one is to make the calculation in the pricing/menu table already and just use the data in the main table. If you’d do it in the main table, you’d probably have to iterate through everything twice which is not an easy task in Coda, as nested “CurrentValues” are not working.
The highlight here is probably the count appearance part:
With FormulaMap, I go trough every row of the main table and use a formula for each of them. In there I want to search the dish in the bullet list. But the find() formula alone returns just 1 when it is found and -1 when it is not found, which doesn’t help me in the total number of appearances. So I use a if() formula around it to check if the find formular returns a number higher than 0 and if yes it notes down a “1”, if no it notes down a “0”. In the end I sum up all the 1 and 0 which gives me the exact number of appearances.
[Main table Multi].[Menu (auto)].formulaMap(
Important notice: This all only works as long as every dish has a unique name! If e.g. there is a Pizza in ‘american’ and ‘italian’ kitchen, you’d have to give them different dish names e.g. ‘American Pizza’ or even work with dish Id.