4 Replies Latest reply on May 18, 2011 8:39 AM by philmodjunk

    compare and get the information



      compare and get the information


      Hi , i new to this forum, and i having a little problem with making compare's in a database

      i explain.

      I have a database where i set  a price of a part  this part cost 130,- now i have to set margin of this part price

      the compare data base shut be  if price is under € 100,- the factor is 2 if the price is above 100 the factor is 1,5  above 200 1.3 above 500  1.2

      but how i do this in filemaker .i hope some one can help me with this .

      i know this in my head but i just can get in outh Laughing

        • 1. Re: compare and get the information

          You could add a calculation field called Markup with the following function:

          Case( Price<100; 2; Price≥100 and Price<200; 1.5; Price≥200 and Price<500;1.3; Price≥500;1.2)

          Then you could add a SalesPrice Field with the calculation: Price*(Markup).

          • 2. Re: compare and get the information

            "if price is under € 100,- the factor is 2 if the price is above 100 the factor"

            You have skipped the possibility if the price is exactly 100; should it be 1.5 or 2?

            Anyway, here is another way which combines the cost and the markup calculation and just produces one calculation for the Selling Price.  Note that, since most calculations short-circuit (stop evaluating when they hit the first true), there is need only to specify one side of the calculation because it eliminates as it goes and the final default value covers everything from 500 and up.:

            cost *
            Case (
            cost < 100 ; 2 ;
            cost < 200 ; 1.5 ;
            cost < 500 ; 1.3 ;
            1.2 )

            • 3. Re: compare and get the information

              Thx a lot it works fine Thx Laughing

              • 4. Re: compare and get the information

                There are also ways to enhance this method so that you aren't "hardwired" into these specific price points and mark up percentages. If you use a looked up value approach, you can change both the price points and Markup percentages in the future just by editing the look up table instead of having to modify the calculation definition.

                Build a MarkUp table with these values:

                Price     Pct
                0            2
                100      1.5
                200      1.3
                500      1.2

                Define a relationship:

                LineItems::Price > MarkUp::Price

                Sort the relationship by MarkUp::Price in descending order

                Then you can define a number field in LineItems, MarkUpPct that uses a looked up value setting to copy the Pct value from the MarkUp table to use in your calculation.