Jump to content

Hope I can explain this right


This topic is 6674 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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


Length < LengthMax


Length >= LenghtMin


Quantity >= CountMin


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.

Link to comment
Share on other sites

This topic is 6674 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.