Gerhardpet
asked on
Need help with a crystal formula
I have this formula that only works with the first line. When adding the second line I get an error
This works with no errors
When adding the second line I get (see image below):
This is the final formula that I need
This works with no errors
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01}
When adding the second line I get (see image below):
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE02}
This is the final formula that I need
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE02} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE03} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE04} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE05} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE06} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE07} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE08} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09}
Personally, when I have a finite list of possible combinations, I like to use a SWITCH function, which is the functional equivalent of an IF-THEN-ELSE that has no default "ELSE" value at the end. Also, if I'm going to have one value repeated constantly, I like to replace it with a variable to make things easier to read. Here's an example:
LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};
SWITCH
(
QTY <= {PRICING.BVBREAKQTY01},{PRICING.BVBREAKPRICE01},
QTY <= {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE02},
QTY <= {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE03},
QTY <= {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE04},
QTY <= {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE05},
QTY <= {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE06},
QTY <= {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE07},
QTY <= {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE08},
QTY <= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
)
ASKER
Ok changing to else works but my formula does not work.
@ rhinok, I like your idea better but I have a hard time figuring out how to use the <= and >=
Let's assume my QTY = 25. In this case my cost price should be $250
Here is my table for the BVBREAKQTY (Quantity) and BVBREAKPRICE (Cost price)
How would I phrase my formula?
@ rhinok, I like your idea better but I have a hard time figuring out how to use the <= and >=
Let's assume my QTY = 25. In this case my cost price should be $250
Here is my table for the BVBREAKQTY (Quantity) and BVBREAKPRICE (Cost price)
How would I phrase my formula?
Try
mlmcc
LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};
SWITCH
(
QTY <= {PRICING.BVBREAKQTY01},{PRICING.BVBREAKPRICE01},
QTY > {PRICING.BVBREAKQTY01} AND QTY <= {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE02},
QTY > {PRICING.BVBREAKQTY02} AND QTY <= {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE03},
QTY > {PRICING.BVBREAKQTY03} AND QTY <= {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE04},
QTY > {PRICING.BVBREAKQTY04} AND QTY <= {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE05},
QTY > {PRICING.BVBREAKQTY05} AND QTY <= {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE06},
QTY > {PRICING.BVBREAKQTY06} AND QTY <= {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE07},
QTY > {PRICING.BVBREAKQTY07} AND QTY <= {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE08},
QTY > {PRICING.BVBREAKQTY08} AND QTY <= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
)
mlmcc
ASKER
It is very close to working
When QTY = 42 I get 180 but it should be 190
When I have QTY >= 51 then I get 0.00 but it should be 170
When QTY = 42 I get 180 but it should be 190
When I have QTY >= 51 then I get 0.00 but it should be 170
Based on your example, try this:
LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};
SWITCH
(
QTY < {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE01},
QTY >= {PRICING.BVBREAKQTY02} AND QTY < {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE02},
QTY >= {PRICING.BVBREAKQTY03} AND QTY < {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE03},
QTY >= {PRICING.BVBREAKQTY04} AND QTY < {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE04},
QTY >= {PRICING.BVBREAKQTY05} AND QTY < {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE05},
QTY >= {PRICING.BVBREAKQTY06} AND QTY < {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE06},
QTY >= {PRICING.BVBREAKQTY07} AND QTY < {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE07},
QTY >= {PRICING.BVBREAKQTY08} AND QTY < {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE08},
QTY >= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
)
Just try this:
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE09} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE08} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQ TY} > {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE02} else
{PRICING.BVBREAKPRICE01}
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
{PRICING.BVBREAKPRICE01}
From the OP:
In order to return Break Price 7, based on the scenario provided by the OP, the Order Qty needs to be >= Break Qty 7 and < Break Qty 8.
When QTY = 42 I get 180 but it should be 190
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE08} else
Using the formula above, an Order Qty of 42 would still result in 180 when it should be 190. In order to return Break Price 7, based on the scenario provided by the OP, the Order Qty needs to be >= Break Qty 7 and < Break Qty 8.
ASKER
@13Shadow
I get the same results with your formula except for >50 does works.
I'm confused...I know enough about writing formulas to get myself in trouble, which is the case here.
@ rhinok I'll appreciate if you could help with the finish formula.
I get the same results with your formula except for >50 does works.
I'm confused...I know enough about writing formulas to get myself in trouble, which is the case here.
@ rhinok I'll appreciate if you could help with the finish formula.
Oh yeah I didn't see that comment.
The original post was asking for a solution to the formula failing with errors.
My solution was a valid one correcting the syntax error due to the invalid use of and.
The original post was asking for a solution to the formula failing with errors.
My solution was a valid one correcting the syntax error due to the invalid use of and.
Try this:
if {SALES_ORDER_DETAIL.BVORDQ TY} >= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE01} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE02} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQ TY} < {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE08}
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
My last code sample accounts for your scenario. 13Shadow's last formula should work, too. It basically does the same thing as mine, except it relies on implicit predicate order instead of explicitly declaring ranges.
FYI - it doesn't really matter if you use the IF-THEN-ELSE or the SWITCH (or even a SELECT) in this case. They'll all accomplish the same thing. I personally just prefer the "cleanliness" of one vs. the other.
FYI - it doesn't really matter if you use the IF-THEN-ELSE or the SWITCH (or even a SELECT) in this case. They'll all accomplish the same thing. I personally just prefer the "cleanliness" of one vs. the other.
ASKER
So, in most cases, there are only three breaks? Is the maximum number of breaks always going to be nine or can it be even more? In the example above, you would want to show $256 as the price then the ordered QTY was >= 25, right? In your example above, you explicitly list "0" as values for unpopulated breaks. Will that always be the case or will those fields be NULL?
Assuming there is a maximum of nine possible breaks AND "unused" breaks are populated with zeroes, try the following:
Assuming there is a maximum of nine possible breaks AND "unused" breaks are populated with zeroes, try the following:
//The following variable is to evaluate and assign the order quantity
LOCAL NUMBERVAR OQTY := {SALES_ORDER_DETAIL.BVORDQTY};
//The following variables are to evaluate and assign the various possible break quantities
LOCAL NUMBERVAR BQTY2:= {PRICING.BVBREAKQTY02};
LOCAL NUMBERVAR BQTY3:= {PRICING.BVBREAKQTY03};
LOCAL NUMBERVAR BQTY4:= {PRICING.BVBREAKQTY04};
LOCAL NUMBERVAR BQTY5:= {PRICING.BVBREAKQTY05};
LOCAL NUMBERVAR BQTY6:= {PRICING.BVBREAKQTY06};
LOCAL NUMBERVAR BQTY7:= {PRICING.BVBREAKQTY07};
LOCAL NUMBERVAR BQTY8:= {PRICING.BVBREAKQTY08};
LOCAL NUMBERVAR BQTY9:= {PRICING.BVBREAKQTY09};
SWITCH
(
OQTY < BQTY2 OR BQTY2 = 0,{PRICING.BVBREAKPRICE01},
OQTY >= BQTY2 AND (OQTY < BQTY3 OR BQTY3 = 0),{PRICING.BVBREAKPRICE02},
OQTY >= BQTY3 AND (OQTY < BQTY4 OR BQTY4 = 0),{PRICING.BVBREAKPRICE03},
OQTY >= BQTY4 AND (OQTY < BQTY5 OR BQTY5 = 0),{PRICING.BVBREAKPRICE04},
OQTY >= BQTY5 AND (OQTY < BQTY6 OR BQTY6 = 0),{PRICING.BVBREAKPRICE05},
OQTY >= BQTY6 AND (OQTY < BQTY7 OR BQTY7 = 0),{PRICING.BVBREAKPRICE06},
OQTY >= BQTY7 AND (OQTY < BQTY8 OR BQTY8 = 0),{PRICING.BVBREAKPRICE07},
OQTY >= BQTY8 AND (OQTY < BQTY9 OR BQTY9 = 0),{PRICING.BVBREAKPRICE08},
OQTY >= BQTY9,{PRICING.BVBREAKPRICE09}
)
You don't necessarily need all the variables, but it makes the code a littler easier to read, since you won't have the field names replicated so many times.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@rhinok
You are right both yours and @13Shadow are working.
Thank you to both of you for your help.
To do these formulas is way beyond me...
Best for the new year!
You are right both yours and @13Shadow are working.
Thank you to both of you for your help.
To do these formulas is way beyond me...
Best for the new year!
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ
if {SALES_ORDER_DETAIL.BVORDQ