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.