Crystal report formula help

Crystal report writer v11

I need to edit the below formula and add a bit more criteria, any help would be much appreciated.

Current formula:
IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’
THEN ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2) ELSE {OrderHed.TotalTax}

Criteria to add:
But if the Bill to address (as each line above) does contain “United Kingdom” AND the orderreltax.percent is 0.00 only apply the 20% to the orderhed.totalmisc

Thanks.
Mark

I wrote and tried this but it only seemed to look at the below part of the formula then…so its not right.

IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’
THEN ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2) ELSE {OrderHed.TotalTax};

IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’ AND {Orderreltax.percent} = 0.00
THEN {OrderHed.TotalMisc} *0.2 ELSE {OrderHed.TotalTax}

How do you feel about using InStr to find ‘United Kingdom’ or ‘~United Kingdom’?

As long as the formula works, i don’t mind!

Been awhile since I used Crystal, I think you need to move the second
criteria check, {Orderreltax.percent} = 0.00, to a nested IF after the Then
statement.

IF
SPLIT …
Then
IF {Orderreltax.percent} = 0.00
Then
{OrderHed.TotalMisc} *0.2 ELSE {OrderHed.TotalTax}

That way any of the first IF that are true would flow into the second check
on tax percent.

It should work, basically checking the initial string to see if it contains that substring, rather than breaking it all out and doing this whole logic structure.

HI Guys, sorry im not the best at crystal syntax…do you mean like this Randy?

IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’
THEN IF {Orderreltax.percent} = 0.00 THEN {OrderHed.TotalMisc} *0.2 ELSE {OrderHed.TotalTax} ELSE
((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2) ELSE {OrderHed.TotalTax}

Yes that’s what I was meaning. Did it work for you? Though, I think Jim’s
suggestion is probably cleaner like you, if it works and doesn’t impact
performance too much I’d leave it alone.

i’ll try it now and let you know.

You only have two if’s so you can only have two else’s.

Try using a staggered format, like this:

IF…
THEN
__IF
__THEN
__ELSE
ELSE

You might have to use parentheses or something to get the order of operations right with this:
IF THEN (IF THEN ELSE) ELSE

my brain is fried, i could do this easy in excel lol!

i’ll give this one a go:

IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’
THEN ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2)
IF {Orderreltax.percent} = 0.00
THEN {OrderHed.TotalMisc} *0.2
ELSE {OrderHed.TotalTax}

i tried it with parenthesis before the 2nd IF and at the end of the formula but still no joy

Remove “(((OrderHed.TotalCharges…” before the send IF statement.

Would you need an “and” at the end of your first THEN statement?

I was curious about the InStr function and ran this in CR. Also, you have OrderRelTax and my table has OrdRelTax

IF InStr({OrderHed.Calc_BillToAddressList},‘United Kingdom’) > 0
THEN IF {OrdRelTax.Percent} = 0
THEN ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2)
ELSE {OrderHed.TotalTax}

Hi Miguel,

Your formula returned no errors in the syntax but didn’t give the desired results…i’m just going to go back over the criteria needed as this thread has got quite large !

If the address contains “united kingdom” THEN ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})*0.2)
BUT if the address contains “united kingdom” AND the orderreltax.percent is 0.00 THEN only multiply the OrderHed.TotalMisc *0.2
ELSE {OrderHed.TotalTax}

Another way of writing this that maybe simpler if somebody could help me with the syntax is:

If the orderreltax = 0.00 AND the address contains “united kingdom” THEN multiply the OrderHed.TotalMisc *0.2
ELSE {OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc} * orderreltax

Thank you all for your patience on this one.

I’ve just written the below but for a UK customer with a orderreltax percent of 20% it only calculated the tax on the misc charge…not the rest so i think my formula is conflicting

IF SPLIT({OrderHed.Calc_BillToAddressList},’~’)[1] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[2] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[3] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[4] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[5] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[6] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[7] = ‘United Kingdom’ OR
SPLIT({OrderHed.Calc_BillToAddressList},’~’)[8] = ‘United Kingdom’
AND {OrderRelTax.Percent} = 0.00
THEN {OrderHed.TotalMisc} 0.2
ELSE ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})
{OrderRelTax.Percent})

IF InStr({OrderHed.Calc_BillToAddressList},‘United Kingdom’) > 0
AND {OrderRelTax.Percent} = 0.00
THEN {OrderHed.TotalMisc} 0.2
ELSE ((({OrderHed.TotalCharges}-{OrderHed.TotalDiscount})+{OrderHed.TotalMisc})
{OrderRelTax.Percent})/100

The above seems to be working, i’ll do some thorough testing and report back…