Hi All,
A little background to my problem.
In our system customers can sell products to multiple countries. I am trying to create a search which will group sales to each country that the customer sells to and deduct a certain amount based on a checkbox on the customer record.
Below shows how the checkbox is used to set the amount that should be deducted from sales (This is displayed as a results column):
Summary Type: Group - Formula (Numeric)
CASE WHEN {customer.custentity_is_discount_account} = 'T' THEN 0 ELSE 150 END
Quite straight forward, this displays the correct 0 and 150 values as expected.
I can correctly calculate the sales amount per country by creating the results column shown below:
Summary Type: Sum - Formula (Currency) -
CASE WHEN SUM({custcol_fee_amount}) > 150 THEN SUM({custcol_fee_amount}) - 150 ELSE SUM({custcol_fee_amount}) END
However i want to update this so that it takes the 'is discount account' checkbox value into account when performing the calculation. ie. If discount account then the value in the column should be Sum({custcol_fee_amount}) otheriwse the value in the column should be Sum({custcol_fee_amount})-150, I came up with the following shown below:
Summary Type - Sum - Formula Currency -
CASE WHEN Sum({custcol_fee_amount}) > 150 THEN CASE WHEN {customer.custentity_is_discount_account} = 'F' THEN Sum({custcol_fee_amount}) - 150 WHEN {customer.custentity_is_discount_account} = '' THEN Sum({custcol_fee_amount}) - 150 ELSE Sum({custcol_fee_amount}) END ELSE 0 END
However when i run this i get an error message:
An unexpected error has occurred. Please click here to notify support and provide your contact information.
Could someone give me some guidance on what is wrong with this search formula and why this error is occurring?
Any help you could provide would be great,
Thanks in advance,
Jordan