Streamlining Netsuite Data: A Seamless Data Retrieval Integration

In the ever-evolving landscape of technological innovation, efficiency and performance are paramount. At Taboola , we've tackled the complexities and limitations posed by the NetSuite API head-on, crafting an ingenious integration process that not only overcomes challenges but also enhances speed and accuracy. In this article, we delve into the journey of creating an integration process that leverages the power of Netsuite's saved searches, N/Task module, and Workato automation to revolutionize data retrieval.

The Netsuite API, while a powerful tool, presents its fair share of challenges. Complex and with suboptimal performance, it can become a bottleneck for efficient data extraction. Furthermore, the limitation of retrieving only 200 records per page result necessitates multiple iterations to retrieve extensive data sets.

In response to these challenges, we set out to create a dynamic process that not only simplifies data extraction but also boosts performance. Our solution revolves around utilizing Netsuite's saved searches and harnessing the capabilities of the N/Task module.

Step 1: Dynamic Saved Searches

To address the complexities of the Netsuite API, we designed a script that dynamically generates saved searches. This eliminates the need for pre-created searches and empowers us to tailor search criteria programmatically. This dynamic approach aligns with the agile nature of today's data-driven landscape.

define([ 'N/search','N/log','N/query'
function( search,log,query) {
? ? function execute() {
? ? ? var searchObj? = search.create({
? ? ? ? ?type: "Backstage_Account",
? ? ? ? ?filters:
? ? ? ? ?[
? ? ? ? ? ? ["internalidnumber","lessthan","1000"]
? ? ? ? ?],
? ? ? ? ?columns:
? ? ? ? ?[
? ? ? ? ? ? search.createColumn({name: "formulatext",formula: "case when {custentity_tb_currency_translate} = 'T' then 'T' else 'F' end",label: "bs_currency_translation"}),
? ? ? ? ? ? search.createColumn({name: "formulatext",formula: "TO_CHAR({datecreated}, 'YYYY-MM-DD HH24:MI:SS')",label: "create_date"}),
? ? ? ? ? ? search.createColumn({name: "formulatext",formula: "UPPER({credithold})",label: "credithold"}),

? ? ? ? ? ? search.createColumn({name: "currency", label: "Primary Currency"}),
? ? ? ? ? ? search.createColumn({name: "internalid", label: "Internal ID"}),
? ? ? ? ? ? ...
? ? ? ? ? ? ...
? ? ? ? ? ? ...
? ? ? ? ?]
? ? ? });

? ? ? ? // replace previous run saved search?
? ? ? ? try{
? ? ? ? ?search.delete({id: 'customsearch_backstage_accounts_full_data'});
? ? ? ? } catch(e){
? ? ? ? ?// previous not found
? ? ? ? }
? ? ? ??
? ? ? ? searchObj.id="customsearch_backstage_accounts_full_data";
? ? ? ? searchObj.title="customsearch_backstage_accounts_full_data";
? ? ? ? var newSearchId = searchObj.save();
? ? }
? ??
? ? return {
? ? ? execute: execute
? };
? ??
});        

Step 2: Streamlined Data Retrieval

By employing the N/Task module, we've significantly improved data retrieval speed. Our script efficiently pulls records based on the dynamic saved search and compiles the results into a CSV file. The entire process is executed within seconds, ensuring near real-time data availability.

define(['N/task','N/runtime'],
function(task,runtime) {
?function execute(context) {
? ? ?try {
? ? ? ? let myTask = task.create({taskType: task.TaskType.SEARCH})
? ? ? ? let fileId = parseInt(runtime.getCurrentScript().getParameter({ name: 'custscript_search_2_file_fileid' }));
? ? ? ? let savedSearchId = parseInt(runtime.getCurrentScript().getParameter({ name: 'custscript_search_2_file_searchid' }));
? ? ? ? log.debug('parameters', `fileId: ${fileId},? searchId: ${savedSearchId} `);


? ? ? ? myTask.fileId = fileId;
? ? ? ? myTask.savedSearchId = savedSearchId;
? ? ? ??
? ? ? ? // Submit the search task
? ? ? ? let myTaskId = myTask.submit();


? ? ? ? log.debug(`myTaskId: ${myTaskId}`)
? ? ? ? let taskStatus = task.checkStatus({
? ? ? ? ? ? taskId: myTaskId
? ? ? ? });


? ? ? ? log.debug(`taskStatus.status before: ${taskStatus.status}`);


? ? ? ? if (taskStatus.status === task.TaskStatus.COMPLETE) {
? ? ? ? ? ? log.debug(`taskStatus.status inside: ${taskStatus.status}`);
? ? ? ? }


? ? ? ? log.debug(`taskStatus.status end: ${taskStatus.status}`);




? ? ?} catch (e) {
? ? ? ? log.debug(`Failed execution, error: ${e}`);
? ? ?}
?}
?return {
? ? ?execute: execute
?};
});?        

*Note: Expanding Control with Prepared Saved Searches

In addition to our dynamic saved searches approach, we've grown into processes where data retrieval is facilitated by leveraging prepared saved searches as a source. This dual solution offers a remarkable advantage to Netsuite administrators, granting them comprehensive control over their saved searches while seamlessly channeling the results to external systems.

Step 3: File Cabinet Storage

The generated CSV file is seamlessly stored in Netsuite's file cabinet, preserving data integrity while maintaining a clutter-free environment. This storage approach not only ensures data availability but also simplifies subsequent steps in our integration process.

Step 4: Automating with Workato

To complete the integration journey, we've integrated Workato, into our workflow. Workato detects the presence of the newly generated CSV file in the file cabinet and seamlessly transfers it to its designated destination. This step ensures that data is effortlessly delivered to where it's needed most.

The Result: Seconds to Success

Through our innovative integration process, we've turned the complex and time-consuming Netsuite API retrieval into a streamlined, near-instantaneous endeavor. By bypassing the limitations of manual search creation, optimizing data retrieval with N/Task, and automating the final data delivery with Workato, we've significantly enhanced performance and efficiency.

Conclusion

Innovation thrives at the intersection of challenges and solutions. At Taboola, our integration process exemplifies this principle by transforming the way we interact with the Netsuite API. By utilizing dynamic saved searches, leveraging the N/Task module, and orchestrating automation with Workato, we've achieved remarkable results – revolutionizing the speed, accuracy, and efficiency of data retrieval. This journey stands as a testament to our commitment to continuous improvement and optimization in the realm of data integration.