This article is relevant if you are looking to keep a marketing website updated with business data from NetSuite. It also provides insight into using Saved Search and SuiteQL to create specialized JSON data structures.
Background
When developing promotional websites, companies often handle marketing independently from their business operations. Today, marketing leadership typically opts for a content management system (CMS) like WordPress paired with a simple technical infrastructure made simpler by a range of third-party hosting services. The focus then shifts to designing the site, which generally involves graphics, layout, and content.
It is much less common to see marketing strategies that leverage the data already housed in NetSuite ERP. However, following NetSuite’s vision of a unified database for the entire business, why not extend that approach to marketing? NetSuite offers Lead Generation Forms and Promotional Email to help, and while it also provides eCommerce solutions, it is more common to see platforms like WordPress managing promotional websites. So, one does not generally think of using NetSuite to produce a corporate website.
But what if we want to feature data from NetSuite directly on a promotional website? This article discusses Prolecto’s approach to this challenge and aims to inspire marketing and technology leaders to integrate content maintained in NetSuite better.
Using Prolecto Accelerator Templates as an Example
Since 2008, when we focused our entire efforts on NetSuite, our firm has built over 75+ accelerator templates. These templates are NetSuite software bundles provided to clients license-free to help us create tailored solutions that maximize NetSuite’s potential. We recognized early on that we would need a database to manage the software that we produce and that is maintained by our internal teams. Using NetSuite for this purpose was a natural choice for this specialized application, allowing us to connect the app to all the other business elements we use to run our NetSuite Systems Integration practice.
In 2017, I published an article: Learn How to Dynamically Post NetSuite Content to WordPress to show the NetSuite community how to get business content into WordPress. More recently, we have refined this approach to help both our clients and staff become more educated about our Accelerator Templates and how using them can act as a foundation for NetSuite solution development. Thus, we had a content development and distribution challenge.
The Accelerator Templates follow a straightforward record structure:
- Primary — Application: This table holds the main information about each Accelerator Template. Each record has a unique (internalid) key.
- Child — Versions: As we evolve the software bundles, we track changes in a version history record.
- Child — Articles: Over time, I’ve written many articles about these templates to educate others on how we innovate and add value. This record relates those articles to the respective application record.
- Child — Videos: We catalog demonstration and concept videos related to the software bundles so they can be showcased separately to help educate interested listeners.
Our goal is to publish this data on our marketing website, enabling it to dynamically generate pages that showcase our evolving Accelerator Templates. We don’t want to manage content on the marketing website as we have it properly cared for in the NetSuite database.
Using JSON to Share Data Between NetSuite and the Content Management System (CMS)
Now, in 2024, when this article was crafted, JavaScript Object Notation (JSON) has long become the standard for sharing complex data between systems. With our strong technical leadership in NetSuite, we designed a JSON structure that can be periodically retrieved by our content management system (CMS) from NetSuite. We agreed to build a NetSuite endpoint for this purpose and could use either a Restlet or Suitelet to handle the job. I won’t go into which one (a Restlet or Suitelet) to use, as the most important consideration is data retrieval and craft.
The key technique was to generate the data in a single HTTPS call to NetSuite. Click the image to get a better representation of the data structure and architecture. Here’s how we approached it.
A SuiteCloud Technology Approach to JSON Hierarchical Data
Due to the complexity of the data, we crafted a JSON structure that reflects the natural organization of our Accelerator Template components. Here is the approach I used:
- Saved Search for Accelerator Template Application Records: Saved Search was used to define the query for retrieving Accelerator Template Application records. A saved search naturally produces flat (single-level) data, so the record ID in these records becomes critical for further steps.
- Image Asset Representation: We store promotional images for each template in NetSuite, using NetSuite’s anonymous URLs for reference. Our saved search can only return the internal file ID of the image asset, so we used SuiteQL to look up the corresponding URLs we need.
- Child Reference Information: For Versions, Articles, and Videos child tables, we used SuiteQL to dynamically retrieve the related records. Here, we needed to pull the data that is connected to the Accelertor Template Application Records through foreign keys.
- Binding Data for JSON Representation: We programmatically crafted the JSON structure. SuiteScript 2.1, which supports ECMAScript 6, made this process efficient with its map, reduce, and filtering functions.
SuiteScript 2.1 Example for Complex JSON
Below is an example of a SuiteScript program consolidating Saved Search and SuiteQL to produce complex JSON. This code example can be useful for SuiteScript developers looking to generate intricate data structures.
/** * @NApiVersion 2.1 */ var SERVER = 'https://123456.app.netsuite.com'; var SEARCH_LAB_APP = 'customsearch_pri_labs_for_website_c1' var SQL_LOGO = `SELECT id, url FROM file WHERE id in ?` //paramertize where clause with ? placeholder var SQL_VERSION = `SELECT id, custrecord_pri_application_ver_app app, name, custrecord_pri_application_ver_enhance FROM customrecord_pri_application_ver WHERE custrecord_pri_application_ver_publish = 'T' AND custrecord_pri_application_ver_app IN ? ORDER BY app, name` var SQL_ARTICLE = `SELECT id, custrecord_pri_application_article_app app, name, custrecord_pri_application_article_ov overview, custrecord_pri_application_article_ref url , custrecord_pri_application_article_lead lead, custrecord_pri_application_article_order order FROM customrecord_pri_application_article WHERE custrecord_pri_application_article_app IN ? ORDER BY lead, order` var SQL_VIDEO = `SELECT id, custrecord_pri_application_video_app app, name, custrecord_pri_application_video_ov overview, custrecord_pri_application_video_ref url , custrecord_pri_application_video_lead lead, custrecord_pri_application_video_order order FROM customrecord_pri_application_video WHERE custrecord_pri_application_video_app IN ? ORDER BY lead, order` define(['N/search', 'N/http', 'N/query'], function(search, http, query) { function onRequest(context) { if (context.request.method === http.Method.GET) { //get primary results and update SQL where clauses var searchResults = executeSavedSearch(SEARCH_LAB_APP); //execute SQL and return objects var sqlLogoResults = executeSuiteQL(SQL_LOGO); var sqlVersionResults = executeSuiteQL(SQL_VERSION); var sqlArticleResults = executeSuiteQL(SQL_ARTICLE); var sqlVideoResults = executeSuiteQL(SQL_VIDEO); //map the objects together into JSON var jsonmapLogoResults = mapLogoResults(sqlLogoResults, searchResults) var versionResults = mapResults(jsonmapLogoResults, 'ID', sqlVersionResults, 'app', 'versions') var articleResults = mapResults(versionResults, 'ID', sqlArticleResults, 'app', 'articles') var videoResults = mapResults(articleResults, 'ID', sqlVideoResults, 'app', 'videos') var finalResults = videoResults //output the JSON setting the content type context.response.setHeader({ name: 'Content-Type', value: 'application/json' }); context.response.write(JSON.stringify(finalResults)); } }; function executeSavedSearch(searchId) { //the primary search is the applications / while spinning through object, build the SQL Where clause var mySearch = search.load({id: searchId}); var searchResults = []; // map the logo and general IDs into the sql for inquiry and later mapping var l = '(' var w = '(' //the logos provide the internal file ID. //the other objects reference the primary record set as foreign keys mySearch.run().each(function(result) { var resultRow = {}; mySearch.columns.forEach(function(column) { resultRow[column.label] = result.getValue(column); if (column.label == 'Logo') { if (resultRow[column.label]){ l = l + resultRow[column.label] + ', ' } } if (column.label == 'ID') { if (resultRow[column.label]){ w = w + resultRow[column.label] + ', ' } } }); searchResults.push(resultRow); return true; }); //finish the where clauses using Zero as a meaningless value l = l + '0)' w = w + '0)' //update SQL for related APP IDs SQL_LOGO = SQL_LOGO.replace('?', l) SQL_VERSION = SQL_VERSION.replace('?', w); SQL_ARTICLE = SQL_ARTICLE.replace('?', w); SQL_VIDEO = SQL_VIDEO.replace('?', w); return searchResults; } function mapResults (primaryObject, primaryColumn, secondObject, secondColumn, newNodeName){ // map to gather related elements from the second object by matching the ID from the primary object const gatherRelatedElements = primaryObject.map((accumulator, primaryItem) => { // Find all related elements from the second object const relatedElements = secondObject.filter((secondItem) => { return (parseInt(secondItem[secondColumn]) === parseInt(primaryObject[primaryItem][primaryColumn])) } ); //add the filtered items to the primaryObject accumulator[newNodeName] = relatedElements return accumulator; }, {}); return gatherRelatedElements } function mapLogoResults (url, app){ // Convert the first array into a mapping object for quick ID lookup const urlMap = url.reduce((acc, item) => { acc[item.id] = item.url; return acc; }, {}); // Map through the second array and add the url if the IDs match const UpdatedApps = app.map(app => { const id = parseInt(app.Logo, 10); // Convert to integer if necessary if (urlMap[id]) { app.LogoUrl = SERVER + urlMap[id]; // Add the url property from the matching id in urlMap } return app; }); return UpdatedApps; } function executeSuiteQL(q) { return query.runSuiteQL({query: q}).asMappedResults(); } return { onRequest: onRequest }; });
An Alternative NetSuite Approach to Complex JSON
Our Content Renderer Engine (CRE) offers a user-friendly alternative for generating complex JSON. In this article Content Renderer Engine 2.0 with NetSuite SuiteQL, I demonstrate how administrators can combine Saved Search and SuiteQL with FreeMarker templates to create custom outputs. One of these outputs can be JSON, which is fully customizable by the designer. You can then call the CRE profile to render the JSON and output it as a NetSuite endpoint. Thus, this alternative approach takes advantage of our license-free toolsets.
Working with Expert NetSuite Leadership
This article showcases the power of NetSuite when used intelligently to drive business efficiency. Organizations can unlock the full value of NetSuite by centralizing their business data and integrating it with third-party systems.
Our firm is often called upon when management seeks high-value outcomes. We assist in raising organizational standards through hands-on support, guidance, and technical expertise.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you have a business challenge to publish content from NetSuite to a content management system, let’s have a conversation.