# Crystal report formula help
**Category:** [Epicor ERP 9](https://www.epiusers.help/c/e9/7)
**Created:** 2017-01-05 13:56 UTC
**Views:** 2230
**Replies:** 21
**URL:** https://www.epiusers.help/t/crystal-report-formula-help/36791
---
## Post #1 by @Rowley150
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
---
## Post #2 by @Rowley150
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}
---
## Post #3 by @Jim_Lutz
How do you feel about using InStr to find 'United Kingdom' or '~United Kingdom'?
---
## Post #4 by @Rowley150
As long as the formula works, i don't mind!
---
## Post #5 by @Randy
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.
---
## Post #6 by @Jim_Lutz
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.
---
## Post #7 by @Rowley150
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}
---
## Post #8 by @Randy
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.
---
## Post #9 by @Rowley150
i'll try it now and let you know.
---
## Post #10 by @Rowley150
---
## Post #11 by @Jim_Lutz
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
---
## Post #12 by @Rowley150
my brain is fried, i could do this easy in excel lol!
---
## Post #13 by @Rowley150
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}
---
## Post #14 by @Rowley150
i tried it with parenthesis before the 2nd IF and at the end of the formula but still no joy
---
## Post #15 by @Randy
Remove "(((OrderHed.TotalCharges...." before the send IF statement.
---
## Post #16 by @ajones
Would you need an "and" at the end of your first THEN statement?
---
## Post #17 by @MiguelS
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}
---
## Post #18 by @Rowley150
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.
---
## Post #19 by @Rowley150
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})
---
## Post #20 by @Rowley150
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.........
---
## Post #21 by @Hally
My 2c here, but I would be a bid hesitant hard coding a tax value into a report.
Not sure if it is possible, but perhaps joining in UseCodes in the RDD and using a criteria, perhaps the report title to make sure you can pull the defined percentage value from it. That would allow you to alter any of that type of information from within E9.
Cheers
Simon
---
## Post #22 by @Rowley150
i completely agree with you.
Your right, its best practise to do it your way.
I'll see how i can change the * 0.2 element.
---
**Canonical:** https://www.epiusers.help/t/crystal-report-formula-help/36791
**Original content:** https://www.epiusers.help/t/crystal-report-formula-help/36791