Sync NetSuite Records with Tulip Tables

If you're using Tulip, you've probably had some experience with Tables. They are, in short, a super convenient layer that the Tulip platform provides to enable users to create and store tabular data for use in Tulip applications without needing to be skilled in a query language.

If you happen to work for an organization where transactional data lives in the NetSuite ERP you likely are already aware that working with those tables may present some interesting challenges. Namely that you are definitely going to need to enlist a developer in order to get your Work Orders, Sales Orders, Item Fulfillments over to Tulip so you can build apps that allow your operators insight into the transactions that their day to day revolves around. Worse, every time you need to add a new NetSuite record field to your digital twin in Tulip you'll need to get back in touch with the dev team.

Luckily, we've built this more than a few times.

Our Tulip Table Sync customization provides real time CRUD (Create, Read, Update, Delete) operations that can be deployed on any record in NetSuite to transport header level field values to a corresponding table in Tulip.

We're leveraging the description field on the Tulip Table column to map the NetSuite field id within Tulip. This way, when you need to add new fields from NetSuite to your Tulip table, it's every bit as simple as adding a new Tulip Table Column. No developer required.

We want to be clear. This is not an installable bundle. This is a scripted customization that we can install for clients in a matter of hours that will give your team the ability to create and evolve Tulip Tables that function as digital twins with minimal involvement from us or other developers as your Tulip ecosystem grows.

Not a developer? We'll implement it for you!

The API Class for User Event Scripts

We've created a couple JS classes that function as instantiable APIs for a subset of the RESTful endpoints that Tulip provides. The result is super easy to use:

let ttRecord = new tulip.userevent.Records(sc.newRecord);
ttRecord.POST();
ttRecord.GET();
ttRecord.PUT();
ttRecord.DELETE();

All the variables that handle routing and authentication are stored as parameters on the Deployment Record, which allows a great degree of flexibility in configuring which records go to which tables.

When the Records class is instantiated. We make a call to the specified table to get back information about each column and automatically map each column to its specified field on the NetSuite record.

Here's a basic example of how we're using it to transport Work Order Data

/**
 * @NApiVersion 2.1
 * @description User Event logic related to syncing Work Order records
 * @author Eric Alwine <support@monocerosconsulting.com>
 */
define([
    'N/record'
    , 'N/search'
    , 'SuiteScripts/MC/Tulip API/tulip_ue.api'
  ],

  (record, search, tulip) => {

    /**@function onCreate
     * @description Handles creation of WO data
     * @param {Object} sc
     * @param {Record} sc.newRecord - New record
     * @param {Record} sc.oldRecord - Old record
     * @param {string} sc.type
     * @returns
     */
    function onCreate(sc) {
      if (!['create'].includes(sc.type)) return;
      new tulip.userevent.Records(record.load(sc.newRecord)).POST();
    }

    /**@function onEdit
     * @description Handles update of WO data
     * @param {Object} sc
     * @param {Record} sc.newRecord - New record
     * @param {Record} sc.oldRecord - Old record
     * @param {string} sc.type
     * @returns
     */
    function onEdit(sc) {
      if (!['edit', 'xedit'].includes(sc.type)) return;
      new tulip.userevent.Records(record.load(sc.newRecord)).PUT();
    }

    /**@function onDelete
     * @description Handles deletion of WO data
     * @param {Object} sc
     * @param {Record} sc.newRecord - New record
     * @param {Record} sc.oldRecord - Old record
     * @param {string} sc.type
     * @returns
     */
    function onDelete(sc) {
      if (!['delete'].includes(sc.type)) return;
      new tulip.userevent.Records(sc.newRecord).DELETE();
    }
    
    /**@function afterSubmit
     * @param {Object} scriptContext
     * @param {Record} scriptContext.newRecord - New record
     * @param {Record} scriptContext.oldRecord - Old record
     * @param {string} scriptContext.type
     */
    const afterSubmit = (scriptContext) => {
      onCreate(scriptContext);
      onEdit(scriptContext);
      onDelete(scriptContext);
    }

    return {afterSubmit}
  }); 

You might have noticed the @NScriptType annotation is missing. That's because this is not the actual entry point, but a module. Read on!

Multiple User Event Deployments

Because you may need to transport several types of records, or have other records update the records in your digital twins (I.E. Work Order Completions updating Work Orders) We've built this to be one script record deployed against many record types.

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 * @author Eric Alwine <support@monocerosconsulting.com>
 */
define([
      'N/runtime'
      , 'N/email'

      , 'SuiteScripts/MC/SalesOrder/Sync/so_sync_so.userevent'
      , 'SuiteScripts/MC/ItemFulfillment/Sync/if_sync_so.userevent'
      , 'SuiteScripts/MC/WorkOrder/Sync/wo_sync_wo.userevent'
      , 'SuiteScripts/MC/WorkOrderCompletion/Sync/woc_sync_wo.userevent'
    ],

    (
        runtime
        , email

        , customdeploy_mc_so_sync_so
        , customdeploy_mc_if_sync_so
        , customdeploy_mc_wo_sync_wo
        , customdeploy_mc_woc_sync_wo

    ) => {

      const deployments = {
        customdeploy_mc_so_sync_so: customdeploy_mc_so_sync_so
        ,customdeploy_mc_if_sync_so: customdeploy_mc_if_sync_so
        ,customdeploy_mc_wo_sync_wo: customdeploy_mc_wo_sync_wo
        ,customdeploy_mc_woc_sync_wo: customdeploy_mc_woc_sync_wo
      };

      /**@function handleError
       * @description Logs and Emails Errors
       * @param e
       */
      const handleError = (e, {oldRecord, newRecord, type}) => {
        log.error({title: 'Tulip Sync Failed', details: e});
        let user = runtime.getCurrentUser();
        email.send({
          author: your_user_id_or_whoever,
          recipients: [your_user_id_or_whoever],
          subject: 'Tulip Sync Failed',
          body: JSON.stringify({type, record, e, user})
        });
      };

      function afterSubmit(sc) {
        const script = runtime.getCurrentScript();
        try {
          deployments[script.deploymentId].afterSubmit(sc);
        } catch (e) {
          handleError(e, sc);
        }
      }

      return {
        afterSubmit
      }
    });

This allows us to define behavior specific to each type of record, while still being able to leverage the flexibility that script parameters allow us and keep the technical debt at a minimum.

The API for RESTlet Scripts

We've also created an alternative class that is used the same way, but is intended for RESTlets. The reason for this is that due to the nature of the use case for RESTlets, each script that uses the API will be unique. Because script parameter id's need to be unique in NetSuite, we cannot use the same approach as the User Event script in regards to storing the domain, api key, table id, etc. as script parameters. For RESTlets, these variable need to be defined within the script.

const domain= 'youraccount.tulip.co';
const key = 'apikey.2_0neOfyOuRAP1k3Yz';
const secret_id = 'custsecret_tulip_api';
const table_id = 'ezJx7uQhJytFFcTqk';

Accordingly, this requires a few more arguments during instantiation

let woTable = new tulip.restlet.Records(domain, key, secret_id, table_id);

Another difference is that the RESTlet flavor of our API is intended to be used with a search.Result object, rather than a record.Record.

search.create({
    type: "transaction",
    filters: [
        ["some_filter", "anyof", "your_condition"],
        "AND",
        ["mainline", "is", "T"],
        "AND",
        ["type", "anyof", "WorkOrd"]
    ],
    columns: woTable.getSearchColumns()
});

search.run().each(r => {
    woTable.bind(r);
    woTable.PUT();
});

We've included methods "getSearchColumns" to allow for the dynamic creation of saved searches without the need for additional mapping and "bind" to allow for the instance to be used with multiple results without incurring the cost of re-instantiating unnecessarily.

Un-mapped Columns

Lastly, we've also provided an easy way to send data to columns in the Tulip Table that are not mapped to NetSuite fields.

The POST and PUT methods take an object argument that will allow for arbitrary values to be sent to any column on the Tulip Table. This, obviously requires scripting skills to adjust, but we feel that it's an appropriate level of technical debt given that the infrastructure to facilitate corner case functionality would create additional technical debt in other areas.

Simply create an object where the properties correspond to the Tulip Column ID

let virtuals = {
  ctqcp_remaining: parseInt(values?.quantity) - parseInt(values?.built)
  ,iatkb_built: parseInt(values?.built)
  ,potok_built_2: parseInt(values?.buildable)
};

And pass it to the PUT or POST call

new tulip.Records(record.load(sc.newRecord)).PUT(virtuals);

Voila!

Getting Started

Our goal with this project is to implement a data transport functionality that embodies Tulip's mission to the greatest extent possible and allows basic changes to made to Tulip Table digital twins for NetSuite records with as minimal scripting skill as possible.

We've made the choice to leave out the API classes themselves and some other details because we'd like an opportunity to discuss use cases and implementation with folks who are interested in using what we've built.

If you're interested in getting this set up for your environment, please reach out!

Let's Connect!