4 Replies Latest reply on Nov 11, 2009 8:55 AM by ninja

    Solving mathematical function - similar to excel solver



      Solving mathematical function - similar to excel solver


      I am currently working with Filemaker Pro v10 on a MacBook Pro.  


      I have a huge database containing customer information (approx 2500 records) and some of the data I currently analyse in MS Excel using the Solver function. In most cases the general problem is formulated as follows:


      1) maximise cell "profit"

      2) by changing the value "price"

      3) subject to a set of constraints (eg. minimum margin) 


      The Excel solver is works a treat but the continuous importing and exporting of data to perform the analysis is not only time consuming due to having to respecify the problem but is also subject to transposing errors.  


      Does anyone know how I can automate this process in Filemaker.  I have looked everywhere but have not been able to find anything useful.  I imagine that this can be done by writing a script but I have no idea how to go about formulating it.  Any help, ideas or suggestions are really very much appreciated


        • 1. Re: Solving mathematical function - similar to excel solver
             Can you post some examples of the Inputs and outputs for this function?
          • 2. Re: Solving mathematical function - similar to excel solver

            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


            subject to:

            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.



            • 3. Re: Solving mathematical function - similar to excel solver

              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.

              • 4. Re: Solving mathematical function - similar to excel solver



                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.