If you use advanced templates and item groups, you got problems! Although Freemarker is a wonderful templating language, it is not a programming language. Here are some tips and tricks I learned when dealing with item groups and advanced templates.

Rule #1: The scope of variables in Freemarker is somewhat illusive. I’ll spare you the details, but recommend using unique variable names in all functions. You’ll see that in my examples.

Rule #2: Freemarker supports the creation of objects with properties. However, it will not let you update an object after it’s been instantiated.

Rule #3: If you are going to work with numbers, make double sure that whatever you’re working with is a number, variable!0 does not accomplish this.

Below is code taken from an advance template applied to sales orders. This section is where I check to make sure there are items and then looping through them to print each one on the PDFed output.

As I’m looping through all items, you see on line 503 I pass the index of the current item to the getItemDetails() function that returns an object with properties taken from the current line.

Here is where you hit a problem with “Item Group” items. Item group items consist of a “Group” line with a quantity but no unit price or extended price, followed by items of varying types, each with a quantity and unit price, followed by a “Group End” item, again with no quantity or unit price. In order to show the group item correctly with the correct totals, you need to read all items in the group keeping track of the totals yourself. Here is the twist. If the quantity on the group is not 1 and quantities of items in the group are not 1, you’re hosed! In order to get your unit price, you need to calculate the extended price of each line. When done, take the total and divide by the quantity on the original group item. This is unbelievable to me, but the quantities of items in the group already reflect the quantity of the group, BUT can be modified.

As an example, let’s say you have a group of dinnerware with quantity of 2. The items in the group are 4 plates and 4 cups. The group item will show quantity of 2. The quantity of plates in the group will be 8 and the quantity of cups will be 8. The extended price of each line must be calculated as quantity * price. If for some reason, and this is definitely possible once the group is initially added to the order, someone edits the line associated with plates and changes the quantity back to 4, the group’s total unit price and extended price can no longer be calculated by summing the unit prices of all lines and multiplying by the quantity on the group. I hope that’s not too convoluted, but it’s still true! Try it yourself. Groups, unlike Kits, can be modified independent of their definition.

So keep reading…

In line 185, you see a call to getLineDetails, which returns an object. Then lines 192 through 215 “read ahead” looking for the end of the group, keeping track of each line’s extended price (quantity * unit price). When the end of the group is located, the original line, the “Group” line, gets the total extended price. The group’s unit price is then calculated as the extended price divided by the quantity on the group. This “fixes” all the changes made to unit prices and quantities of items in the group after the group was added to the order. Inconceivable (Princess Bride)!

From before, where we called getItemDetails, you see it returns an object with modified properties. It also skips over items inside a group item, calling getGroupDetails to accumulate all details associated with the group into one object with the correct properties to print in that line.

And finally, you’ll see references to updateLine(). You’ll find that you can create objects, but you cannot modify properties of an object once it’s been instantiated. So…. you need to clone the object with the updated property and move forward with the replica. You’ll see how that is done here.

I am unable to give you the code in a form that can be copied. You may have already noticed the missing line numbers and lines through my screenshots. That is because I have removed references to the “secret sauce.” My wife just finished a 9 week cooking class taught be a master chef. She told me she learned that many of the great chefs tweak their recipes ever so slightly in cookbooks, as they don’t want anyone copying them with the result being as good as the original. I don’t consider myself a proud programmer and I’m not trying to water down the lesson, but there are some trade secrets here that you’ll just need to discover for yourself.

Best of luck, and I hope this enlightens both you and me (as I often return to these blog posts as notes). Freemarker is quirky, but it’s all we got!

I just stumbled into this today and it is extremely helpful when editing HTML or JSON in vsCode.

I was editing a NetSuite Advanced Transaction Template which is written in Freemarker. There is no interactive debugging of templates in NetSuite, so I wanted to show variables as part of the page. I wanted variable names, followed by the variable’s value. Here’s how easy this was, and I hope this makes it easy to see other uses for the multi-line copy and paste functionality in vsCode.

First, here is how it works. Then I’ll share the keystrokes.

• First, I pasted in the variables. I actually used the same multi-line edit capabilities to create the list from Freemarker <#assign> statements higher up in the page.
• [ctl] + [alt] + [shift] to select the lines for multi-edit. Of course you [down arrow] to include multiple lines.
• [home] to move to the beginning of each line.
• [shift] + [end] to highlight the entire line.
• [ctl] + c to copy within the line.
• [end] to jump to the end of the line on all lines
• I typed “: ${“, which completed the brackets and left the cursor in between brackets.
• [ctl] + v to paste the duplicate variable names
• [end] to jump to the end of each line
• I typed “<br />”

I could see this being extremely handy when creating arrays of JSONized objects in JavaScript, or tables in HTML. I hope this helps all my fellow lazy typists to spend more time writing code and less time formatting code.

No extensions! Native commands in vsCode can sort lines of text in ascending or descending order.

Highlight the lines you wish to sort.

Hit F1

Type “sort” and select sort order from the dropdown

I’m still scratching my head over this one. How can you link system notes to transaction line items? I found it’s possible, but not intuitive.

Since discovering SuiteQL, I’ve tried to use it instead of saved searches. The advantage is it allows multi-level joins, where saved searches only allow single level (one and only one level) of joins.

Querying system notes in SuiteQL looks something like this.

select
*
from
(
    select
        t.id as tran_id
        , t.tranid as tran_num
        , n.date
        , case n.type when 2 then 'Set' 
            when 4 then 'Change' 
            else to_char(n.type) 
          end as change_type
        , case n.context 
            when 'SLT' then 'Script (Restlet)' 
            when 'UIF' then 'User Interface' 
            when 'WST' then 'Web Store' 
            else n.context 
          end as change_context 
        , n.field
        , n.oldvalue
        , n.newvalue
    from systemnote n
        inner join transaction t 
        on t.id = n.recordid 
        and t.type = 'SalesOrd' 
        and n.date > '10/23/2022'
        and BUILTIN.DF(t.status) in
        (
            'Sales Order : Pending Fulfillment',
            'Sales Order : Pending Billing/Partially Fulfilled',
            'Sales Order : Partially Fulfilled'
        )
    where 1=1
    and n.oldvalue != n.newvalue
    and n.type in (2,4) -- Set or Changed
) as dtbl
order by tran_num, n.date desc

It works great, but has the limitation of not being able to link the system note directly to the transaction line. I searched to the end of the internet, and I don’t see anyone who’s solved this problem, not even Tim Dietrich, the PL/SQL Guru. However, the relationship exists in a saved search. Marty Zigman points this out in one of his blog articles. Thank you, both of you!

Of course you can always use SuiteQL to query your saved search. However, it sure would be nice if NetSuite shared the secret foreign key relationship between transaction lines and system notes in their Schema Browser. I noticed some views described in the Schema Browser “Connect Browser” tab, but I could not translate that into anything that worked in PL/SQL.

If you know more about this than I do, please share it with the rest of us. Respond to this post and I’ll include it and credit you.

I needed a way to move data from NetSuite to Magento. Here is a simple example that creates an attribute set in Magento. This example runs in a NetSuite debugger session. It is written in SuiteScript 2.1.

To create your token in Magento. Login in as admin. Go to
System >> Integrations >> Add New Integration

Once you’ve added your integration, Magento will create an Access Token. This is your Bearer Token.

Use the Bearer Token in your header. Refer to the Magento 2 Admin REST endpoint’s documentation to correctly build the body. Here is example code.

require(['N/https'],
    (https) => {

        var header = {
            'Authorization': 'Bearer [your token here]',
            'Accept': 'application/json',
            'Content-Type': 'application/json; charset=utf-8'
        };

        var body = JSON.stringify(
            {
                "attributeSet": {
                    "attribute_set_name": "[Your attribute set name here]"
                },
                "skeletonId": 4
            }
        );

        https.post.promise({
            url: encodeURI('https://[Your Magento base domain here]/rest/default/V1/products/attribute-sets'),
            body: body,
            headers: header
        })
            .then(function (response) {
                log.debug({
                    title: 'Response',
                    details: response
                });
            })
            .catch(function onRejected(reason) {
                log.debug({
                    title: 'Invalid Request: ',
                    details: reason
                });
            })


    }
)

I needed away to roll up entries from a custom table to display as a single value under an item record. What a meticulous and tedious pain this was. I can’t think of any more applicable adjectives, or I’d use them. I needed a checklist!

To be fair, NetSuite is great about letting you create a saved search to display summary results in what they call a “Calculated Custom Field”. It displays as if the value was actually stored with the entity, but it’s not, it’s built on the fly. In my case, I needed to show the SuiteCommerce Advanced base image from the list of images associated with any item.

The task at hand presented multiple problems. I’ll spare you the details of the first problem, which was an inability to write a saved search listing only images associated with a single item. I learned this is not supported in NetSuite, so I had to built my own custom table. Note: I used a SuiteQL query against the ItemImage table (Thank you Tim Dietrich) along with a Map/Reduce script. This let me build a copy of the ItemImage table which WAS accessible from a Saved Search.

This brings me to the list of all quirky things required show the one file name under an item. Here is that list!

• Identify the standard or custom table you wish to pull your value from. In my case it was a custom table called “Item Images”.
• Create a saved search against your source table.
  • Make your search public.
  • Add any criteria you like. In my case it was “Base Image” = true.
  • Include the entity type as the first field in your results.
  • Make sure the results are “Summary Results”. In my case I used MINIMUM File Name
  • Create an Available filter that limits results to a single entity and make sure to check “Show in Filter Region”.

• Create a new Custom Field.
  • Pick the field type that is correct for the rolled up value you will display from your saved search.
  • Uncheck “Store Value”. This value will not be stored, but calculated on the fly.
  • Under “Validation and Defaulting”, select the saved search your just created.

Tying up lose ends, I’ll show the definition of my custom table.

Hopefully, in the future, if we all follow this checklist, we’ll be thinking happy thoughts about NetSuite and not letting fly any uncomplimentary adjectives. Cheers!

I feel like every time I post, the answer is simple, but the documentation is terrible. The task: Call Magento API from a JavaScript program in Node.js.

• Login to your Magento Admin account at https://%5Byour-domain%5D/admin
• Create an integration: System >> Integrations >> Add New Intgration
• Copy the Access Token for use later in the header of your get request

Here is the code in a format you can cut and paste.

const axios = require('axios').default;

axios({
    method: 'get',
    url: 'https://[My URL Here]/rest/default/V1/products' +
            '?searchCriteria[currentPage]=1&searchCriteria[pageSize]=5',
    responseType: 'text/json',
    timeout: 1000,
    headers: {'authorization': 'Bearer [My Access Token Here]'}
  })
    .then(function (response) {
      console.log(JSON.stringify(response.data));
    })
    .catch(function(err) {
        console.log(err.message);
    })
    .finally(
        function() {
            console.log('finally');
        }
    )

ES6 got me! The same code in SuiteScript 2.0 passes all syntax checks, but when converted to SuiteScript 2.1 fails to upload to NetSuite. What the heck? I’m not even going to try to explain this. I’m just going to show the problem and the fix with examples.

This PASSES syntax checks but FAILS to upload to NetSuite:

This PASSES syntax checks and SUCCEEDS to upload to NetSuite:

The only difference is moving the reference to N/format inside the main function. Apparently this is something the NetSuite JavaScript compiler frowns upon, but the vsCode syntax checker is OK with.

My complaint is with the NetSuite error message. It’s ugly! I formatted it here to make easier to parse with the eyes. If you are reading this, you already know it comes out as a jumbled mess on the screen. Argh!

Here is the actual text you’d see on the screen, again I formatted it to make it readable. I’m including it in text to help find this article if you experience this error. It’s exactly what you see above.

Notice
 
Fail to evaluate script:
{
    "type": "error.SuiteScriptError",
    "name": "SUITESCRIPT_API_UNAVAILABLE_IN_DEFINE",
    "message": "All SuiteScript API Modules are unavailable while executing your define callback.",
    "stack": [
        "Error\n at /SuiteScripts/Logic/your_suitelet_here.js:24:32"
    ],
    "cause": {
        "type": "internal error",
        "code": "SUITESCRIPT_API_UNAVAILABLE_IN_DEFINE",
        "details": "All SuiteScript API Modules are unavailable while executing your define callback.",
        "userEvent": null,
        "stackTrace": [
            "Error\n at /SuiteScripts/your_suitelet_here.js:24:32"
        ],
        "notifyOff": false
    },
    "id": "",
    "notifyOff": false,
    "userFacing": true
}

I’m a developer who bounces back and forth between working at the office and working from home. I have a full developer setup in both places. I know… “First world problems!” Because I work in the cloud, publishing JavaScript files to NetSuite, I noticed files were checking in and out of GitHub on both systems, but did not match what I last uploaded to NetSuite. What was going on?

Using remote access from home to my office, I did a couple of tests.

• I trued up source code at home, in the office, and in NetSuite. (WinMerge will compare files in 3 directories).
• I modified one JavaScript file at home and checked it in.
• I pushed changes to the remote repository on GitHub.
• I pulled from my office computer. Changes came through.
• I modified the file again in the office, checked back in and pushed to remote repository.
• Pulled again from home. All was fine. Changes were there.
• I came back a few minutes later on the same home computer and changes were gone!!!!!
• Both systems were saying they were fully up to date with the same repository on GitHub (remote).
• The same file on home and office were not in sync.
• I deleted the local repository on my office computer, and cloned it again outside of OneDrive.
• Interestingly enough, my home computer then prompted with a pull request and proceeded to update the file which had been out of sync, and replaced it with the correct version. Again, this was my home computer! Crazy.

This was such an odd situation, I thought I’d document it. I’ll add, and I have no real understanding of this, my new rule is, “I’m already saving updated source code in the remote repository. What again is the point of OneDrive? So I’m now personally steering clear of OneDrive for my local repositories.” I don’t need any more hassles! I have enough writing software for a living.

I’ve been asked to investigate why our NetSuite system is making so many calls to Avalara to calculate tax. Each call costs, and we make an inordinate number of calls. These notes are mainly for me, but I’m happy to share. If anything in this post is incorrect, please feel free to comment. This is not intended to disparage Avalara, it’s simply notes about how to manage it.

First, I’ve determined that any time transactions (Estimates, Sales Orders and possibly Invoices) are saved, a web service call is made to Avalara to calculate tax. Cha Ching!

Second, and to Avalara’s credit, they offer configuration options for turning off the tax calculation on each type of transaction. So if you don’t need tax on an estimate, just turn it off. However, if you convert an estimate to a sales order, you may want the totals to match, and that requires tax.

Third, if nothing affecting the tax calculation changes, you would expect Avalara to skip the web service call. It does not! Consider this, someone copies an estimate/quote to make a revision. Nothing changes. The shipping address and all items in the quote remain 100% the same as the copied transaction. However, at that instant, the newly created copy isn’t saved until the user clicks Save, Cha Ching! If anything is changed in the transaction, like assigning a second sales rep, anything, Cha Ching! And so on, and so on, and so on. Cha Ching.

Now, here are some notes regarding how to track the web services calls. Open a transaction and save it, without changing anything. NetSuite will prompt to confirm. Say yes. Next, view the AvaTax tab. You’ll see something like this. Check the timestamps, it confirms you just made a webservice call to Avalara! If you click “More…”, you’ll see the response includes your calculated tax amount.

Developer Notes:

Avalara is installed as a bundle and associated scripts live in the file cabinet in “/SuiteBundles/Bundle 1894”.

AvaTax got an upgrade from SuiteScript 1.0 to SuiteScript 2.0 with AvaTax 7.6. If you have not upgraded your Avalara bundle in some time, you should. All code from 7.6 on which affects web services calls has been upgraded and moved to new libraries.

Reviewing Avalara code:

It’s an easy task to browse to “/SuiteBundles/Bundle 1894” and download the entire directly. It comes down as a zipped file which can be expanded and checked into source control.

In AVA_CLI_Transaction.js (the naming conventions make it very easy to find the client-side scripts that handle the Save and Calculate Tax buttons), you’ll find AVA_TransactionSave() and AVA_CalcuateOnDemand() functions. Because they are client-side calls you can evaluate these using Chrome Developer Tools.

Ultimately, calls wind up at TaxLibrary.js AVA_CalculateTax(). It is important to note that this library is not available in VIEW mode, but only in EDIT mode when setting stops in Chrome Developer tools.

And finally, here is where I stopped my research prior to writing this post, you can make a direct call to AVA_CalculateTax() from the console window of Chrome Developer Tools. It looks like this. Open a transaction in EDIT mode. Let it fully load. Then paste this into the console window of Chrome Dev Tools. You can step through the entire process of updating the tax amount, including the web services call.

require(['N/currentRecord', '/SuiteBundles/Bundle 1894/utility/AVA_TaxLibrary.js'],
    function (currentRecord, tax_lib) {
        var cRecord = currentRecord.get();
        var connectionStartTime = new Date();
        debugger;
        tax_lib.AVA_CalculateTaxOnDemand(cRecord, connectionStartTime);
    }
)

This call appears to calculate tax and update your transation (I didn’t actually confirm that the tax amount has been updated). And, it does not log the web services call in the AvaTax tab. That remains to be investigated.

It took time to get this far and I don’t want to lose my research. Thus, this blog article. If anyone picks up here, send me a comment and perhaps you can return the favor by saving me some time. Thanks!