Can you post some examples of the Inputs and outputs for this function?
To illustrate the solver function, I am a slightly adjusted example from the microsoft site (http://office.microsoft.com/en-us/excel/HA011118641033.aspx) as it is a good representation of what I want to do in Filemaker without having to export and import massive data files.
The example is as follows:
A coffee shop currently sells three beverages: regular fresh-brewed coffee, premium caffe latte, and premium caffe mocha. The price of regular coffee is $1.25, caffe latte is $2.00, and caffe mocha is $2.25.
The objective of the coffee shop is to maximise total revenue each week.
However, because of storage facilities and merchandising constraints, the coffee shop is currently able to produce only 500 cups of coffee (both regular and premium) per week.
Since we want to maximise revenue for the coffee shop, we want to determine how many cups of regular, Premium lattes and Premium mocha coffee we need to sell.
Putting this in maths we get:
Revenue = Quantity Regular x price regular + Quantity premium Latte x price latte + Quantity premium Mocha x price Mocha
Quantity Regular + Quantity Premium Latte + Quantity Premium Mocha <=500
All of the variables (quantity, price and revenue where the latter is a calculated sheet based on the function described above) are separate cells in Excel. The solver function automatically choose a quantity for each of the coffee, calculate the corresponding total revenue for that combination of quantities, checks whether the quantities selected are less or equal to 500 cups and if it less than 500 it stores the revenue in memory. Excel will then choose another combination of quantities, checks the constraint, records the revenue in memory. This process is repeated until it finds a combination of quantities that is optimal. That is, there is no other combination of quantities that will result in a higher total revenue. This optimal combination of products is then reported in the cells of the worksheet.
Essentially what happens is a looping function combined with random sampling of numbers and an if function for the constraints. The trick is how to create the loop, generate the random numbers for each variables, store the calculated revenue in memory and have it only report back when all conditions are met (ie. there is no other combination of quantities will generate a higher total revenue given the constraint of 500 cups per week).
I hope this helps. Thank you for taking the time to think about this.
This can be scripted for a specific set of fields. The issue to be careful of is how generally applicable you need the final result to be. Referring to your "coffee" example, does the function need to allow for variable numbers of different coffee products or just three and only three types?
In general terms, I can picture a looping script that generates a series of possible solutions that compares results to gradually narrow possibilities down to a single optimum result, but the devil is truly in the details here.
I also pictured a looping script (3 nested loops actually) but couldn't get past a recurring thought...
While it's possible to drive a screw with a hammer, it isn't the right tool for the job.
I'm sure it's possible to rebuild a "solver" function within an FMP script...but I wonder if it is truly worth the effort. Use a tool that is designed to do what you're after, and store the result in FMP.
My two cents...hopefully of some value.