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
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}
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.
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 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}
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
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…