You'll really need to learn filemaker scripting and how to set and use variables to accomplish this. I'm not sure how access translates to this (most likely some sort of VB scripting, which I'm not sure you ever did).
The filemaker script will start in the sales forecast table, and loop through each record there, it would look something like:
1) GoTo record - first
Loop actions to find a list of related ingredients, and perform subloop to generate ingredient forecasts based on sales forescast quantity and recipe ingredients.
Return to sales forecast table and goto next record, exit after last record in sales forecast table
3) End Loop
Thanks Mike for your response. You have confirmed what I eventually discovered. Now I'm on the journey to find some decent info on scripting. To be quite honest that's proving a challenge. If you've any recommendations on where to go for a beginners guide that would be appreciate. Plenty of stuff explaining what the functions do but not much on building scripts. Guess it's assumed that you come from programming in other languages so there's no need.
Sent from my iPhone
Hmm, now that you mention it I don't really know any *great* resources for learning scripting. It's just pretty easy to learn since most of it is based on general menu commands that you can perform manually in filemaker.
I would start by searching youtube for "filemaker scripting". http://www.youtube.com/results?search_query=filemaker+scripting&oq=filemaker+scripting&gs_l=youtube.3..0.744.2572.0.26184.108.40.206.10.10.0.153.648.8j1.9.0...0.0...1ac.1.sYAoz6PkvyU
See where that takes you. If Filemaker is going to be an important part of your life going forward, I would definitely recommend going through a training course at your local authorized filemaker trainer. http://www.filemaker.com/support/training/authorized_trainers.html
If you think about the way filemaker works as a program (IE, how would I do this manually in filemaker), it will help you in thinking of how to script processes to automate repetitive tasks (looping) and save normal processes (IE reports and exports).
What I'm trying to do is take the results of a sales forecast for products and the recipes for those products and work out the quantity of ingredients I need
By all means do learn scripting. However, IMHO this task depends first and foremost on relationships.
I don't see in your PDF how a forecast relates to the recipes; I would expect to have a list of recipes to be used during the forecast period. This would be starting point for calculating the required quantities.
Note that this is by no means a simple task, primarily because the same product can be used in many recipes.