RE: Saved search invalid expression error
I am trying to sum and substract the balances using saved search formula. but its giving ERROR: Invalid Expression. Please help!
I am using formula in saved search like this but its giving invalid expression error CASE WHEN {custrecord1} = ‘Expenses’ THEN {balance}+ CASE WHEN {custrecord1} = ‘Interest Expense’ THEN {balance}+ CASE WHEN {custrecord1} = ‘Adjustments’ THEN {balance}+ CASE WHEN {custrecord1} = ‘Depreciation’ THEN {balance}+ CASE WHEN {custrecord1} = ‘Revenue’ THEN {balance}+ CASE WHEN {custrecord1} = ‘Other Income’ THEN {balance}+ (CASE WHEN {custrecord1} = ‘Interest Earned’ THEN {balance}- CASE WHEN {custrecord1} = ‘Fixed Asset’ THEN {balance}) ELSE 0 END
We need to start by knowing more about the search, is this a Group results search?
I would start by Cleaning up your Case statement it would like this
SUM ( case when {Yourfield}in( ‘Interest Earned’ ,‘Other Income’, ‘Revenue’ …) then {balance} end )-(Case when [Yourfield}=’Fixed Asset’ then (-{balance}) end )
You might need to wrap the balance in a NVL it would look like this: NVL({balance},0)
I did not test this formula but it should work if you are not grouping the results..
