[darrenhillconsulting]

So,

As we all know, Netsuite’s search don’t support SQL statements (a real shame). Instead, you need to create a bunch on variables, fields, etc to to execute a simple search.

For years, I’ve been building a library to remove this, and I just updated it for SS2.0 … see it at npm at https://www.npmjs.com/package/@darre…sulting/jsuite

I’m taking on an impossible dream now … attempting to mimic the SELECT * in SQL

Essentially, when you request ‘customer’ (or any other record for that matter), I do the followingcreate a temp record of the request recordType
use the getFields() function to get all possible fieldsthe problem is that it gives you EVERY field .. most of which are not valid search fields

I then filter out those fields against valid search fields
I also allow any ‘custentity’ fields to go through
I create searchColumns based on these namesI do so creating ‘formulas’ so if Netsuite doesn’t like the search column, it won’t fail

I do some tidying up of the results
It works ‘ok’, but I’m sure it could be better,

This is a long way of asking if anyone else has ever tried to implement a similar feature/behaviour.

If we could do this, there’s great potential I think.
This is a cached copy. Click here to see the original post.

[david.smith]

Hey Darren.

I was just talking with my coworker the other day about this. Our conclusion was that it would be great to have but might be short lived. NetSuite says they are going to have the SQL ability soon. How soon, who knows?

I’ve done some of these things your attempting in the past as well. It’s really frustrating that the getFields doesn’t filter out the back-end junk from NS. I might actually have a trick or two that could help.

Oh, here is a tip, not just for this but in general with SS2.0. DO NOT use the search.runPaged() for anything!! I’ve tried using it a few times now and it has problems. Inconsistent results and it may cause errors in SCA if used in connection there.

[darrenhillconsulting]

Noted! Thanks David for the feedback.

And … what?!?!? … where did you hear/read that Netsuite will have SQL abilities. I may stop pulling our my hair on this if that is the case.

As for the runPaged(), ya .. never.

jSuite’s runSearch makes recursive calls in order to grab all data (bust through the 4000 record limit). I’m pretty proud of its ability as it monitors units, so it’ll never die.

[david.smith]

This is the best I could find now but at SuiteWorld 2016 it was shown as a future looking enhancement (or whatever you want to call it)

https://netsuite.custhelp.com/app/an…ail/a_id/50614

[JCirocco]

Darren,

It was a demo by Evan at 2 sessions that I was at (Partner Session and the General Keynote) where he showed multiple level joins and then the last was the straight SQL. They initially announced a total rewrite of Reporting and Searches back in 2014 then we go to see last May. I am REALLY hoping that it is at least Beta in 2017-1

[karenn]

What is in that presentation cannot come soon enough!!! Awesome!!!

[Author]

Posts