Case Formula

[diggnified]

I was hoping to get some help on a quoting system I am working on. This is a quoting system for a granite company. In the granite industry the customer is charged based off the height and on certain granite pieces and sometimes it is based off the width and in either case the Sq Foot rate. For example a piece of granite that is:

L W H

30” x 6” x 20”

So how we figure it is Length x Height / 144 which gives you the Sq Foot of that piece. So using the above size the Sq Foot would be 5 Sq Foot. Now I have to add a one time setup rate and Sq Foot Rate based off the height. Here is my range.

Height Setup Rate Per Sq. Ft Rate

1–10” $160.00 $32.00

11–14” $175.00 $42.00

15-20” $185.00 $48.00

21-24” $195.00 $48.00

25”+ $195.00 $42.00

So using the example the formula would be $185 + $48.00 x 5.00 = $425.00

I got all of that working just fine using this Case Formula:

Case (CarveTrigger1 = "Carve" and size1height <=10 ; 160 + 32*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1height <=14 ; 175 + 42*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1height <=20 ; 185 + 48*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1height <=24 ; 195 + 48*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1height >25 ; 195 + 42*SqFt1)

Now here is where I need help. In some cases depending on the size of the granite we would base the carve charge off the Width of the granite not height so it then becomes:

Length x Width / 144 = SQ Foot

So now I was wondering if I can put a button on the layout that when clicked will change the Sq Foot field to now calculate Length x Width = Sq Foot. And also change the Case Formula above to:

Case (CarveTrigger1 = "Carve" and size1width <=10 ; 160 + 32*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1width <=14 ; 175 + 42*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1width <=20 ; 185 + 48*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1width <=24 ; 195 + 48*SqFt1)

&

Case (CarveTrigger1 = "Carve" and size1width >25 ; 195 + 42*SqFt1)

So the button would actually trigger 2 events. 1. Change how the Sq Foot field calculates and 2. change the case formula to figure based off width not height.

I would greatly appreciate any help anybody can give. Thanks in advance.

Dave

I have attached my solution in the event you need to see how I have things configured.

Attachment

[comment]

A single Case() function can have multiple pairs of test-result. The result of the first test that evaluates as true is the result returned.

Try something along the lines of:

Let ( [

dimension = Case ( CarveTrigger = "Carve" ; Height ; Width ) ;

area = Length * dimension / 144

] ;

Case (

dimension ≤ 10 ; 160 + 32 * area ;

dimension ≤ 14 ; 175 + 42 * area ;

dimension ≤ 20 ; 185 + 48 * area ;

dimension ≤ 24 ; 195 + 48 * area ;

195 + 42 * area

)

)

[diggnified]

comment thanks for your help I will give this a try. Would I then asign this to a button so when clicked it would know to figure it by width and not height? Thank you.

Edited August 4, 2006 by Guest

[comment]

No, this is a calculation formula designed to replace what you have posted (but note that I haven't used your exact field names).

[diggnified]

comment, thank you very very much that works brilliantly. I do not think I ever would have figured it out. It makes sense now but I was shaking my head on this one. Thanks again I appreciate it.

[diggnified]

comment I think I jumped the gun a little bit. It appeared to be working. Right now it seems to be getting the setup fee ranges correctly but it will not take sq foot rate * the sq foot. Example when i enter a size of 36 x 06 x 20 it gets that its 20" high and fills the carve1price field with $185.00 but then does not add the $48 x Sq Ft. Here is the forumula I have now:

Let ( [

dimension = Case ( CarveTrigger1 = "Carve" ; size1height ; size1width ) ;

SqFt1 = size2length * size1height / 144

] ;

Case (

dimension ≤ 10 ; 160 + 32 * SqFt1 ;

dimension ≤ 14 ; 175 + 42 * SqFt1 ;

dimension ≤ 20 ; 185 + 48 * SqFt1 ;

dimension ≤ 24 ; 195 + 48 * SqFt1 ;

195 + 42 * SqFt1

)

)

Also can you tell me what the "dimension" references are to be replaced with? Thank you.

Edited August 4, 2006 by Guest

[comment]

I am not sure I follow this entirely, but I do see one thing:

First, we define 'dimension' as either height or width, depending on the test (CarveTrigger). Then, we use 'dimension' in TWO places: first to compute the square footage (which I called 'area' in the first calc), then to compute the price. You do not use 'dimension' in your square footage calculation, so your square footage is ALWAYS based on height, regardless of what CarveTrigger happens to be.

BTW, is there a good reason why your lines are not in a portal as individual records, instead of this field1, field2... business?

[diggnified]

As you can see I am a beginner and I was not sure how to use portals exactly. I have used them in another solution I worked on but in this solution I did not even think about it.

Since your last post I have decided to make some changes. I ended up putting in a checkbox called switchtrigger1 which is a checkbox with a static value of switch. I am now trying to use that in my formula so that if the switchtrigger1 is checked it will instruct the sq foot field to calculate based off of length * width /144 and if unchecked to calculate length * height /144.

I cannot seem to get anywhere with it. Could I possibly send this to you just for you to look at and see what I am doing wrong here. I am not expecting you to do it only to look at it. Thanks again for your help.

[diggnified]

comment I forgot to mention in my previous posts. In the granite industry there are 3 memorials types and there are 2 ways to get sq foot.

1. Monuments (example) 36 x 6 x 20 which would be calculated as Length * Height /144 = Sq Foot

2. And there is grass markers and bevel markers which are for example 24 x 12 x 4. In that case the sq foot has to be figured as Length * Width /144. As you can see if we figured the sq foot based off 24 * 4 /144 it would not give us the proper sq foot. We would lose money based off 4" so we then switch the calcuation to 24 x 12 /144. So in those cases where the height is smaller then the width we will switch them. Sorry I forgot to mention that before.

Thanks

Quote_Manager.zip

Edited August 5, 2006 by Guest
Added attachment

[comment]

Questions:

1. If I understand correctly, the correct square footage could be computed automatically by taking the larger dimension of the two (length or height). No manual switching would be required?

2. What does the CarveTrigger field do?

[diggnified]

1. If I understand correctly, the correct square footage could be computed automatically by taking the larger dimension of the two (length or height). No manual switching would be required? I actually thought of that but there are some rare cases where that rule is not followed so we thought this would be better controlled by a checkbox.

2. What does the CarveTrigger field do? The carve trigger is to turn off the engraving charge. We do have some customers that buy the memorials but do not want any carving or engraving done on the memorial. So with CarveTrigger I made it a checkbox to have that switch that charge on or off.

Thanks again for taking your time to help.

[comment]

Still, you could make it automatic by default, with the option to override in those rare cases. Take a look at the attached rough sketch.

granite.fp7.zip

[diggnified]

comment thanks for taking the time to create this. This is exactly what I need it to do. Now I understand what you mean by using the portal. It makes the database much more cleanier and easier to navigate the backend. Im definetly going to be making some chnages to mine. Thanks again and very well done.

[comment]

Good. Your next step should be to take the actual prices out of the calculations and move them to a Prices table. Otherwise every time you change your prices, you will need to adjust the calculations, and what's worse - your older quotes will be modified. Use lookup to get the relevant prices from Prices into a Quote.

[diggnified]

comment thank you for bringing to my attention. When I first started writing down my main goal for this project I knew I would need to impliment that but was not really sure where to begin. So basically anywhere I have a cost being calulated I should put it in a prices table and then do the lookup. I think I can handle most of that but I am not sure how to handle the Carve range calculation. I created a new table called CarveFormula and to handle the range I created 20 fields in that table along the lines of

(level1min) 1" (Level1max) 10" (Level1factor) 32.00 (Level1SetupRate) 160.00

(level2min) 11" (Level2max) 14" (Level2factor) 42.00 (Level2SetupRate) 175.00

And so on to handle this case calc you helped me with me

Case (

dimension ≤ 10 ; 160 + 32 * SqFt1 ;

dimension ≤ 14 ; 175 + 42 * SqFt1 ;

dimension ≤ 20 ; 185 + 48 * SqFt1 ;

dimension ≤ 24 ; 195 + 48 * SqFt1 ;

195 + 42 * SqFt1

Not sure if this is the best way to handle this or not but it was the only thing I can think to do to store each range. Is a lookup possible doing it this way?

Thanks

[comment]

I think I would do something like the attached. Note the "copy next higher" in the lookup definition.

granite2.fp7.zip