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!

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.

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.

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.

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.

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

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.

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.

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!

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.

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.

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

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.

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.

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.

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.

If you follow me, you know I’m working on item groups and discovering they are quirky! The same code run from the client-side will not work from the server-side. I recently posted notes about working with Item Groups from the client. As I continued to write client-side SuiteScript, I realized it was not as performant as I would have liked. 

Here are the differences when handling Item Groups between client-side & server-side:

1. Client-side, to delete an item group you delete the “Item Group”, all sub-items, and the “End of Item Group” item. They are treated just like other item types.
2. Client-side, to add an item group you add all lines back exactly as they were removed. Add the group, all sub-items and also the trailing end of group. In my previous post, I noted that in order to accomplish this you needed to look up the Item Group definition to get the list of sub-items.
3. Server-side, you cannot delete the “End of Item Group” item. It throws an error. You delete the “Item Group” and all sub-items and “End of Item Group” go too!
4. Server-side, when you add an “Item Group” all sub-items and the “End of Item Group” item are added for you. There is no need to look up sub-items. So adding one item, can actually add many lines. However, if you’ve modified anything in the item group, there is no mechanism to remember your changes when you add the group back. It comes back as though it is being added for the first time (which it is).

One more caveat, Item groups are editable. So let’s look at the scenario where you are rearranging a transaction. Keep in mind, some financial transactions are locked and cannot be rearranged. However, a transaction like an estimate which has not become a sales order can be rearranged as follows (meaning reordering line items).

This is my algorithm for doing this from server-side code.

1. Remove all sublist items from bottom to top.
2. When you encounter an “End of Item Group” (identified by checking internalId == ‘0’), stop deleting until you reach the “Item Group”. When you delete it, all subsequent lines in the group will be deleted along with it.
   
3. When adding items back, do this from top down.
4. When add lines and you encounter an “Item Group”, stop and remove all items in the group, with the exception of the “End of Item Group”. That cannot be deleted. After the delete completes, the last line is the “End of Item Group” and the line above it is the “Item Group”. The group is empty!
5. Then INSERT all sub-items back into the group. In essence, you are re-filling the group with its’ sub-items. Why? In my scenario, it is possible that items in the group have been modified/added/removed since the group was originally added to the transaction.
   

So that’s it in a nutshell.

My project required an algorithm to reorder items in an estimate. I originally performed this operation client-side. However, it was not performant on lengthy transactions. To fix this, I’m now calling a RESTlet (server-side) to handle all the removal and adding back of line items.

In my sandbox, I was able to take a transaction with 50+ lines that ran over 1 minute and it completed in ~10 seconds. For a transaction with 1-5 items, you could probably get away with running your code client-side.

Hope this saves you some time. I know it took a lot of experimentation for me to get over this speed-bump!