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

Beginner Asked on December 6, 2023 in Saved Searches.
Add Comment
1 Answer(s)

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

Rookie Answered on December 7, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor