Case Function Problems

[DesignX]

I have a problem with my case function

Case(Quantity >= 1 and Colors="1 Color";"2.70";

Quantity > 6 and Colors="1 Color";"2.55";

Quantity > 20 and Colors="1 Color";"1.50";"")

Example:

I input the number 12 into my quantity field and pick 1 color from my drop down list and i get a value in my unit price field of 2.70. Now as per my case function i should have the number 2.55 in the unit price field instead since 12 is greater than 6. and if i go and change the quantity the same value stays in the unit price field.

Can someone help. Im not sure if i should use a less than sign instead of greater than but i dunno any helpp will be appreciated.

[LaRetta]

Your Case() function stops evaluating when it hits the first true (in 7/8 as you've indicated for this post). So ALL results will pass the first test of >=1 and produce 2.70. Reverse it and it will work (untested)

Case ( Colors = "1 Color" ;

Case (

Quantity > 20 ; 1.5 ;

Quantity > 6 ; 2.55 ;

2.7 )

)

Since each test requires the "1 Color" portion, you can include it only once - it saves evaluations.

[DesignX]

Thank you i got it to work now but now the only problem is i need to make more cases for 5 more colors.

i.e. Color 2 - Color 6 but i cant seem to do it. Would an array work? if so how the heck would i pull it off.

[LaRetta]

Well, I don't like hard-coding values (of colors or anything else) within calculations. You might really wish to consider a table to hold the color and values. But here's how it would work ... building off the original calculation, the new portion in green:

Case ( Colors = "1 Color" ;

Case (

Quantity > 20 ; 1.5 ;

Quantity > 6 ; 2.55 ;

2.7 )

[color:green];

Colors = "2 Color" ;

Case (

Quantity > 20 ; 3.5 ;

Quantity > 6 ; 5.55 ;

9.7 )

;

[color:black]... insert the other colors here then last color would be:

Colors = "6 Color" ;

Case (

Quantity > 20 ; 5.5 ;

Quantity > 6 ; 7.55 ;

11.7 )

) < ---- Notice the ending 'original' Case() parenthesis here.

You will have (right now) 18 choices and what if they decide to add another price break? I can guarantee there will be changes. By using a table, you will also provide Management with the means to changes the prices - they will change data in the records. As it is, they will require a Developer to open your field definitions.

[DesignX]

This actaully has nothing to do with Colors but the amount of colors. I am setting this up for a screenprinting business so 1 Color or 2 Color is the amount of colors used per shirt. and yes i have alot of different values that i gotta input so im not sure another table would actually help me.

what i was trying to achieve is when I fill out a new record and i put a number in the quantity field and i choose one of the six choices (1 Color - 6 Color) my unit price will change varying on what is in the quantity box. thats why i set it up with a case function cause i figured that the function would just go to the quantity field and check the number then move on to the color field and check to see what choice it is and input the appropriate hard coded value into the unit price field.

Not sure if that clears anything up and if you could suggest maybe an easier way to achieve this i am fully open to any suggestion.

Thank you as always

[David Jondreau]

I think you should start by changing the field definition for NumColors from text to number. The user would select just a number (1,2,3,4,5,6) instead of "1 Color", "2 Colors" etc.

But even without that, LaRetta's got it correct. You have 18 different prices, depending on the quantity and number of colors, you need 18 different conditions in your Case statement.

Do consider the table idea. You may not be tracking individual colors, but that's irrelevant. You still have 18 different prices that may change. Your table would have a number field NumColors, a number field Quantity, and a number field Price. You'd have 18 records in this table (with your current price structure). The Case calculation you're currently working on would instead be a simple lookup (or calculation) that would draw from this table based on NumColors and Quantity

This way if someone needed to change a price or add a price level (>50?), they wouldn't have to know much about Filemaker, they'd just change it in the table or add a new record, rather than delve into field definitions and parse a calculation.

G