Marty Zigman Marty Zigman
Prolecto Labs Accelerator Templates

Learn the Craft for Publishing NetSuite Data to a Marketing Content Management Systems

Marketing NetSuite Technical



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:

  1. Primary — Application: This table holds the main information about each Accelerator Template. Each record has a unique (internalid) key.
  2. Child — Versions: As we evolve the software bundles, we track changes in a version history record.
  3. 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.
  4. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

About Marty Zigman

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

Biography • Website • X (Twitter) • Facebook • LinkedIn • YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *