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

# compare and get the information

### Title

compare and get the information

### Post

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

• ###### 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

• ###### 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.