https://netsuiteprofessionals.com logo
Join Slack
Powered by
i have a custom body field representing an id in a...
# suitescript
k
i have a custom body field representing an id in another system; it's specified as an integer. but apparently i can't search with "anyof"? am i required to store it as a string, or is there another approach i can take to run a search to return summary info for a list of ids?
e
suitesql using the IN clause
e
ANYOF is for lists/select fields
k
thanks for the link - i was looking for that at one point
✅ 1
is suitesql faster/better than search? i'm gonna be a little sad if so
e
It's not strictly either. Depends on your needs and your knowledge.
If you hate writing SQL, you'll hate writing SuiteQL 😄
k
i'd love to be able to write sql, just didn't realize it was available when i started the code
and now i have infrastructure built around search that i don't want to rewrite painfully 😅
i may be able to do a different query i suppose. i don't need an explicit list
but search has been pretty slow for me, so if sql is "closer to the data" and more effective, it might be worth the investment
i can't seem to find sales order / customer deposit in the record browser?
i
could you do it through an sql statement in your search criteria
CASE WHEN {fieldname} IN (12,515,15151,51515,1515) THEN 1 ELSE 0 END
k
oh, that'd do it
i haven't explored "formula" criteria yet
e
FYI 
IN
has a limit of 1,000 values (same in SuiteQL and ANYOF, too)
k
that's plenty reasonable
on the whole, i'm just looking for orders that need to be synced up
i
It seems like doing it on the search would be the easiest since it wouldn’t require redoing the whole query in suiteql
👍 1
k
so i don't expect 1000 to be sitting in created / waiting for fulfillment - but ya never know. it's good to encode the expectations in the code
at the moment i'm playing with a broader query (get me the sales orders which have a nonempty external id in a set of statuses)
that'll probably get the job done
one more query related question while i'm here
i observed that you can use e.g. 
['not', 'isempty']
and also 
['isnotempty']
- but i saw some inkling previously that the former could perhaps be extremely slow
is there a known gotcha here? or should i test more deeply?
e
I've never seen the former
k
well, i left out the column name - but i did test that it seemed to work. but maybe not thoroughly enough 🙂
(haven't needed it yet)
e
I understand about the column, but I've still never seen/used the former, only the latter
k
don't recall where i picked it up, i was playing in a chrome devtools window to get a grip on things
the formula trick did work, though i can't mix an expression with a formula as best as i can tell. i can either do a list of 
search.createFilter
objects (which don't support booleans), or an array of expressions (which do support booleans and nesting) but not both. this is sufficient for me for now, since i don't need both in the same place / at the same time
e
You can do, but it's probably not worth it if you've got it working
k
how so? right now it's a big hack i'm gonna have to clean up. i get an error when just adding one element to the filter search expression - something about expecting an array. glancing briefly through the code in a browser debugger, it appears to try and classify the "filters" argument as "either" an array of search.createFilter objects "or" a filter expression
but it could be that i had some other error in my code
i notice that expressions get converted to a flat list of createFilter objects relying on the "isOr" property (which i can't set via the public api) for grouping, but the function that does that is not exposed to users and the semantics are unclear
e
I've been seeing that "expecting an Array" error recently as well, and I haven't figured out why
k
if it works sometimes, i'm guessing maybe it happens when parsing nested conditions, which i was using
e
You are right that you cannot combine the two syntaxes
👍 1
k
i'll stick with just accepting either but not both through my TS interface by different methods
e
In general, this is how the Filter Expression syntax should look: https://stoic.software/articles/logical-operators-and-grouping/
k
yep, that's what i've got
i just tried to sneak in a "createFilter" argument there and it failed (unfortunately), for the purpose of using a formula
e
ah yeah
k
perhaps worth noting in the blog that unary expressions like ISEMPTY apparently expect an empty third argument (which i discovered by examining a saved search with that one extension)
e
You can use formulae in filter expressions as well:
Copy code
// ...
const daysElapsedFormula = 'CEIL({date}-{datecreated})'
s.create({
    type: s.Type.TIME_BILL,
    filters: [
      [`formulanumeric: ${daysElapsedFormula}`, s.Operator.GREATERTHAN, 7]
    ],
// ...
k
!
well that would solve my pain 🙂
i had trouble finding docs about "filternumeric" vs (presumably) "filterstring" or whatever
i understand that is the type that is expected to be returned from the expression, i don't suppose you know where that stuff is laid out?
e
As far as what types of formulas are available?
k
what types, and the syntax of the expression / interaction with search operators
in the example upthread about 
CASE WHEN {column} IN (values) THEN 1 ELSE 0 END
, i'm assuming that i'm expected to run this as a numeric formula and compare the result to 1
e
Yes that's the right expectation. Here is the official documentation on formulas, but it's a bit all over the place
👍 1
k
what else is new 😅
e
Here is the reference of all the various SQL functions you can use in formulas
❤️ 1
k
(ah, amending my statement above - i believe i saw other properties on createfilter objects for parens - isleft / isright or something like that)
the invocation you suggested did work \o/
✅ 1
it's not quite as good as a WHERE with the case when thing, but i can only assume the database is smart enough not to overwork itself here
s
FWIW, SuiteQL is known to execute faster then N/search.
watching following 1
e
In all my measurements and observations, the results were never consistent enough for me to say that either way in all cases
💯 1
but those have always been pretty narrow
e
In my suiteapp, I converted quite a few searches to queries and the results return quicker, but the 'spin up' is longer. Once a query has been executed once, the next go around with different params executes much quicker
s
I think I heard that 10%+ value from a Netsuite product manager a while ago.
k
(just getting back to this) thanks folks - that gives me an idea of if it's worth it (seems not). this isn't going to be super high traffic probably, i was just looking for a ballpark / order of magnitude. even if it's 10% (which seems uncertain), that's probably not enough for the lift
7 Views
Open in Slack
PreviousNext
Powered by