I have a hugely complex Search Formula that I now need to run some further comparisons on.
When I try to do this, I exceed the maximum field length for the Formula.
I was wondering if anyone had any suggestions on alternative ways of building the logic.
This is my current (working) formula (simplified for this post):
CASE WHEN ([complex formula here]) > 0 THEN ([complex formula here]) ELSE 0 END
Essentially, if the formula returns a negative value, I just want to give the result as 0
This is based on Invoice and Cash Sale transactions, but I now need to include Credit Memos and Cash Refunds (which will be genuinely negative values, but need to be excluded if they return a positive value).
So I am trying:
CASE WHEN
(([complex formula here]) > 0) AND ({type} IN ('Invoice','Cash Sale'))
OR
(([complex formula here]) < 0) AND ({type} IN ('Credit Memo','Cash Refund'))
THEN
([complex formula here])
ELSE 0
END
But I can't include the 'complex formula' 3 times as this exceeds the total character limit. I'm not sure what the maximum character limit is, but my 'complex formula' is 1756 characters long.
Is there are SQL function or similar that could provide a more efficient way of evaluating if the 'complex formula' is positive/negative?
Unfortunately I can't see a way to simplify / reduce the 'complex formula' at this time, so I need to try and find a way to build this logic in a different way....