2 Replies Latest reply on Dec 29, 2011 3:04 PM by minitprint

    Calculate price with variable pricing

    minitprint

      I'll start off with the standard disclaimer... I am a FM n00b.

       

      I am building a job ticketing/invocing solution for my print shop. There are many variables that go into pricing a print job. My issue is how do I get FM to calculate the price based upon job quanity and with price breaks at certain quantities?

       

      For example:

       

      Job "A" cost $100 if printing 1000 pieces.

      Job "B" cost $68 if printing 500 pieces.

      Job "C" cost $49 if printing 250 pieces.

       

      The quantities for price breaks are at 250, 500, and 1000. There is also typically a break for each additional 1000. The price break is different for different types of print jobs.

       

      This is not very hard if people always ordered in quantities of 250, 500, 1000. The hard part is figuring out how to calculate 750 pieces.

       

      The logic I'm looking at using is

       

      Case (

      Qty > 1000; Price of 1000 + (((Qty-1000)/1000)*Price of additional 1000);

      Qty = 1000; Price of 1000;

      Qty >= 500 and Qty < 1000; Price of 500 + (((Qty-500)/1000)*Price of1000);

      Qty >=250 and Qty < 500; Price of 250 + (((Qty-250)/1000)*Price of 500);

      Qty < 250 = Qty * (Price of 250/250);

      )

       

      I want to setup a product table that can be used in the job ticket to select the print job(via drop down list). However I do not want to clutter the product listing with multiple listing of the same job (different prices).

      I also want to use the product listing for invoices.

       

      So my questions are:

      1. Does my logic look right?
      2. Is it better to setup a seperate table for prices that is related to the product table or is it easy to filter the products that are shown in the job ticket.

       

      Last bit of info is that we don't want everyone to be able to access the pricing, so I want to keep it out of the job ticket and only in the invoices.

       

      Thanks!

        • 1. Re: Calculate price with variable pricing
          ariley

          Hi Minitprint, 

           

          Whenever something looks insanely complicated to me, I step back and see if there's an easier way to accomplish my crazy idea. And, usually there is.

           

          In FileMaker we have tables. If you can add another table that contains your quantity breakdown, you can make your life a lot easier.

           

          You should have 3 fields in your new table (aside from the primary and foreign keys):

           

          JOB-TYPE

          QUANTITY

          PRICE

           

           

          then you can connect your new table to your job records by job-type and quantity and get your price that way.

           

          Looks like you can have a million variants roughly, and you'll grow long grey hair before you simplify your calculation.

           

           

          Which, by the way, could start with this:

           

          Use the Let function to define your variables (the things you are calculating based on):

           

          Let (

          [

          job_type = (job_type field from your DB); 

          quantity =  (quantity field from your DB)

          ];

          Case ( 

          job-type is "business cards" and quantity is 250 ; 

              0.5; 

          job-type is "business cards" and quantity is 500

              0.25;

           

          "")

          )

           

           

          Hope this helps.

           

          Regards,

           

          Agnes B. Riley

          FileMaker 10 and 11 Certified Developer

          http://www.zerobluetech.com

          ZeroBlue Technology Solutions

          where your data goes to work

           

          Member, FileMaker Business Alliance

          Member, FileMaker Technical Network

           

          FileMaker Advanced 11.3 on Mac OS X 10.6.8

           

          "Imagination is more important than knowledge..."

             /Albert Einstein/

          • 2. Re: Calculate price with variable pricing
            minitprint

            Stepping back is great advice! Speaking of which I'm about to head out for the day. Thanks for the quick reply. I will look further at your solution tomorrow.