Ok sorry guys but like I finally found the right f...
# suitescript
s
Ok sorry guys but like I finally found the right field ID trackingnumberlist only to be unable to query if it isn't null.... How the heck do you do that? I've tried a million different things already like
'ANY_OF_NOT' [null, '']
(unexpected error) and
'IS_NOT' null
(values can't be null), and `'IS_NOT' 'NULL' `(returns all orders even when they don't have tracking numbers
w
As I understand it, Oracle DB (and NetSuite which is built atop it) doesn't have an understanding of the difference between SQL NULL and an empty string--so you want either 'is' '' or 'is empty'
s
There are constants for the operators available through query.Operator, which ensures that you are using a valid value, and there are no typos in the operator. The full list of operators is: AFTER, AFTER_NOT, ANY_OF, ANY_OF_NOT, BEFORE, BEFORE_NOT, BETWEEN, BETWEEN_NOT, CONTAIN, CONTAIN_NOT, EMPTY, EMPTY_NOT, ENDWITH, ENDWITH_NOT, EQUAL, EQUAL_NOT, GREATER, GREATER_NOT, GREATER_OR_EQUAL, GREATER_OR_EQUAL_NOT, IS, IS_NOT, LESS, LESS_NOT, LESS_OR_EQUAL, LESS_OR_EQUAL_NOT, ON, ON_NOT, ON_OR_AFTER, ON_OR_AFTER_NOT, ON_OR_BEFORE, ON_OR_BEFORE_NOT, START_WITH, START_WITH_NOT, WITHIN, WITHIN_NOT
It doesn't look like there is an IS_EMPTY, but there is an EMPTY operator. That one may work
s
yea so I tried checking if it's empty, but apparently null doesnt count as empty to Netsuite
And I am using the query.Operator constants
Using query.Operator.EMPTY_NOT still returns all orders even if their tracking number list is null
s
hmmm. You can try building the query manually through the UI, then load it via the script. You can print out a JSON stringified version of the query, which might give an idea of what the correct operator is. It's not always obvious what NetSuite expects. I wish it were documented better, or they provided examples.
s
I cant even make it work through the ui
The ui at least got me the field id, but I could still only choose from the operators
include
,
include all
,
include only
,
exclude
,
exclude all
and I tried
exclude <NO VALUE>
but that didn't work
s
Yeah, I think something is really odd with filtering by that field. When I add a criteria for: Tracking Numbers include only <NO VALUE>, it filters out all of the NULLs, which is the opposite of what i'd expect.
it may be a bug
s
Hmm I just tried include only <NO VALUE> and got 0 rows even though I have a mix of tracking number and non tracking number orders
strange
So frustrating lol this should be a simple query
s
after playing around with it myself, i think there is a bug with criteria based on that field (either that, or it is doing something very unintuitive). I'd report it to NetSuite support, and see what they come back with. They really want people to adopt the new workbooks, so I think they will be responsive.
s
Yea I took your advice and gave some feedback on the Analytics page so we'll see what happens. Thanks for making me feel like I'm not crazy lol