I have a Formula (Currency) field with Summary Type of Maximum in a transaction saved search. This works: MAX({custbody_test}/{exchangerate})-SUM(NVL(CASE WHEN {applyingtransaction.type} = ‘Customer Deposit’ THEN {applyingtransaction.fxamount} ELSE 0 END,0)) but when I added a CASE statement, it throws an error: CASE WHEN {type} = ‘Sales Order’ THEN (MAX({custbody_test}/{exchangerate})-SUM(NVL(CASE WHEN {applyingtransaction.type} = ‘Customer Deposit’ THEN {applyingtransaction.fxamount} ELSE 0 END,0))) ELSE 0 END Does anybody know what is wrong with the second formula?

It seems like the error might be caused by the use of the MAX function within the CASE statement. The MAX function is an aggregation function that can only be used outside of a CASE statement in a summary type formula.

Sorry here’s my actual formula: CASE WHEN {type} = ‘Sales Order’ THEN MAX({custbody_test}/{exchangerate})-SUM(NVL(CASE WHEN {applyingtransaction.type} = ‘Customer Deposit’ THEN {applyingtransaction.fxamount} ELSE 0 END,0)) WHEN {type} = ‘Invoice’ THEN {fxamount} ELSE {fxamountremaining} END I tried what you did but still didn’t work. Thanks though

Since you only added the outer Case, and only three new fields, make sure they are returning the values expected. I’d try these five additional formulas and see what the results are: •

{type}

•

{fxamount}

•

{fxamountremaining}

•

CASE WHEN {type} = 'Invoice' THEN {fxamount} ELSE {fxamountremaining} END

•

CASE WHEN {type} = 'Sales Order' THEN MAX({custbody_test}/{exchangerate})-SUM(NVL(CASE WHEN {applyingtransaction.type} = 'Customer Deposit' THEN {applyingtransaction.fxamount} ELSE 0 END,0)) ELSE 99999999 END