I spent 14 years running a small software company in Kansas City. During my time writing software for numerous customer, I had issues that produced outages. What I learned was it was always best to fess up and tell the truth. If you are truthful in the small things, you’re probably truthful in the big things. Customers recognize that.
This morning my phone rang at 9:20 AM Central US time. NetSuite was down! Let me run back through what I experienced, just to document. I’ll tell you my version of the truth! Let me add, I’m a NetSuite fan! I recommend NetSuite as an excellent cloud based ERP provider and will continue to recommend them to other businesses. Do they always tell the truth? Hmmm… You can judge for yourself.
9:20 – NetSuite status page shows no problems. Last update was from 1/12/2022
My users were seeing this message
I called NetSuite support and got a message that all lines were busy
I started monitoring a forum for NetSuite outages. I posted this at 10:05 CST.
By 10:15, NetSuite updated their status page as follows.
At 10:25, I got through to NetSuite support. I posted this in the forum.
At 11:05 AM I posted this in the forum.
At 11:20 AM NetSuite was back online
During the outage, I noted NetSuite customers from all over North America, Mexico, UK, Canada, Singapore who could not login to production NetSuite. In the US, users from New York, Georgia, Texas, Ohio, Pennsylvania and of course Kansas City were also unable to login. The NetSuite status page has confirmed an outage in the US Seattle data center. That’s probably true, even though it defies common logic.
And one more note about being truthful. If NetSuite would debrief us, as customers with more detail, perhaps we’d be more inclined to believe that this outage was limited to just their west coast US data center customers.
As a footnote: 30 days per month. 24 hours per day. 99.98% available or 0.02% unavailable. 30 * 24 * (1 – 0.9998) = 8 minutes 38 seconds a month.
This error was so hard to find, I thought I’d document it. The error message read: “Invalid number of parameters. Expected: 0. Provided: 3“
I was coding a SuiteQL query in SuiteScript Version 2.0. Here’s an example.
If you happen to make the mistake I made and add an extra comma at the end of one of the column definitions, BOOM!
This is relatively easy to spot is a small query. However, in a much larger query with lots of columns, it’s easy to miss. I’ll attest to that. Here is the code in case you want to try this at home.
require(['N/query'],
function (query) {
log.debug('start');
const MY_SQL_CONSTANT =
"select " +
" BUILTIN.DF(tl.item) as item_name\n" +
" , tl.linesequencenumber as line\n" +
"from transactionline tl\n" +
"where tl.transaction = ? " +
" and tl.item is not null " +
"order by tl.linesequencenumber";
var results = query.runSuiteQL({
query: MY_SQL_CONSTANT,
params: [your transaction id here]
}).asMappedResults();
log.debug(results[0].item_name);
log.debug('finished');
}
)
Visual Studio Code natively supports editing multiple lines, adding a quote at the beginning of the line and a quote followed by a comma at the end of the line. Here’s how. This example is from vsCode running on Windows 10.
ctl+home – to move to top
shift+ctl+alt+[down arrow] – to select all lines that you wish to multi-line edit
single tick – adds opening tick to all selected lines
end – to jump to the end of all lines in this multi-line edit block
single tick and comma – close and end all lines
esc – to get out of multi-line edit mode
ctl+a – to select all lines
[ – to enclose the entire list as an array (this is JavaScript)
I recently ran into an issue where a new integration wasn’t doing what it was supposed to. During debugging I learned some tips and tricks that I don’t want to forget.
In my case, I was dealing with a Hubspot integration where I was synchronizing contacts from NetSuite to Hubspot. Initially, I installed a Hubspot bundle. Let’s start there.
Customization >> SuiteBundler >> Search and Install Bundles >> List
Open the Hubspot bundle. Review the Components tab.
You can see that this bundle includes a couple of JavaScript Restlets. Restlets are stateless APIs. It is important to note that Restlets are not scheduled, and are not triggered on events. They simply sit and wait for requests, in this case from Hubspot. So let’s start by looking at the integration.
Setup >> Integration >> Manage Integrations
I clicked on Hubspot Sync. Look at the “Execution Log” tab. There are 3 subtabs.
You may be asking yourself about the REJECTEDACCOUNTCONCURRENCY status. It’s not a problem, so let’s table that for now.
If you click on the “view” links under both request and response, you can see the SOAP request and response to Hubspots API calls. Remember, the Restlet is stateless and simply responds to requests from an outside entity (Hubspot).
If you click on the REST Web Services subtab, you see more of the same. However, there is more detail if you drill into the “view” links under both request and response columns.
Let’s look at a request.
And here is the response.
This is a great place to review communication to and from your integration. And now back to the REJECTEDACCOUNTCONCURRENCY status that we saw earlier.
In a standard NetSuite installation, you are allowed 5 concurrent transactions. It is my understanding this includes UI interactions. So if 2 people hit enter at the same time, that counts for 2. If you have multiple integrations, like Hubspot, each call to an API also counts as 1. So in our case, we have enough users and integrations that we hit our concurrency limit from time to time. This is normal!
If you want more info on all of your integrations, look here.
Setup >> Integration >> SOAP Web Services Usage Log
And the final thing I’ll point out is how you tell what your concurrency limit is. Look at any of your integrations. This is where it will show you your max concurrency limit.
Oh… One more thing… Here is a great place to see how often an API request is rejected.
I just got off the phone with Jason at NetSuite support. Thank you Jason! I’d called several months ago when initially setting up NetSuite Upload. In order to save a callback when reinstalling, I wrote a blog article with required steps. After refreshing out sandbox, I followed my steps, but without success. Unfortunately, I’d missed a step. I omitted permissions.
This call turned out to be a positive experience, because Jason taught me how to troubleshoot Token Based Authentication problems. So in this post, I’ll share the missing permission, and I’ll share how to troubleshoot any token based application like this one.
So how did we find the missing permissions? Step 1: Determine if we were logging in successfully or not. To do that we did the following:
Create a saved search on “Login Audit Trail” records
Add Detail, Token-based Access Token Name. Token-based Application Name in the results columns
Sort by Date descending
What we saw was my TBA user had successfully logged in. TBA was working! This prompted us to look further into the Restlet called by NetSuite Upload, vscodeExtensionRestlet.js. To do that, we opened its script deployment and reviewed the execution log sublist. That made it abundantly clear we were missing the “Allow JS/HTML Upload” permission on the user. That’s the user I’d assigned in step 2 of the previous post (where I created the Access Token).
So the tricks are… Write an ad hoc saved search on the login audit trail, and review the detail. If you’ve successfully logged in, then keep digging into the application, in this case it was a restlet, and see what’s breaking there.
I don’t know if this is an Oracle issue or something related to NetSuite. I come from a Microsoft SQL server world where queries like this work. Perhaps there’s a way to do this in PL/SQL, but I have not found it.
It is possible this query works outside of NetSuite, but it does not work in SuiteSQL. I’ve noticed other problems rolling up data in NetSuite, even in Saved Searches. Maybe that’s related.
I’ll add that I know from attending sessions at SuiteWorld that NetSuite is in the process of modifying their provisions such that each customer will have their own database. I’m unclear where they are in that process. But at the last SuiteWorld I attended, all queries were being modified to include an additional filter adding a customer’s account number. This was explained to me as all tables included data for all customer accounts in a common database. So NetSuiteCustomer#1 and NetSuiteCustomer#2 both submit a query to list their customers, “Select * from customers“. NetSuite will modify this to be “Select * from customers where NetSuiteCustomerID = [Your NetSuite Account Number]“. Perhaps this explains what you see below.
If you have a solution to this issue, feel free to send it to me. I’ll repost it and credit you. But wait! Before you rewrite my query, thinking you’ve found my flawed reasoning…
This doesn’t work either…
AND… Removing the NVL wrapper around my raw data column… Uh no! That doesn’t work either.
Best of luck to you if you decide to take on this problem. I ended up solving it by pulling the raw data (omitting the SUM() function altogether) and rolling it up in Excel.
Happy Programming!
September 2022: This comment is from Joseph Lee. He writes:
Hello Kevin,
You should be able to do something similar to get yours to sum properly. The error I am now experiencing is when I try to do a group by rollup my forecast column only sums the amounts from opportunities and now everything else. I suspect it has to do with my case statement and I’m forced to roll up in the subquery.
Here is Joseph’s code. Thank you Joseph!
WITH
SalesSummary AS
(
SELECT
Employee AS RepID,
Type,
SUM( ForeignTotal ) AS ForeignTotal,
SUM( projectedTotal*probability ) AS ProjectedTotal
FROM
Transaction
WHERE
( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) IN ('SalesOrd:B','SalesOrd:A') AND TO_CHAR(shipdate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(shipdate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'Opprtnty' ) AND ( BUILTIN.CF( Status ) IN ('Opprtnty:A','Opprtnty:C') AND TO_CHAR(expectedclosedate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(expectedclosedate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CustInvc' ) AND ( BUILTIN.CF( Status ) IN ('CustInvc:A','CustInvc:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CashSale' ) AND ( BUILTIN.CF( Status ) IN ('CashSale:C','CashSale:B') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
OR ( ( Type = 'CustCred' ) AND ( BUILTIN.CF( Status ) IN ('CustCred:B','CustCred:A') AND TO_CHAR(trandate, 'mm') = TO_CHAR(sysdate, 'mm')
AND TO_CHAR(trandate, 'yyyy') = TO_CHAR(sysdate, 'yyyy') ) )
GROUP BY
Employee,
Type
)
SELECT
a.RepName,
a.Opportunity_Total,
a.Sales_Pending_Total,
a.Invoiced,
a.Cash_Sale,
a.Credit_Memo,
case when (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo) < Opportunity_Total
then Opportunity_Total
else (a.Sales_Pending_Total + a.Invoiced + a.Cash_Sale + a.Credit_Memo)
end Forecast
from (
SELECT
RepID,
( Employee.FirstName || ', ' || Employee.LastName ) AS RepName,
SUM ( CASE WHEN Type = 'Opprtnty' THEN ProjectedTotal ELSE 0 END ) AS Opportunity_Total,
SUM ( CASE WHEN Type = 'SalesOrd' THEN ForeignTotal ELSE 0 END ) AS Sales_Pending_Total,
SUM ( CASE WHEN Type = 'CustInvc' THEN ForeignTotal ELSE 0 END ) AS Invoiced,
SUM ( CASE WHEN Type = 'CashSale' THEN ForeignTotal ELSE 0 END ) AS Cash_Sale,
SUM ( CASE WHEN Type = 'CustCred' THEN ForeignTotal ELSE 0 END ) AS Credit_Memo,
FROM
SalesSummary
INNER JOIN Employee ON
( Employee.ID = SalesSummary.RepID )
WHERE
RepID IN ('17','24','-5','26')
GROUP BY
RepID,
( Employee.FirstName || ', ' || Employee.LastName )
ORDER BY
RepName
) a
This is conjecture, not fact, on my part. It began with our NetSuite upgrade to 2021.2, which broke NetSuite-Sync. NetSuite-Sync is a Visual Studio Code plugin that makes it easy to upload JavaScript files to sandbox or production NetSuite with a couple of keystrokes. It only supports credentialed authentication.
After waiting a couple days, trying to upload to both our production and sandbox, and even swapping URLs to NetSuite SOAP API, nothing worked. I even tried a different plugin “Netsuite Upload.” Again, I used a credentialed based authentication. It failed too.
I had been notified that all SuiteTalk apps were going to require Token Based Authentication, and would no longer support credential-based authentication, but I never equated this to NetSuite-Sync or “NetSuite Upload.” However, this appears to be what broke them.
And now I’ll cut to the case. If you are using NetSuite-Sync… well… you’re sunk! npm uninstall netsuite-sync -g.
I installed “NetSuite Upload” from directly from within vsCode. It’s a plugin. Just find it by name and click install. It’s different from NetSuite-Sync in that it doesn’t use the Netsuite SOAP API, but instead uses it’s own custom Restlet. It also supports Token Based Authentication (TBA).
There is a link in the NetSuite Upload documenation to download the latest vscodeExtensionRestlet.js. If you are a developer, you’ll know how to upload that to the NetSuite file cabinet and create a Script File Definition, as well as a Deployment. Do that next.
Here are some notes about setting up the Token Based Authentication.
Setup >> Integreations >> Manage Integrations >> New
Setup >> Users/Roles >> Access Tokens >> New
Be sure to copy the output from both the Integration and the Access Token when you hit save in the previous two screens. You won’t ever see that info again. So paste it into Notepad and put it somewhere safe. Then use it to fill out your NetSuite upload settings. Your Realm is your account number. In this case, it is my sandbox account.
The last thing I’ll mention is that I have folders in my source code repository. I organize my scripts by type, like Event Scripts, Suitelets, Restlets, etc. I do not have similar folders in the NetSuite SuiteScripts directory. I upload all scripts to s single folder. When I was using NetSuite-Sync, it always uploaded to one and only one folder, no matter where the file was located on my local hard drive. So I wanted NetSuite Upload to work the same. To accomplish this, I modified the vscodeExtensionRestlet.js file as follows:
I make sure that no matter what folder on my hard drive a file originates from, it ends up in “SuiteScripts/[2nd level folder]/”. Be sure to modify all three functions, getFunc, postFunc and deleteFunc.
That’s the lowdown. From there you can open any JavaScript file in your source tree and hit [ctl+n] & [ctl+u] to unload it to NetSuite. If you right click the file in the left Nav, you’ll see you have options to download files, compare files between your local copy and Netsuite. NetSuite Upload is more robust than NetSuite-Sync. The only drawback is it is not so easy to swap between production and sandbox. I’ll save that for another blog post. Happy computing!
Tabs are a great way to organize screen real estate. However, there are some things that you need to keep in mind when adding tabs to a Suitelet.
If all controls in your Suitelet are standard NetSuite controls and you do not add any INLINEHTML, you should be fine. However, there are times when you need to do your own thing. If that is your situation, read on!
If you add <input> or <textarea> or any other standard HTML controls tags inside an INLINEHTML, the values in those will correctly POST. They work just like NetSuite controls, form.addField({ type: ANYTHING_BUT_INLINHTML}). However, once you add tabs to your Suitelet, those same <input> controls no longer POST.
The workaround is this. In the client script, be sure to populate a standard NetSuite control, which can be hidden, with any values you need to POST back to your server-side program.
Here is an example of how this works. In this Suitelet, I have 2 tabs and 3 controls which I POST back to the server. You can’t see it here, but Tab 2 has a hidden serverWidget.FieldType.HIDDEN control which I POST back to the server. What I’m demonstrating is that you can still use INLINEHTML <input>, <textarea> tags, but be sure to move the results into your HIDDEN field before submitting the page.
When you click POST, which is just a standsard <submit> control, here is what you see.
The <textarea> control that is nested inside an INLINEHTML control, but not inside a tab, works great. Results are sent back to the server. The <textarea> control inside Tab 1 does not make it back. And finally the serverWidget.FieldType.TEXT, but hidden, inside Tab 2 also works fine and POSTs correctly back to the server.
One more things before I show you my code. If you dissect the DOM, you’ll find that each tab has become it’s own <form>. If you have 2 tabs, you’ll have at least 3 forms . Form[0] is full of a bunch of NetSuite variables. Once I added tabs, I could no longer jQuery(‘form’).submit(). I ended up doing a jQuery(‘input[type=”submit”]’)[0].click().
Here is my example code, which hopefully makes better sense of this topic.
/**
*@NApiVersion 2.x
*@NScriptType Suitelet
*/
define(['N/ui/serverWidget'],
function (serverWidget) {
function onRequest(context) {
var form = serverWidget.createForm({
title: 'Custom Suitelet Demo of Tabs Response on POST'
});
form.addFieldGroup({
id: 'fieldgroup_main',
label: 'Primary Information'
})
if (context.request.method === 'POST') {
// Show results of POST
var parameters = context.request.parameters;
var results = form.addField({
id: 'custpage_not_in_tab_inlinehtml',
type: serverWidget.FieldType.INLINEHTML,
label: 'This label will not show',
container: 'fieldgroup_main'
});
results.defaultValue = [
'<b>custpage_not_in_tab:</b> ' + parameters.custpage_not_in_tab + '<br />',
'<b>custpage_in_tab:</b> ' + parameters.custpage_in_tab + '<br />',
'<b>custpage_std_control:</b> ' + parameters.custpage_std_control + '<br />',
].join("\n");
}
else {
// Build the initial Page on GET
form.addField({
id: 'custpage_label1',
type: serverWidget.FieldType.LABEL,
label: 'There is a standard HTML control outside of tabs',
container: 'fieldgroup_main'
});
// Add a standard html control (not a NetSuite form.addField()) outside of the tabs
var results = form.addField({
id: 'custpage_not_in_tab_inlinehtml',
type: serverWidget.FieldType.INLINEHTML,
label: 'This label will not show',
container: 'fieldgroup_main'
});
results.defaultValue = [
'<textarea id="custpage_not_in_tab" name="custpage_not_in_tab">This will make it back on POST</textarea>'
].join("\n");
// You'll need at least 2 tabs for them to show.
form.addTab({
id: 'custpage_tab_1',
label: 'Tab 1'
});
form.addField({
id: 'custpage_label2',
type: serverWidget.FieldType.LABEL,
label: 'There is a standard HTML control inside a tab',
container: 'custpage_tab_1'
});
// Add another standard html control inside of the tabs
var control_in_tab = form.addField({
id: 'custpage_in_tab_inlinehtml',
type: serverWidget.FieldType.INLINEHTML,
label: 'This label will not show',
container: 'custpage_tab_1'
});
control_in_tab.defaultValue = [
'<textarea id="custpage_in_tab">This will NOT make it back on POST</textarea>'
].join("\n");
form.addTab({
id: 'custpage_tab_2',
label: 'Tab 2'
});
form.addField({
id: 'custpage_label3',
type: serverWidget.FieldType.LABEL,
label: 'There is a hidden NetSuite control inside a tab',
container: 'custpage_tab_2'
});
/**
* The shipmethod control must be a standard netsuite control
* in order to post back to the server while the control is placed inside a
* tab.
*/
var custpage_std_control = form.addField({
id: 'custpage_std_control',
type: serverWidget.FieldType.TEXT,
label: 'This label will not show',
container: 'custpage_tab_2'
}).updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
custpage_std_control.defaultValue = 'This text WILL make it back to the server';
}
form.addSubmitButton({
label: 'POST'
});
context.response.writePage(form);
}
return {
onRequest: onRequest
}
}
);
Why can’t I calculate shipping in sandbox? How can I test that shipping rates change when I change the shipping method? In sandbox, I get this nebulous error: “Could not calculate cost for the selected Realtime rate. UPS did not return any rates for the specified origin and destination addresses.”
There are two places to configure shipping in NetSuite. I’m referencing these from the Administrator role.
Setup >> Accounting >> Shipping – where you define your shipping carrier information
Lists >> Accounting >> Shipping Items – where you define the delivery methods (like ground, next day air, etc.)
When you refresh your sandbox, most of the carrier info gets transferred, but not all of it. And it is possible that when you setup your carrier info in sandbox, you’ll need special accounts (test accounts) from your carriers.
Since sandbox is not production, but you still need to test, you can simply set unique rates for each of your shipping items. That way, when you change from UPS Ground to 2nd Day air, shipping cost will change. It won’t be correct, but who cares? It’s sandbox! Here is where you’ll do that under the shipping item.
My job is to find ways to make other people’s job easier. One of my tasks involved calculating shipping from a custom Suitelet. This presented multiple obstacles. Here are my notes. I’m betting this will save both of us time if we ever need to come back to calculating shipping.
First, the code that calculates shipping works like this. It’s client-side code that builds an XML request which is sent to the server to do the heavy lifting. All of NetSuite’s client-side code is written in SuiteScript 1.0. It all ends with a server call that looks like this…
As you can see, the client side code sends a request to the server where it passes xmlStr to getShippingRates. However, all the code which builds xmlStr is buried deep in the standard estimate Edit screen and not available in a custom Suitelet, unless you put it there yourself.
Trick number one, how to find the NetSuite’s client side code that handles building the xmlStr. Using Chrome Developer Tools, you can inspect the button and see which event it fires.
However, searching in Chrome Dev Tools to find calculateRates was futile. So here’s how I found it.
Edit an estimate (or sales order)
Open Dev Tools
In the console tab, type the following and hit enter. It exposes the calculateRates function, along with all the other code which builds the xmlStr parameter.
Once I could see the JavaScript, I could cut and paste it into a text file where I was able to rewrite the SuiteScript 1.0 code and integrate it into my client side JavaScript in my custom Suitelet.
Here is my sample code written in SuiteScript 2.0. It was derived from NetSuite standard “Calculate Shipping” code written in SuiteScript 1.0. If you jump to the bottom and review the calculateRate function, this is where it all begins. And please remember, THIS IS CLIENT-SIDE CODE. It will ultimately be fired by a button click or some other event in your custom Suitelet.
NetSuite Experiences 