NetSuite Upload Permissions & How to troubleshoot TBA

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.

Start here if you are initially installing NetSuite Upload: https://followingnetsuite.com/2021/08/26/netsuite-sync-netsuite-upload-and-other-vscode-plugins-broken-by-tba/

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.

Happy coding!

Beware: SuiteSQL – SUM() function is a loser!

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!

NetSuite-Sync, NetSuite Upload, and other vsCode plugins broken by TBA

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!

The Pitfalls of Adding Tabs in a Suitelet

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
        }
    }
);

NetSuite – Calculating Shipping Costs in Sandbox

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.

SuiteScript & The Calculate Shipping Button

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…

nlapiServerCall("app/common/shipping/dynshippingxml.nl", "getShippingRates", [xmlStr], null, 'POST');

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.

function isValEmpty(val) {
    if (val === null || val === undefined)
        return true;

    val = String(val);
    return (val.length === 0) || !/\S/.test(val);
}


function getExchangeRate(rec) {
    var dExchangeRate = rec.getValue('exchangerate');
    if (isNaN(dExchangeRate) || dExchangeRate.length < 1 || dExchangeRate < 0) {
        dExchangeRate = 1;
    }
    return parseFloat(dExchangeRate);
}

function isFulfillableItem(sItemType, bFulfillable) {
    var bIsFulfillableItem = false;
    if ('Assembly' == sItemType || 'InvtPart' == sItemType || 'Group' == sItemType || 'Description' == sItemType) {
        bIsFulfillableItem = true;
    } else if (('Service' == sItemType || 'OthCharge' == sItemType || 'GiftCert' == sItemType || 'NonInvtPart' == sItemType || 'Kit' == sItemType || 'DwnLdItem' == sItemType) && bFulfillable == 'T') {
        bIsFulfillableItem = true;
    }
    return bIsFulfillableItem;
}

function createRatesRequestXml(requestSrc, ratesCarrier, entityId, destCity, destState, destZip, destCountry, shipMethod, salesOrderId, isResidential, isThirdPartyAcct, thirdPartyCarrier, isPackagesReload, currency, tranFxRate, subsidiary, bIsItemLineRates, testId, nexusId, packages, items, isDefaultRequest, overrideShippingCost, isDynamicScriptingRequest, shipmentParameters) {
    var ratesReqXml = '<RatesRequest>';
    ratesReqXml += createXMLElement('RequestSrc', requestSrc);
    ratesReqXml += createXMLElement('RatesCarrier', ratesCarrier);
    ratesReqXml += createXMLElement('EntityId', entityId);
    ratesReqXml += createXMLElement('IsItemLineRates', bIsItemLineRates);
    ratesReqXml += createXMLElement('DestCity', destCity);
    ratesReqXml += createXMLElement('DestState', destState);
    ratesReqXml += createXMLElement('DestZip', destZip);
    ratesReqXml += createXMLElement('DestCountry', destCountry);
    ratesReqXml += createXMLElement('ShipMethod', shipMethod);
    ratesReqXml += createXMLElement('SalesOrderId', salesOrderId);
    ratesReqXml += createXMLElement('IsResidential', isResidential);
    ratesReqXml += createXMLElement('IsThirdPartyAcct', isThirdPartyAcct);
    ratesReqXml += createXMLElement('ThirdPartyCarrier', thirdPartyCarrier);
    ratesReqXml += createXMLElement('IsPackagesReload', isPackagesReload);
    ratesReqXml += createXMLElement('IsDefaultRequest', isDefaultRequest);
    ratesReqXml += createXMLElement('Currency', currency);
    ratesReqXml += createXMLElement('TransactionFxRate', tranFxRate);
    ratesReqXml += createXMLElement('Subsidiary', subsidiary);
    ratesReqXml += createXMLElement('TestId', testId);
    ratesReqXml += createXMLElement('NexusId', nexusId);
    ratesReqXml += createXMLElement('OverrideShippingCost', overrideShippingCost);
    ratesReqXml += createXMLElement('IsDynamicScriptingRequest', isDynamicScriptingRequest);
    ratesReqXml += getAdditionalShipmentXML(shipmentParameters);
    ratesReqXml += getPackagesXML(packages);
    ratesReqXml += getItemsXML(items);
    ratesReqXml += '</RatesRequest>';
    return ratesReqXml;
}

function createXMLElement(nodeName, nodeValue) {
    if (!isValEmpty(nodeValue)) { return ('<' + nodeName + '>' + xml.escape(nodeValue.toString()) + '</' + nodeName + '>'); } else { return '<' + nodeName + '/>'; }
}

function getAdditionalShipmentXML(parameters) {
    var result = '';
    if (parameters != null) {
        for (var paramKey in parameters) {
            result += createXMLElement(paramKey, parameters[paramKey]);
        }
    }

    return result;
}

function getPackagesXML(packages) {
    var packagesXml = '<Packages>';
    if (packages != null && packages.length > 0) {
        for (var i = 0; i < packages.length; i++) {
            var pkg = packages[i];
            if (pkg != null && pkg != undefined) {
                packagesXml += '<Package>';
                packagesXml += createXMLElement('PackageNumber', pkg['PackageNumber']);
                packagesXml += createXMLElement('PackageLength', pkg['PackageLength']);
                packagesXml += createXMLElement('PackageWidth', pkg['PackageWidth']);
                packagesXml += createXMLElement('PackageHeight', pkg['PackageHeight']);
                packagesXml += createXMLElement('PackageWeight', pkg['PackageWeight']);
                packagesXml += createXMLElement('PackageType', pkg['PackageType']);
                packagesXml += createXMLElement('PackageInsuredValue', pkg['PackageInsuredValue']);
                packagesXml += createXMLElement('PackageSignatureOption', pkg['PackageSignatureOption']);
                packagesXml += createXMLElement('AdditionalHandling', pkg['AdditionalHandling']);
                packagesXml += createXMLElement('UseCOD', pkg['UseCOD']);
                packagesXml += createXMLElement('CODAmount', pkg['CODAmount']);
                packagesXml += createXMLElement('CODMethod', pkg['CODMethod']);
                packagesXml += createXMLElement('DeliveryConfirmation', pkg['DeliveryConfirmation']);
                packagesXml += createXMLElement('DryIceWeight', pkg['DryIceWeight']);
                packagesXml += createXMLElement('DryIceUnit', pkg['DryIceUnit']);
                packagesXml += createXMLElement('CODTransportationCharges', pkg['CODTransportationCharges']);
                packagesXml += createXMLElement('CODOtherCharge', pkg['CODOtherCharge']);
                packagesXml += '</Package>';
            }
        }
    }
    packagesXml += '</Packages>';
    return packagesXml;
}

function getItemsXML(items) {
    var itemsXml = '<Items>';
    if (items != null && items.length > 0) {
        for (var i = 0; i < items.length; i++) {
            var item = items[i];
            if (item != null && item != undefined) {
                itemsXml += '<Item>';
                itemsXml += createXMLElement('ItemQuantity', item['ItemQuantity']);
                itemsXml += createXMLElement('ItemAmount', item['ItemAmount']);
                itemsXml += createXMLElement('ItemWeight', item['ItemWeight']);
                itemsXml += createXMLElement('ItemKey', item['ItemKey']);
                itemsXml += createXMLElement('ItemLocation', item['ItemLocation']);
                itemsXml += createXMLElement('ItemUnits', item['ItemUnits']);
                itemsXml += createXMLElement('ItemType', item['ItemType']);
                itemsXml += createXMLElement('ItemExcludeFromRateRequest', item['ItemExcludeFromRateRequest']);
                itemsXml += createXMLElement('ItemShipAddrKey', item['ItemShipAddrKey']);
                itemsXml += createXMLElement('ItemShipAddr1', item['ItemShipAddr1']);
                itemsXml += createXMLElement('ItemShipAddr2', item['ItemShipAddr2']);
                itemsXml += createXMLElement('ItemShipCity', item['ItemShipCity']);
                itemsXml += createXMLElement('ItemShipState', item['ItemShipState']);
                itemsXml += createXMLElement('ItemShipZip', item['ItemShipZip']);
                itemsXml += createXMLElement('ItemShipCountry', item['ItemShipCountry']);
                itemsXml += createXMLElement('ItemShipIsResidential', item['ItemShipIsResidential']);
                itemsXml += createXMLElement('ItemShipMethKey', item['ItemShipMethKey']);
                itemsXml += createXMLElement('ItemName', item['ItemName']);
                itemsXml += createXMLElement('ItemDescription', item['ItemDescription']);
                itemsXml += createXMLElement('ItemCountryOfManufacture', item['ItemCountryOfManufacture']);
                itemsXml += createXMLElement('ItemProducer', item['ItemProducer']);
                itemsXml += createXMLElement('ItemExportType', item['ItemExportType']);
                itemsXml += createXMLElement('ItemManufacturerName', item['ItemManufacturerName']);
                itemsXml += createXMLElement('ItemMultManufactureAddr', item['ItemMultManufactureAddr']);
                itemsXml += createXMLElement('ItemManufacturerAddr1', item['ItemManufacturerAddr1']);
                itemsXml += createXMLElement('ItemManufacturerCity', item['ItemManufacturerCity']);
                itemsXml += createXMLElement('ItemManufacturerState', item['ItemManufacturerState']);
                itemsXml += createXMLElement('ItemManufacturerZip', item['ItemManufacturerZip']);
                itemsXml += createXMLElement('ItemManufacturerTaxId', item['ItemManufacturerTaxId']);
                itemsXml += createXMLElement('ItemManufacturerTariff', item['ItemManufacturerTariff']);
                itemsXml += createXMLElement('ItemPreferenceCriterion', item['ItemPreferenceCriterion']);
                itemsXml += createXMLElement('ItemScheduleBNumber', item['ItemScheduleBNumber']);
                itemsXml += createXMLElement('ItemScheduleBQuantity', item['ItemScheduleBQuantity']);
                itemsXml += createXMLElement('ItemScheduleBCode', item['ItemScheduleBCode']);
                itemsXml += createXMLElement('ItemUnitsDisplay', item['ItemUnitsDisplay']);
                itemsXml += createXMLElement('ItemUnitPrice', item['ItemUnitPrice']);
                itemsXml += createXMLElement('ItemLine', item['ItemLine']);
                itemsXml += createXMLElement('ItemTotalQuantity', item['ItemTotalQuantity']);
                itemsXml += createXMLElement('ItemQuantityRemaining', item['ItemQuantityRemaining']);
                itemsXml += createXMLElement('ItemTotalAmount', item['ItemTotalAmount']);
                itemsXml += '</Item>';
            }
        }
    }
    itemsXml += '</Items>';
    return itemsXml;
}


function getShippingRate(quote_id) {
    var quote = record.load({
        type: record.Type.ESTIMATE,
        id: quote_id,
        isDynamic: true
    });

    var itemlinecount = quote.getLineCount('item');

    var bIsItemLineShippingRequest = (quote.getValue('ismultishipto') == 'T');
    var ratesType = quote.getValue('shipping_cost_function');
    if (!bIsItemLineShippingRequest && (ratesType == null || ratesType.length == 0)) {
        invalidateShippingCharge();
        return true;
    }
    var destCity = quote.getValue('shipcity');
    var destState = quote.getValue('shipstate');
    var destZip = quote.getValue('shipzip');
    var destCountry = quote.getValue('shipcountry');
    var shipMethod = quote.getValue('shipmethod');
    var currency = quote.getValue('currency');
    var isResidential = quote.getValue('shipisresidential');
    var isthirdpartyacct = 'F';
    var thirdpartyacct = '';
    var thirdpartycarrier = '';
    var items = new Array();
    var totalWeight = 0;
    var iladdrcount = -1;
    if (bIsItemLineShippingRequest) {
        iladdrcount = quote.getLineCount('iladdrbook');
    }
    for (var i = 0; i < itemlinecount; i++) {
        var itemtype = quote.getSublistValue('item', 'itemtype', i);
        var bFulfillable = quote.getSublistValue('item', 'fulfillable', i);
        if (!isFulfillableItem(itemtype, bFulfillable)) {
            continue;
        }
        var nkey = quote.getSublistValue('item', 'item', i);
        var weight = quote.getSublistValue('item', 'weightinlb', i);
        var quan = quote.getSublistValue('item', 'quantity', i);
        var amount = quote.getSublistValue('item', 'amount', i);
        var excludeFromRateRequest = quote.getSublistValue('item', 'excludefromraterequest', i);
        var location = parseInt(quote.getSublistValue('item', 'location', i));
        if (isNaN(location)) {
            location = getTranLocation();
        }
        if (weight == null || isNaN(weight) || weight.length < 1 || weight < 0) {
            weight = 0;
        }
        if (quan == null || isNaN(quan) || quan.length < 1 || quan < 0) {
            quan = 0;
        }
        if (amount == null || isNaN(amount) || amount.length < 1 || amount < 0) {
            amount = 0;
        }
        var nQuantity = parseInt(quan);
        if (bIsItemLineShippingRequest) {
            var itemshipaddr1 = '';
            var itemshipaddr2 = '';
            var itemshipcity = '';
            var itemshipstate = '';
            var itemshipzip = '';
            var itemshipcountry = '';
            var itemshipisresidential = '';
            var itemshipaddrkey = quote.getSublistValue('item', 'shipaddress', i);
            for (var iladdridx = 0; iladdridx < iladdrcount; iladdridx++) {
                var iladdrbookkey = quote.getSublistValue('iladdrbook', 'iladdrinternalid', iladdridx);
                if (itemshipaddrkey == iladdrbookkey) {
                    itemshipaddr1 = quote.getSublistValue('iladdrbook', 'iladdrshipaddr1', iladdridx);
                    itemshipaddr2 = quote.getSublistValue('iladdrbook', 'iladdrshipaddr2', iladdridx);
                    itemshipcity = quote.getSublistValue('iladdrbook', 'iladdrshipcity', iladdridx);
                    itemshipstate = quote.getSublistValue('iladdrbook', 'iladdrshipstate', iladdridx);
                    itemshipzip = quote.getSublistValue('iladdrbook', 'iladdrshipzip', iladdridx);
                    itemshipcountry = quote.getSublistValue('iladdrbook', 'iladdrshipcountry', iladdridx);
                    itemshipisresidential = quote.getSublistValue('iladdrbook', 'iladdrshipisresidential', iladdridx);
                    break;
                }
            }
            var itemshipmethkey = quote.getSublistValue('item', 'shipmethod', i);
        }
        var nUnits = 0;
        var nWeight = parseFloat(weight);
        if (!isNaN(nQuantity) && !isNaN(nWeight)) {
            weight = nWeight * nQuantity;
            totalWeight += weight;
        }
        if (amount && quan) {
            var itm = {};
            itm['ItemQuantity'] = quan;
            itm['ItemAmount'] = amount;
            itm['ItemWeight'] = weight;
            itm['ItemKey'] = nkey;
            itm['ItemLocation'] = location;
            itm['ItemUnits'] = nUnits;
            itm['ItemType'] = itemtype;
            itm['ItemExcludeFromRateRequest'] = excludeFromRateRequest;
            if (bIsItemLineShippingRequest) {
                itm['ItemShipAddrKey'] = itemshipaddrkey;
                itm['ItemShipAddr1'] = itemshipaddr1;
                itm['ItemShipAddr2'] = itemshipaddr2;
                itm['ItemShipCity'] = itemshipcity;
                itm['ItemShipState'] = itemshipstate;
                itm['ItemShipZip'] = itemshipzip;
                itm['ItemShipCountry'] = itemshipcountry;
                itm['ItemShipIsResidential'] = itemshipisresidential;
                itm['ItemShipMethKey'] = itemshipmethkey;
            }
            itm['Name'] = nlapiGetLineItemText('item', 'item', i);
            itm['ItemDescription'] = quote.getSublistValue('item', 'description', i);
            itm['Quantity'] = quote.getSublistValue('item', 'quantity', i);
            itm['ItemUnitPrice'] = quote.getSublistValue('item', 'rate', i);
            items.push(itm);
        }
    }
    if (ratesType == 'fedexRealTimeRate') {
        var fedexServiceName = quote.getValue('fedexservicename');
        if (totalWeight > getFedExMaxPackageWeight(fedexServiceName)) {
            if (!(fedexServiceName == 'FEDEX1DAYFREIGHT') && !(fedexServiceName == 'FEDEX2DAYFREIGHT') && !(fedexServiceName == 'FEDEX3DAYFREIGHT') && !(fedexServiceName == 'FIRSTOVERNIGHTFREIGHT') && !(fedexServiceName == 'INTERNATIONALECONOMYFREIGHT') && !(fedexServiceName == 'INTERNATIONALPRIORITYFREIGHT')) {
                alert(getFedExMaxWeightExceededMsg(fedexServiceName));
            }
        }
    }
    var subsidiary = '';
    var tranFxRate = getExchangeRate(quote);
    var entityId = quote.getValue('entity');
    var testId = quote.getValue('testid');
    var nexusId = quote.getValue('nexus');
    var isDefaultRequest = quote.getValue('isdefaultshippingrequest');
    var overrideShippingCost = quote.getValue('overrideshippingcost');
    var shipmentParameters = {};
    var reqXml = createRatesRequestXml('SALESORDER', ratesType, entityId, destCity, destState, destZip, destCountry, shipMethod, null, isResidential, isthirdpartyacct, thirdpartycarrier, false, currency, tranFxRate, subsidiary, bIsItemLineShippingRequest, testId, nexusId, null, items, isDefaultRequest, overrideShippingCost, false, shipmentParameters);

    return reqXml

}

function calculateRate(quote_id) {
    var xmlStr = getShippingRate(quote_id);

    var parameters = JSON.stringify({
        method: 'remoteObject.getShippingRates',
        params: [xmlStr]
    });

    https.post.promise({
        url: '/app/common/shipping/dynshippingxml.nl',
        body: parameters
    })
        .then(
            function (httpsResponse) {
                var objResult = JSON.parse(httpsResponse.body);
                var parser = new DOMParser();
                var xmlDoc = parser.parseFromString(objResult.result, "text/xml");
                var shippingRate = xmlDoc.getElementsByTagName('ShippingRate')[0].childNodes[0].nodeValue;
                alert(shippingRate);
            }
        )
        .catch(
            function onRejected(reason) {
                alert(reason);
            }
        )
}

SuiteScript – Shipping Carriers and Methods Unraveled

Like almost all my posts, this is a simple concept that quickly turned into brain surgery. So let’s dumb this down.

I needed a way to select and set the shipping method on an estimate from a Suitelet. Before I even started coding, I noticed that I had different shipping methods based on my selection of shipping carriers. What I mean by this is that when editing an estimate, when you change the shipping carrier, it blanks the shipping methods dropdown and the choices change. So how does NetSuite know which methods (also known as shipitem) are associated with each of the two carriers? Great question!

Setup >> Accounting >> Shipping — Shows carriers

List >> Accounting >> Shipping Items — Shows shipping methods

I’ll share some code written in SuiteScript 2.1 that can be dropped into debugger. When you run it, you’ll see the list of all shipping methods. It is not limited by carrier and in fact the carrier field is blank. Why? Who knows?

However, what I learned was that any custom shipping methods (shipitem) gets associated with the FedEx/USPS/more carrier. AND… when you change the shipping method, it automatically changes the carrier. So set to one of the UPS methods and the carrier magically becomes UPS. Set to any method not prefixed with UPS, and the carrier becomes FedEx/USPS/more.

In an estimate, after changing the method to any method other than one of the UPS methods, and inspect it using SuiteScript Field Explorer (a Chrome plugin), you’ll see the carrier is set to “nonups”.

So it would appear that the two carriers (under the hood) are UPS and “Other”!

Here is my code. The commented section at the bottom was my great revelation. Changing the method automatically changes the carrier. Be sure to select script type of 2.1 when you run this in debugger.

require(['N/search', 'N/record'],
    (search, record) => {
        log.debug('Start');

        let mySearch = search.create({
            type: 'shipitem',
            columns: [
                search.createColumn({ name: 'internalid'}),
                search.createColumn({ name: 'carrier', sort: 'ASC'}),
                search.createColumn({ name: 'itemid', sort: 'ASC'})
            ],
            filters: [
                ['isinactive', 'is', false]
            ]
        })

        let myPages = mySearch.runPaged({ pageSize: 1000 });
        for (let i = 0; i < myPages.pageRanges.length; i++) {
            let myPage = myPages.fetch({ index: i });
            myPage.data.forEach(
                function (result) {
                    let internalid = result.getValue('internalid');
                    let carrier = result.getValue('carrier');
                    let itemid = result.getValue('itemid');
                    log.debug(`${carrier}: ${itemid}: ${internalid}`)
                }
            )
        }


        // var rec = record.load({
        //     type: record.Type.ESTIMATE,
        //     id: xxxxxxxx,
        //     isDynamic: false
        // });

        // rec.setValue('shipmethod', xxx);

        // rec.save();

        log.debug('Done');
    }
)

SuiteScript 2.0 – “An unexpected SuiteScript error has occurred” with Array.sort()

Here is another coding challenge that set me back more time than I would have liked. Passing an array of objects to the Array.sort() function worked most of the time, but not always. And when it blew up, the error was completely unhelpful.

Please note this is server-side code. Client-side code runs in a different JavaScript engine. In my case it was running inside a Suitelet written in SuiteScript 2.0. I did NOT try this same problem in SuiteScript 2.1 which gets a completely new JavaScript engine and ES6. Perhaps it might have worked. Moving from 2.0 to 2.1 requires also updating libraries. That was too much work. Here was my basic problem:

var transactions = [
   {
      salesrep: salesrep_id,
      salesorder_total: salesorder_total
   },...
   /* In my case, there were less than 200 
      records in this array when it failed.
      This was far short of the number of 
      records it processed successfully.
      The only thing I noticed about this 
      particular set of records which caused 
      the failure was the number of records 
      where salesorder_total was zero. 
      Not all, but more than a few! */
]

var sortedbyTotalDescending = transactions.sort(
      function(a, b) {
            return a.salesorder_total <= b.salesorder_total ? 1 : -1;
      }
);

This worked correctly for months. However, there were certain times when it blew up. This is what solved the problem. And don’t ask me why. I’m clueless. It should have. Please direct your questions to NetSuite. I believe it is a bug in their SuiteScipt 2.0 JavaScript engine!

/* This one works! */
var sortedbyTotalDescending = transactions.sort(
      function(a, b) {
            if (a.salesorder_total == b.salesorder_total) return 0;
            return a.salesorder_total < b.salesorder_total ? 1 : -1;
      }
);


SuiteScript – Modifying Addresses

Understanding how to edit addresses under a customer turned out to be a bit tricky. Here is how I did it.

First, you can add custom fields, but to do that, you need to go to

Customization >> Forms >> Address Forms

Customize the “Standard Address Form.” Assign a country or countries to your form. This is important if you plan to use custom fields in an address. The form is where you add them. Custom fields will then be available to addresses in the assigned countries.

Next, it is important to understand that addresses are a subrecord. The sublist is called “addressbook” and is indexed by “addressbookaddress”. This will make more sense when you review the code below. You read the sublist, and then the associated subrecord under it. Here are the standard fields in the sublist for “addressbook”.

Label – not unique and if omitted becomes Address Line 1
defaultbilling – must be unique to one address
defaultshipping – must be unique to one address
isresidential – can apply to any or all addresses

Under each address is a subrecord that contains these fields:

Attention
Addressee
Address Line 1
Address Line 2
Address Line 3
City
State
Zip
Any custom fields that you’ve created

Here is a code example that demonstrates how to update addresses. The variable “line” corresponds with a zero-based index of the address as it appears in the Address tab under a customer. The variable “customer_id” is the internal ID of the customer and all assigned values are coming from variables which you’d set prior to running this code.

    /**
    * Load the record to gain access to the Address sublist 
    */
    var rec = record.load({
        type: record.Type.CUSTOMER,
        id: customer_id,
        isDynamic: false
    });

    if (/* you need to insert an address, do it here. You decide! */) {
        rec.insertLine({
            sublistId: 'addressbook',
            line: 0
        });
        line = 0;
    }

    /**
    * Modify those fields present in the Address sublist lines
    */
    rec.setSublistValue({
        sublistId: 'addressbook',
        fieldId: 'label',
        value: label,
        line: line
    });

    rec.setSublistValue({
        sublistId: 'addressbook',
        fieldId: 'defaultbilling',
        value: defaultbilling,
        line: line
    });

    rec.setSublistValue({
        sublistId: 'addressbook',
        fieldId: 'defaultshipping',
        value: defaultshipping,
        line: line
    });

    rec.setSublistValue({
        sublistId: 'addressbook',
        fieldId: 'isresidential',
        value: is_residential,
        line: line
    });


    /**
    * Load the sublist record, which holds all the actual address fields
    */
    var subrec2 = rec.getSublistSubrecord({
        sublistId: 'addressbook',
        fieldId: 'addressbookaddress',
        line: line
    });

    /**
        * Modify all subrecord fields
        */
    subrec2.setValue({
        fieldId: 'attention',
        value: attention
    });

    subrec2.setValue({
        fieldId: 'addressee',
        value: addressee
    })

    subrec2.setValue({
        fieldId: 'addr1',
        value: addr1
    });

    subrec2.setValue({
        fieldId: 'addr2',
        value: addr2
    });

    subrec2.setValue({
        fieldId: 'addr3',
        value: addr3
    })

    subrec2.setValue({
        fieldId: 'city',
        value: city
    });

    subrec2.setValue({
        fieldId: 'state',
        value: state
    });

    subrec2.setValue({
        fieldId: 'zip',
        value: zip
    });


    /**
    * Save the record
    */
    rec.save();

SuiteTalk 2021.2 Deprecating HMAC-SHA1

I recently got this in an email from NetSuite: “You are receiving this notification because you use the Token-based Authentication (TBA) feature in your account for integrations that use HMAC-SHA1 as a signature method. As of NetSuite 2021.2, the use of HMAC-SHA1 will be deprecated. Before your account is upgraded to 2021.2, you must change your integrations to use HMAC-SHA256 as the signature method.

It sounded ominous, but turned out not to be a huge effort. If you’re like me, this TBA stuff captured my attention just long enough to get it working. So getting back into it took a little refamiliarizing. Hopefully this will save you some time.

In my post SuiteTalk TBA Example in C#, you’ll find the following code snippet. Here are the changes required to move from HMAC-SHA1 to HMAC-SHA256. I commented out the old block to highlight what changed.