SuiteScript – Mainline filter not working

It’s becoming a common theme for me to write about little frustrations. Hopefully this little frustration will save us both time, as I’m sure I’ll hit this again myself.

According the NetSuite’s Record’s Browser, this should work. And in fact it does… but only if you know ask nicely!

When you filter on “Mainline”, you must do it in a filter expression. A standard search.createFilter() blows up.

Every filter in the filter expression I show above works using search.createFilter(), except “Mainline.”

SuiteScript – Referencing Columns in a Saved Search

This IS documented in SuiteAnswers. However, it took more than a minute to find. So here is my simplified version of “How to load and run a saved search referencing multiple formula columns from SuiteScript 2.0 code.”

Here is my saved search. You’ll notice it has multiple formula columns.

The problem is this. What are my column names. It’s possible to execute result.getValue(‘formulacurrency’). However, it is not possible to excute result.getValue(‘formulacurrency1’) or result.getValue(‘formulacurrency_1’). Neither work.

If you press into debugger to see the column names, you run out of luck.

Moving back one level in the call-stack and looking at mySearch, you see that all columns have been defined , some without a distinct name, all include the distinct formula.

So the solution to how to reference all formula columns is this.

result.getValue(mySearch.columns[4])

I hope this gets you across the finish line. Happy coding!

Reporting Invoices by Vendor

How hard could getting a report of sales by customer and vendor be in NetSuite? Ummm… Harder than you think!

You might look throught the list of standard reports and find this one.

… and you’ll find it a dead end!

Here’s how we solved this.

  1. First Create a custom column in invoice line items to hold the vendor.
  2. Then, you’ll need a “Before Save” event script on Invoices.
    • Look through your line items and find those with no vendor
    • Look up the preferred vendor for those items.
    • Set the custom vendor column to the item’s preferred vendor.

Now you are ready to report.

Here is the secret sauce. And note, if you try to use a “Calculated column”, one where the “Save Value” checkbox is unchecked and the field is sourced from the item’s preferred vendor… well… it won’t work. It simply isn’t an option to select in your report. So there is no getting around the custom field and event script solution at this time.

SuiteScript – Filter Expression – Between Dates

This simple snippet of code gave me fits. Here is a working example of filtering by date range in a SuiteScript 2.0 filter expression.

The first thing you need to know is, don’t try to use the search operator “between” to filter a date range. It doesn’t work. Or I should say, it appears to work, but in reality, it ignores the filter altogether. Instead do this.

SuiteScript N/Search – Grouping Numeric Formula Columns

I wanted a Suitelet that showed rolled up totals based on the expected close date on quotes, by month. There were 2 gotchas in this process. First, how to pull out the month in a search column, and secondly, how to roll that up. Here’s how I did it.

To pull the month out of a date field in a search column, use a numeric formula shown below. Names of numeric formulas must begin with ‘formulanumeric’. This same example will work if you use ‘formulatext’, but won’t roll up.

search.createColumn({
    name: ‘formulanumeric1′,
formula: ‘EXTRACT(MONTH FROM {expectedclosedate})
})

Here is an example of how I used this. I’m pushing my results into an INLINEHTML field.

Grouping Formulas

SuiteScript – Reading Cookies Server-Side

Perhaps I’m missing something. However, I looked and could not find any support for reading cookies in a server-side script in SuiteScript. You can read request headers, but nothing on parsing cookies. Bummer!

My task at hand was saving state of check boxes in a Suitelet. Easy enough? Right? When you serve the page (server side), remember the settings in a cookie. Next GET request, set the check boxes using those cookies. But wait, NetSuite doesn’t appear to support cookies on the server side. In fact, even if you roll your own solution, you still can’t write to a cookie from server-side script.

Note: I’m a maintenance programmer on this example. My company hired a consultant to write this originally. So please let’s just stick to concepts and don’t throw any stones. And… It’s old enough, it’s SuiteScript 1.0. 

So the only apparent way to save checkbox state is to commandeer the submit event (client side) and write your cookies there. Here is a snippet of client script.

Client Side Snippet

When the page reaches the server, the cookies have been set and can be read like this.

Server Side Snippet

And finally, here is where I’m reading my checkbox values from the array of cookies created above. That array has a bunch of other stuff in there too (see all the blurred stuff in the request header shown below), so I need find my specific value from that list.

getCookie

Another quirk of server-side script is that many of the array functions you’re used to on the client side are not available on the server side. Yet another bummer!

Here is the request header passed from client to server. My cookies live at the bottom.

Request Header

SuiteScript – Disable buttons until the page loads?

Here is a very complex problem with a very simple solution. I wasted way too much time trying to understand how to protect my users from clicking a <button> tag in my inline HTML before the NetSuite page had fully loaded. Disabling the button and then enabling it after the page loads? That didn’t work. Managing the event queue? Uh… No, that’s a dead end.

Here is the super-secret handshake! Don’t use <button> tags. Use what NetSuite uses, <input type=”button”> instead. No other code is required.

Using the latter, your buttons are automatically disabled until the page fully loads. In my case, using <button> tags left the tags clickable, and when clicked, it redirected my page to a brand new transaction, instead of editing an existing transaction. Very (very) frustrating!

SuiteScript 2.0 Intellisense for vsCode has Arrived

This blog has afforded me a window into what developers are struggling with. Over the last year+, I’ve seen an uptick in developers using vsCode and SuiteScript 2.0. The problem has been no good solution that brings SuiteScript 2.0 intellisense to vsCode… until now.

Several weeks ago, I began corresponding with Eric Birdsall, another SuiteScript developer who contacted me via my blog. He was interested in a solution to this yet unsolved mystery. When we first talked, he’d been combining NetSuite’s multi-file 2.0 API solution into a single file, much like the 1.0 API. His work produced a lot of excitement, but yielded a very shallow solution, not workable.

SuiteScript2API

Next, I tried reflection, peering back into SuiteScript 2’s modules (‘N/record’, ‘N/search’, ‘N/file’) prototypes to build a working solution. Again, it was way to sparse and unusable.

So Eric plowed head first into a solution. He spent a weekend working through NetSuite’s developer guides and building an extension to vsCode. It’s called SuiteSnippets. Version 1.0.1 is currently available, and it works as expected, bringing intellisense to vsCode. I’d highly encourage you to give it a try. And thank you Eric.

SuiteSnippets

https://marketplace.visualstudio.com/items?itemName=ericbirdsall.SuiteSnippets

SuiteScript’s Quirky Array Methods

I spent all afternoon chasing this bug down. In the end, I learned that the same line of JavaScript code when run in NetSuite’s debugger acts differently than when run in a RESTlet. How can that be?

First, let’s cover some of the really quirky stuff regarding arrays. All my testing was done in SuiteScript 2.0 under NetSuite version 2020.1. I tested in debugger and in a RESTlet I was working on. At issue: Array methods.

[‘123′,’456′,’789’].find(‘456’) will not run in either debugger or a RESTlet. It throws an error when executing the statement.

[‘123′,’456′,’789’].findIndex(‘456’) same!

[‘123′,’456′,’789’].filter(‘456’) to my amazement, actually works and produces consistent results when run client-side, or server-side in debugger or a RESTlet. Awesome!

Here is where it gets annoyingly confusing.

[‘123′,’456′,’789’].indexOf(‘456’) does not throw an error when executing this statement in either scenario. It runs in both debugger and in a RESTlet. However, in debugger it produces a result of 1 and in a RESTlet (also running SuiteScript 2.0) it produces a result of -1.

BEWARE JAVASCRIPT DEVELOPERS. DON’T LET THIS TAKE A BITE OUT OF YOUR BACKSIDE LIKE IT DID MINE!

Oh… One more thing. Since .filter() works, feel free to give this a try. It’s not as efficient as either .findIndex() or .indexOf(), but in my preliminary testing, it works.

        function getIndexOfInArray(arrayOfString, valueToIndex) {
            var index = -1;
            arrayOfString.filter(
                function (a, i) {
                    if (a == valueToIndex) index = i;
                    return a == valueToIndex;
                }
            )
            return index;
        }

SuiteScript Filter Expression Example

I had trouble getting the ‘anyof’ search operator to work in a complex SuiteScript 2.0 filter expression. Perhaps it’s possible. However, I got tired of messing with it. Here is my workaround.

AnyOf

I also needed my results to be unique. I supplied a list of item names and only had room to associate one internal ID with each name in that list. Here is how I solved that problem.

FirstMatch

In my initial test, I included 190 item names in my arrayOfNames. All names in that list matched items where display names did not match item name in the databsae. Some had ‘(COPIED)’ in the item name and were still active. In summary, it was a good sample set.

My test ran is a couple of seconds. It matched 100% of my arrayOfNames (190 out of 190). We have about 40,000 items in our item’s table in NetSuite. This was in sandbox too! So, it was pretty fast.

Here is the code in a form you can paste directly into debugger.

require([‘N/log’, ‘N/search’],
    function (log, search) {
        function getItemIdsByName(arrayOfNames) {
            var myFilters = new Array();
            arrayOfNames.forEach(
                function (name) {
                    if (myFilters.length > 0) myFilters.push(‘or’);
                    myFilters.push([
                        [‘itemid’, ‘is’, name],
                        ‘or’,
                        [‘displayname’, ‘is’, name]
                    ])
                }
            )
            var mySearch = search.create({
                type: search.Type.ITEM,
                columns: [‘internalid’, ‘itemid’, ‘displayname’],
                filters: [
                    [‘isinactive’, ‘is’, false],
                    ‘and’,
                    [‘itemid’, ‘doesnotcontain’, ‘(COPIED)’],
                    ‘and’,
                    [myFilters]
                ]
            });
            var myPages = mySearch.runPaged({ pageSize: 1000 });
            var matches = new Array();
            for (var i = 0; i < myPages.pageRanges.length; i++) {
                var myPage = myPages.fetch({ index: i });
                myPage.data.forEach(
                    function (match) {
                        var itemid = match.getValue(‘itemid’);
                        var displayname = match.getValue(‘displayname’);
                        var internalid = match.getValue(‘internalid’);
                        matches.push({
                            itemid: itemid,
                            displayname: !displayname ? itemid : displayname,
                            internalid: internalid
                        })
                        return true;
                    }
                )
            }
            var results = new Array();
            arrayOfNames.forEach(
                function (name) {
                    var firstMatch = matches.filter(
                        function (a) {
                            // If the itemid or the displayname matches a name in arrayOfNames, it matches.
                            if (a.itemid === name) return true;
                            if (a.displayname === name) return true;
                            return false;
                        }
                    )
                    if (firstMatch && firstMatch.length > 0) {
                        results.push({
                            itemid: name,
                            internalid: firstMatch[0].internalid
                        })
                    }
                }
            )
            return results;
        }
        var arrayOfNames = [
            ‘Your Item Name here #1’,
            ‘Your Item Name here #2’,
            ‘Your Item Name here #3’,
            ‘Your Item Name here #190’,
        ];
        var matches = getItemIdsByName(arrayOfNames);
        // The log function is slow. Don’t call it more than once if you are
        // interested in response time.
        log.debug(‘Matched ‘ + matches.length + ‘ out of ‘ + arrayOfNames.length);
        // If you want to see results and not measure response time.
        // matches.forEach(
        //     function (match) {
        //         log.debug(‘itemid: ‘ + match.itemid + ‘, internalid: ‘ + match.internalid);
        //     }
        // )
    }
)