Monster Calculation (for me)

[Baylah]

Hi,

I have a calculation that I can put together by combining several fields but I know there has to be a better way. I am trying to learn to be a better developer so I thought I would ask the group for help as I cannot find anything that specifically addresses this type of calculation.

I am working on a DB for a Wholesale Contract Embroidery company. They charge for their services by the number of stitches that are in a pattern combined with a sliding base price determined by the size of the order. A snapshot of their price list is below.

The Base price is for the first 3,000 stitches in a given embroidery pattern.

# of Garments...Base Price...per Extra 1k Stitches

144-287...........$1.95..........$0.30

288-576...........$1.80..........$0.28

577-999...........$1.55..........$0.27

If a customer submits 500 garments for embroidery with a pattern that has 5,399 stitches the math would look something like this:

Base Price.................$00001.80 (for 1st 3k sts)

2,399 extra stitches....$00000.67...(2,399 * .028)

Price Per Garment.......$00002.47...(1.80 + .67)

Total for Order Line.....$1,235.00...(2.47*500)

Is it possible to combine all of this information into one calculation? The fields I currently have defined in the DB are:

Qty: Total Garment Quantity

EmbBase (Currently a calculation based on a case statement of QTY)

StitchBase (Currently a calculation based on a case statement of QTY)

Stitches = Actual stitch count in design.

StitchAdjust (Stitches - 3,000)

StitchPrice (StitchAdjust*StitchBase)*.0001

TotDesignPrice (SttichPrice+EmbBase)

LineTotal (TotDesignPrice*qty

If possible I really want the user to only enter the QTY and let the calculation do all of the math in the background. The Stitch Count is auto entered through a LookUp relationship when the user enters the design number used.

I knew going in that this was going to be the most difficult part of this project for me. If anyone can help out I would really appreciate it and promise to pay it forward whenever I can.

Thanks,

Steve

Edited April 1, 2007 by Guest

[Raybaudi]

Hi

try this calc:

Case(

IsEmpty ( qty ); "";

qty ≤ 3000 ; Base Price * # of Garments ;

(Round ( (qty - 3000) * Extra / 1000 ; 2 ) + Base Price ) * # of Garments

)

[comment]

Hm. Didn't we go through this over two years ago ?

[Baylah]

You have an amazing memory!

I was never able to get it to work the way I wanted from that thread of posts. But I have kept trying different things.

Attached is what I have, and it works, I just thought I could do better. I am trying to learn to think about better ways to think about the logic in these types of problems and then apply it.

Comment: In your original post you addressed what is my main concern with the solution attached. You need a developer to change the prices. Even though it is pretty easy it is cryptic for anyone who doesn't work in FMP. I am going to revisit your solution to see if I can get it to work right.

Thanks for all of your help...As usual.

Steve

PS You'll notice I changed my Skill Level...it is no longer Advanced. I have so much to learn.

StitchCalculator1.zip

Edited April 2, 2007 by Guest

[comment]

Here's a sketch of the method I suggested then. It seems some things have changed since then, but the basics are the same.

Stitches.fp7.zip