AnsweredAssumed Answered

Join table to populate numbered ingredient fields?

Question asked by hellkay on Feb 10, 2016
Latest reply on Feb 12, 2016 by DanielShanahan



I'm a filemaker DIY'er, and having trouble creating a join table using data imported from the existing ERP system:


Table A: Recipe Table - each recipe with up to 20 ingredients: ingredient fields are numbered ing1 > ing 20, ingredient qty fields also #1 - #20.

Table B: Ingredient Master Table with inventory levels (6500 records)

Table C: Ingredient Log Table with batch dates, date of purchase and current cost


I want a table where the existing recipe and ingredients data can be displayed, along with inventory levels and latest cost date.  I need to be able to create an updated recipe cost based on updated (or projected) ingredient costs.


So far I have a join table between A & B, primary and foreign keys, but can't figure out how to key the ingredient table items to each of the numbered ingredients in each recipe.