OK, it's been a lon day already but this should be a no brainer. I have a customer who wants to send a report to her dealers BUT if the location quantity available is more than 100 to just display 100+. Easy right?
I created a formula field (Text) and used the following:
CASE WHEN {locationquantityavailable} >100 THEN '100+' ELSE {locationquantityavailable} END
I get ERROR: Invalid Expression. To make sure I get the syntax correct I changed it to (just to test):
CASE WHEN {locationquantityavailable} >100 THEN '100+' ELSE 'Less than 100' END
Now I do not get the Invalid Expression and the results are correct. So I really Need to get the {locationquantityavailable} to work.
One other very strange thing I get as I was trying to get the syntax correct, I accidentally placed the {locationquantityavailable} after the else within the single quotes and believe it or not, this is what was displayed to the screen for all records where the available is less than 100:
(nullif(round(itemlocationmap.ronhandavail/nvl(stockunit.mconversionrate,1),5),0))
Where the heck did THAT come from?