SuiteScript’s ‘N/Search’ filterExpression – Don’t forget it!

This came up yesterday. Here is my note regarding how it works.

If you create a search in SuiteScript 2.0, you can set a filter expression via the filters property.

var mySearch.create({
   type: [whatever type you like],
   columns: [ search.createColumn({ name: 'internalid'... }),
   filters: [
      ['internalid', 'anyof', [more filter stuff here]]
 }

This works great. It accepts the advanced filter, no problem! However, when I load an existing saved search, I cannot submit a filter expression in the same way. Here’s how that works.

var mySearch.load({
   id: [an id of an existing saved search]
};
mySearch.filterExpression = [
   ['internalid', 'anyof' [more filter stuff here]]
];

When you load an existing search and want an advanced filter expression, you must use the filterExpression property!

Transactions may not reflect item setting changes

Here’s a gotcha! Changing a setting on a group item after you’ve added it to a transaction may not get reflected in your transaction going forward.

A perfect example of this is the setting “Display components on Transactions” checkbox in “item Groups”. If you print your transactions using a standard print template, you’ll probably want this checkbox unchecked. This prints just the group item, no subitems.

If you print your transactions using an Advanced PDF/HTML template, you will probably want this checked, so you can add up the total of all subitems and print it as the total for the group.

This become problematic when you switch from a standard print template to an advanced template. This is huge, so don’t miss it!!!!!

Any transaction that had that a group item added to it prior to changing that checkbox, remembers the previous setting. So copying an old transaction where the checkbox was checked yields the same setting on the line item in the copy. HUGE!!!

I’ve wasted way too much time tracking down group items that don’t print correctly after swapping from a basic print template to an advance print template. Hopefully this saves you and me both wasted time tracking this down in the future.

Custom Fields – Propagating Default Values

NetSuite’s ability to link custom fields and propagate default values is amazing. So much so, I wanted to make sure everyone following me knows how it works.

My task was to create a custom field, a dropdown at the customer level. Then add the same field to estimates and sales orders. When new estimates or sales orders are created, set the dropdown to the value of the customer assigned to the new transaction. However, allow the field to be modified after the fact. Then, when an estimate is copied to a sales order, let the modified selection follow to the new transaction. And, if the customer is changed, revisit the selection on same transaction and set it to the default value of the new customer. Crazy! I know.

Instead of writing complicated event scripts, this is easily accomplished when defining the custom field. Simple as that. Here’s the trick.

I added a custom dropdown at the customer level and populated from a list. Next, I created another custom field at the transaction level and assigned it to estimates and sales orders. It also populates from the same list. Here is how I sourced the custom field at the transaction level. It looks back to the customer to get its default value. Then it can be modified at any point thereafter. The most important thing to understand is that checking the “Store Value” checkbox, makes this behave as I’d described above.

Unchecking that checkbox will make this a “Calculated field.” In that case, it will always display whatever value is currently set in the dropdown at the customer level. This is a very important distinction!

Saved Search which includes child customer’s transactions

One of the fantastic things NetSuite supports is parent/child relationships among customers. The problem is figuring out how to write a saved search that leverages that.

I’ve been missing until now a relationship on customer records called “Top Level Parent Fields…” The second relationship is is “Transaction Fields…” Combine those two and you get the missing link (pun intended) which allows you to show all transactions for both the parent company and all its kids.

This is the criteria you’d use to pull one parent-company and all child-company transactions into one result set. If you wanted to reference this from SuiteScript, say inside a Suitelet, you’d remove the “Top Level Parent: Company Name” and add a filter on “Top Level Parent: Internal ID” or {toplevelparent.internalid}. You could also move this filter from Criteria in the Available Filters tab .

Here, my result set includes a list of parent customers followed by the child customer that actually had the transaction. All fields on the transaction record are available. I’ve just pulled a few as an example.

As you continue to create transactions, you will probably want to filter on “Transation Fields: Date” or {transaction.date}, either in the criteria saved with your search, or in the code that references this search.

Here is a simple example of how to filter directly in the saved search.

Suitelet – Custom Autocomplete Field

Leveraging jQuery UI autocomplete turned out to be amazingly easy and added functionality that is not present in the standard NetSuite autocomplete control.

Here are the steps I followed along with some sample code.

  1. Add the jQuery UI library to your Suitelet
  2. Create a Restlet specific to the field you wish to have autocomplete on
  3. Add the autocomplete feature to an <input type=”text”> control in your form

I’m not going to cover each of these steps in details. I’ll say that adding the jQuery UI library is explained in SuiteAnswers. I did it by uploading the jQuery UI minimized library (a *.js file) to the NetSuite file cabinet and then adding it as a reference in an INLINEHTML control in my form.

When you build your Suitelet, you’ll do something like this…

            var suiteletControl =
                form.addField({
                    id: 'custpage_newcontrol',
                    type: serverWidget.FieldType.INLINEHTML,
                    label: 'Dynamic HTML'
                });

            suiteletControl.defaultValue = '[Your HTML goes here]';

Be sure to put your <input type=”text” id=”whatever” /> control here. Then add some JavaScript that looks like this…

                var whateverElement = $('#whatever');
                whateverElement.autocomplete({
                    source: '/app/site/hosting/restlet.nl?script=[restlet internalid]&deploy=1',
                    minLength: 3
                });

This JavaScript can live directly in a <script> tag in your HTML above. Be sure to run it after the full form has loaded. If you don’t know how to do that, you shouldn’t be attempting any of this. Sorry, I’m really not a jerk!

Finally, you’ll need a restlet. My restlet is looking up vendors that match the text in the “whatever” textbox. The minLength property of the autocomplete object shown above tells jQuery AutoComplete to omit calling the Restlet until there is at least 3 characters in the textbox.

/**
 * @NApiVersion 2.0
 * @NscriptType restlet
 */
define(['N/search', 'N/log'],
    function (search, log) {

        function GET(context) {
            var term = context.term;

            var mySearch = search.create({
                type: search.Type.VENDOR,
                columns: [
                    search.createColumn({ name: 'entityid', sort: 'ASC'}),
                    search.createColumn({ name: 'isinactive'})
                ],
                filters: [
                    ['entityid', 'startswith', term ]
                ]
            })

            var results = mySearch.run().getRange(0,25);
            var suggestions = new Array();
            results.forEach(
                function(result) {
                    var entityid = result.getValue('entityid');
                    var inactive = result.getValue('isinactive') ? ' (inactive)' : '';
                    var label = entityid + inactive;
                    suggestions.push({
                        label: label,
                        value: entityid
                    })
                }
            )

            return JSON.stringify(suggestions);
            
        }

        function POST(context) {

        }

        function PUT(context) {

        }

        function DELETE(context) {

        }

        return {
            get: GET,
            post: POST,
            put: PUT,
            delete: DELETE
        }
    }
)

And finally, in my example, I’m showing how to return a list of vendors, which includes inactive vendors. However, if they are inactive, I’ll append the string ‘ (inactive)’ to the vendor’s company name in the dropdown list. However, the value (as opposed to the label) property in the object returned from the Restlet includes just the vendor’s entityid. That is what gets placed in the textbox when the user selects a row in the autocomplete dropdown list. Super simple, once you get this simple example working in your copy of NetSuite. You can build on this to do things like showing items that are in-stock, etc.

SuiteScript 2.0 – Queuing Background Tasks

I have tasks that use too many resources to run in the foreground. So I had previously pushed them to the background and expected them to queue. However, they didn’t. If a map/reduce script was already running and another was submitted, the second instance simply failed.

Here’s where I went wrong. When kicking off my background map/reduce script, I got too specific. If you submit your background job like this, you can force the job/task to queue. If you add the optional deploymentId you cannot.

var myTask = task.create({
     taskType: task.TaskType.MAP_REDUCE,
     scriptId: 'customscript_map_recalc',
     params: { custscript_customer: customer_id }
 });
 var task_id = myTask.submit();

To force a background job to queue, you need to create multiple deployments for the same script. Then when you submit the job, leave off the deploymentId and the system will select a deployment ID for you.

Of course you have the option of purchasing additional NetSuite processors, which will allow you to move beyond the 2 you get by default. But most of us are limited to 2. And jobs can be configured to run on one or multiple processors. In my case, I need operations to occur in a specific order, so I limit my jobs to one.

In either case, if you don’t setup your jobs to queue and one is already running, the next one fails.

The easiest way I found to create multiple deployments is to “Save As” your initial deployment you create when you save your script for the first time.

Skip Freemarker by Delivering Saved Search Results in a Suitelet

It took me a number of years working as a NetSuite developer to stumble onto this. It’s worth a look.

As a developer, you have the option of writing a saved search and creating your own custom advanced PDF/HTML template to print it. It’s a fantastic option. However, it also requires knowledge of the Freemarker templating language.

So here’s my alternative…

First, the saved search. Code it any way you like. Supply custom titles or not. There are no restrictions.

And here is the end-in-mind, a formatted table included in a Suitelet. Obviously, I’ve burred the data. Can’t share that. Sorry!

Here is now I read the results from the Saved Search.

And here is how I format the results. You’ll need an INLINE_HTML field on your form to hold the resulting markup. I’m counting on you knowing how to do that.

Here is the code in form you can cut and paste.

        function getIssues() {
            var issues = new Array();

            var mySearch = search.load({
                id: 'customsearch_sales_order_issues_2'
            });

            var myPages = mySearch.runPaged({ pageSize: 1000 });

            for (var i = 0; i < myPages.pageRanges.length; i++) {
                var myPage = myPages.fetch({ index: i });
                myPage.data.forEach(
                    function (result) {
                        var issue = {};
                        mySearch.columns.forEach(
                            function (col, index) {
                                issue['column_' + index] = { label: col.label, text: result.getText(col), value: result.getValue(col) }
                            }
                        )
                        issues.push(issue);
                    }
                );
            }
            return issues;
        }
        function formatIssues(issues) {
            var html = new Array();
            html.push('<table class="RPT">');
            html.push('<thead>');

            if (issues.length > 0) {
                var issue = issues[0];

                html.push('<tr>');
                for (var i = 0; i < 20; i++) {
                    if (issue.hasOwnProperty('column_' + i)) {
                        var sortType = isNaN(issue['column_' +i].text || issue['column_' +i].value) ? 'string' : 'float';
                        html.push('<th data-sort="'+sortType+'">' + issue['column_' + i].label + '</th>');
                    }
                }
                html.push('</tr>');
            }

            html.push('</thead>');
            html.push('<tbody>');

            issues.forEach(
                function (issue) {
                    html.push('<tr>');
                    for (var i = 0; i < 20; i++) {
                        if (issue.hasOwnProperty('column_' + i)) {
                            var vAlign = isNaN(issue['column_' +i].text || issue['column_' +i].value) ? 'left' : 'right';
                            html.push('<td align="' + vAlign + '">' + (issue['column_' + i].text || issue['column_' + i].value) + '</td>');
                            break;
                        }
                    }
                    html.push('</tr>');
                }
            )


            html.push('</tbody>');
            html.push('</table>');

            return html.join("\n");
        }

Whoops! Just noticed this… Here is a fix.

NetSuite Upgrade Breaks NetSuite-Sync

I’ve written about this before. However, if you follow me, here is one more reminder.

This morning, after our 2020.2 upgrade overnight, I began writing JavaScript code and hit my normal keystrokes [ctl] + [alt] + u to upload my JS file to the server. To my surprise, I found Netsuite-Sync broken. Since I’ve blogged about this in the past, the answer was right here in my own blog.

Here’s the fix: If you have a Release Preview account, when you are prompted to select a role, do NOT pick your production Netsuite Administrator role. Select another role with appropriate permissions to your file cabinet. That’s it. Simple as that!

https://followingnetsuite.com/2019/09/05/vscode-netsuite-sync-breaks-with-release-preview-2019-2/

SuiteScript – Mainline filter not working

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

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

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

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

SuiteScript – Referencing Columns in a Saved Search

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

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

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

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

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

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

result.getValue(mySearch.columns[4])

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