Jump to content

Case Function Problems


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

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5792 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
 Share

×
×
  • Create New...

Important Information

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