Hey all,

Thanks in advance for any help. I tried to explain this the best I can. I am trying to design a database for my company that will estimate prices. I am not new to FM but am to these more complex calculations. The pricing is based off a spreadsheet. Thickness on the "y" axis, Inches on the "x."

(y)Thickness is broken down per inch in 1/4" increments.

(x)Inches is cutting area of piece in inches.

(x)Inches also has four price steps. Under 2 lineal feet, 2-5 feet, 5-10 feet, over 10 feet.

(x) also another step for different amounts of pieces under the various feet requirements. 1-25,26-199,200or more.

Now let's say you have 5 pieces, 1" thick, 45" of cutting(3.75feet). You would go to row 1" then across to 2-5feet x 1-25pcs. The number looked up would need to multipled by 3.75.

What makes this seem impossible(at least to me) is that this table has 180 "fields" that are all conditional upon what is entered. This is why we want it automated.

I guess what I am really looking for is a way to have a result based off multiple tests. I've looked at "case," "evaluate," and "if." I'm starting to think it is some combonation of these but am very lost. Please Help!

I am not sure what you want to be calculated here. Is the spreadsheet using a formula to calculate the price from thickness and area, or are these arbitrary?

The spreadsheet is basically just a look up table. There are no calculations being done. The numbers are constant. What varies is the input; # of pieces, thickness, and area. Let me try another example.

Someone wants a price for 2 pieces, 1" thick x 5"wide x 5"long. This would give you an area of 20". You would go to the row with 1", then move across until you get the number under "2feet or less AND 1-25pieces." That number would be multiplied by 1.67(which is 20"/12). That would be the charge per piece.

This is the calculation I would like done. I have a field for # of pieces, thickness, and area. Now, based off those inputs, I would like FM to look up the value in the table and then return the correct result.

I hope this helps.

Edited August 26, 200520 yr by Guest

You could use the Lookup() function to do a conditional lookup. The relationship would be based on thickness, which would you give the correct row. Then the calculation could be something like:

Lookup (

Case (

Area < 2 and Qty < 26 ; PriceList::ColumnA ;

Area < 5 and Qty < 26 ; PriceList::ColumnB ;

Area < 10 and Qty < 26 ; PriceList::ColumnC ;

Area > 10 and Qty < 26 ; PriceList::ColumnD ;

Area < 2 and Qty < 200 ; PriceList::ColumnE ;

Area < 5 and Qty < 200 ; PriceList::ColumnF ;

Area < 10 and Qty < 200 ; PriceList::ColumnG ;

Area > 10 and Qty < 200 ; PriceList::ColumnH ;

...

)

)

However, a better solution would be to break up the price list so that each "cell" of your grid is a separate record. Then you could lookup the correct price directly based on a multi-criteria relationship.

If I understand correctly you want

Thichness in inches

Area in Square Inches

Lenght in feet

Units is a number.

Price is $/sq.ft.

I would make a lookup table with these fields: Thichness (in), Area (sqin), LengthMin (ft), LenghtMax (ft), CountMin, CountMax & Price ($/sqft)

In the main table, I would have the fields thichness, length, width, area = length * width, quantity, unitcost, Extendedcost. I would make a relatioship to the lookup table:

Thickness = Thickness

and

Length < LengthMax

and

Length >= LenghtMin

and

Quantity >= CountMin

and

Quantity <= CountMax

Use a large number for the Max where there is no number set.

A lookup based on this relationship should give you the unit price.

Thanks for the help. I'm going to try these out and see what works best. Will reply if I run into more problems.