SuiteScript 2.0 N/search Module samples
We have heard it at SuiteWorld 2018 echoing both from SuiteCloud Developers as well as the Product Team: The SuiteScript 2.0 N/search Module is both one of the most complex as well as underdocumented modules in SuiteScript 2.0 framework.
In order to promote the marvelous capabilities and efficiencies of the new standard framework, we will dedicate a series of blog posts to the SuiteScript 2.0 and the N/search Module in particular.
What is a search or saved search?
A search is a database query on the NetSuite ERP Oracle Database, defined and executed in Oracle Structured Query Language and as such, allowing for a lot of neat SQL expressions. (There is a neat introduction on the Help Center at https://system.eu1.netsuite.com/app/help/helpcenter.nl?fid=section_n2833020.html, but did you know of distinct() or formatting your results with HTML tags such as <\b> or <\i>?)
There two incredibly powerful things about searches in NetSuite are the Saved Search capabilities:
The Saved Search UI is a single page where Users can create, save, run, publish, schedule, email, or “condition” searches without any SQL.
The database schema of NetSuite is designed for SQL queries. Most telling examples for this are the Transaction and Customer records. The Transaction record contains all transaction types, from Invoices over Supplier Credits to Journal and distinguishes them simply by an attribute field {type}. As a result, you can run a search on the table Transactions and immediately group, summarize, or count financial transactions by GL account, customers, projects, employees – without joining a single table. The Customer record does not just hold actual customers, but also leads, prospects, and projects. This allows to run a saved search across actual financial transactions as well as planned or forecasted transactions or projects associated with customers easily.
How is a search built in SuiteScript?
Shall I load or create saved searches in SuiteScript?
Both SuiteScript frameworks offer to create searches directly in your script (search.create) or load existing saved searches created in the UI (search.load).
We always recommend creating searches in the SuiteScript for the following three reasons:
There is an object less to maintain in your migrations or lists, in this case your saved search list, which will probably shoot past a few hundred saved searches in the first year of using NetSuite, and well beyond a few thousand if you use charge-based modules. When creating searches in SuiteScript, you would not even want to save it.
There is a significant risk that Administrators accidentally or with good intentions edit UI saved search and break your code. Consider that the reordering of saved search results alone may impact results generated if you start working with more complex loops.
Developers working with UI based saved searches frequently ignore the neat tweaks SuiteScript based saved searches allow, specifically around pushing and unshifting columns and filters, depending on script parameters.
A strong argument for loading UI based saved searches may be that it allows Administrators to quickly tweak and edit search parameters loaded into the scripts.
Our message there is clear: A good developer caters to such requirement by parameterising the search in SuiteScript, best by script input parameters published in the User Preferences or the Company General Preferences, depending on the use case.
SIMPLE EXAMPLE
You may want to introduce the option to (re)run a scheduled saved search for an individual entity, such as a Customer or Employee. While also possible with UI based saved searches, Developers may be tempted to simply introduce a respective additional criterion into the saved search in the UI or copying the saved search and reassigning it to an additional script deployment … and frequently forgetting about it at a later stage. Our recommended approach is to define an additional script parameter and publish it in the User Preferences or Company General Preferences. If the input parameter is populated, the script shall push the new parameter into the search in the script, allowing for an entity specific rerun on the fly.
The two search run types and our recommended fireproof way
SuiteScript 2.0
How to make your life easy when retrieving the values from the individual columns of the individual values, specifically for joined records
In SuiteScript 2.0, you still need to loop through every single row (as explained in the previous paragraph), but also every single column of the search result. When leveraging on the searchPages function introduced in the previous paragraph, your callback subfunction will be fed an input object {result} with the following columns and properties.
var columns = JSON.stringify(result.columns) /* {recordType,id,columns,getValue,getText,toString,toJSON,getAllValues} */
var results = JSON.stringify(result) /* { “recordType”: “customrecord_oro_1010_demo_data”, “id”: “6898”, “values”: { “customrecord_oro_1010_demo_data_emp”: [{ “value”: “597”, “text”: “MAVEN” } ], “customrecord_oro_1010_demo_data_emp.altname”: “Maven Enrique”, “customrecord_oro_1010_demo_data_emp.email”: “enrique@cloudmaven.com”, “customrecord_oro_1010_demo_data_emp.subsidiary”: [{ “value”: “2”, “text”: “Cloudmaven Indonesia” } ], “customrecord_oro_1010_demo_data_pro.entityid”: “PRO12333”, “customrecord_oro_1010_demo_data_pro.altname”: “SuiteApp for Advanced Work Calendars”, “customrecord_oro_1010_demo_data_pro.jobtype”: [{ “value”: “2”, “text”: “Development Project with Capitalisation” } ] “formulacurrency1”: “12331.25”, “formulacurrency2”: “12331.25”, “formulacurrency3”: “12859.38”, “formulacurrency4”: “.00”, “formulacurrency5”: “.00”, “formulapercent1”: “2.8287%”, “formulacurrency6”: “348.812” } } */
The tedious thing now is to retrieve the results. The simplest, yet most tedious approach would be to write a getValue/getText function for every column: var empinternalid = result.getValue({name: ‘customrecord_oro_1010_demo_data_emp’}); var empentityid = result.getText({name:’customrecord_oro_1010_demo_data_emp’}); var empemail = result.getValue(‘customrecord_oro_1010_demo_data_emp’); // Leaving away the property key ‘name’ is a common abbreviation method in SuiteScript
Take note, however, that in case of joined records, this approach won’t work without replicating the join in your getValue/getText method: var subinternalid = result.getValue({name: ‘subsidiary’, join: : ‘customrecord_oro_1010_demo_data_emp’}); var subname = result.getText({name: ‘subsidiary’, join: : ‘customrecord_oro_1010_demo_data_emp’});
The following method would not work: var subinternalid = result.getValue(‘customrecord_oro_1010_demo_data_emp.subsidiary’);
Obviously, this stinks, especially if we want to get to the point of looping through the columns. Would we want to write a separate function in case of joined records? Nah! The latter columns give us an idea for a workaround. Instead of joining the records directly in the searchColumn, we simply leverage on formulas!
In the case of subinternalid, we would not write [‘customrecord_oro_1010_demo_data_emp.subsidiary’] or [searchColumn({name:’subsidiary’, join:’customrecord_oro_1010_demo_data_emp’})] but instead, turn the column into a formula such as [searchColumn({name:’formulatext1′, formula: ‘customrecord_oro_1010_demo_data_emp.subsidiary’, label:’empsub’}] so that subsequently, we can retrieve the result with var subinternalid = result.getValue(‘formulatext1’);
This abbreviation doesn’t just save you work, ink, and potential typing errors, but also allows to loop through the columns more easily now. for ( var i = 0 ; i < result.columns.length ; i++ ) { log.debug('In the for result columns loop', 'i = ' + i + ' | result.columns[i] = ' + result.columns[i]); var nameColumn = result.columns[i]['name']; // formulatext1 var labelColumn = result.columns[i]['label']; // empsub var valueColumn = result.getText(nameColumn); // Cloudmaven Indonesia var nameSubstr = nameColumn.substr(0, (nameColumn.length - 1)); switch (nameSubstr) { case 'formulacurrency': valueColumn = parseFloat(valueColumn); break; case 'formulapercent': valueColumn = parseFloat(valueColumn) * 100; break; default: break; } objEmpRow[labelColumn] = valueColumn; } How convenient is that! Take note, though, that with formulas searchColumns, there are a few SuiteScript peculiarities to consider. If you want to learn more about it, join our Telegram chat or drop us a message.
