[JCirocco Contributions: 0Level 1☆]

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?
This is a cached copy. Click here to see the original post.

[al3xicon Contributions: 0Level 1☆]

It probably doesn’t like being Formula (Text) and returning an actual Numeric field. Does it work if you wrap {locationquantityavailable} in TO_CHAR()?

[JCirocco Contributions: 0Level 1☆]

THANKS Alex! How about the results of ‘{locationquantityavailable}’ (in single quotes) displaying as (nullif(round(itemlocationmap.ronhandavail/nvl(stockunit.mconversionrate,1),5),0))

Never seen that before. Hope all is well and thanks again.

---

al3xicon replied on 10/08/2015, 01:05 PM: No problem. Can’t help you on that one I’m afraid! Looks like one of the gremlins inside of NetSuite got loose again.

[Author]

Posts