Home/ NetSuite Scripting/Integrations with Boomi & Celigo (NetSuite + Middleware)

Integrations with Boomi & Celigo (NetSuite + Middleware)

🔹 Why use Boomi or Celigo with NetSuite?

iPaaS platforms like Boomi and Celigo give you:

  • Prebuilt connectors for NetSuite + 3rd-party apps
  • Visual mappings, transforms, and schedulers
  • Centralized error handling, retries, monitoring
  • Role-based access, secrets management, and deployment controls

Use cases:

  • Ecommerce (orders, fulfillments, refunds)
  • Finance (invoices, payments, cash apps)
  • Supply chain (WMS, 3PL, EDI)
  • CRM/CPQ (accounts, quotes, contracts)

🔹 Integration patterns (what to choose and when)

  1. Direct NetSuite Connector → CRUD
    • Best for standard objects, moderate volume.
    • Map to NetSuite fields in the connector (less custom code).
  2. RESTlet façade (custom API in NetSuite)
    • Best when you need custom validations, derived fields, or multi-record transactions.
    • Middleware calls your RESTlet; RESTlet can trigger User Events, Map/Reduce, or Saved Searches.
  3. File (SFTP) drops
    • Best for batch/EDI or partners without APIs.
    • Boomi/Celigo handle SFTP; NetSuite Scheduled/MR scripts process the files.
  4. Event/Webhook (outbound from NetSuite)
    • NetSuite sends to middleware via RESTlet → Middleware fans-out to destination apps.

Rule of thumb: Use the native connector first. When logic exceeds connector capabilities, front NetSuite with a RESTlet and keep business rules in NetSuite.

🔹 Security & governance checklist (use this every time)

  • Use Token-Based Auth (TBA) for NetSuite; least-privilege role.
  • Store secrets in iPaaS vault and NetSuite script parameters.
  • Implement idempotency keys on upserts (prevent duplicates on retries).
  • Limit payload sizes; page large reads (Saved Search + runPaged).
  • For heavy jobs, enqueue Map/Reduce from the RESTlet and return a job id.
  • Log safely: mask tokens, truncate large payloads, include a correlation ID.

🔹 Reference payload contract (recommended)

Always wrap responses to/from middleware:

{
  "ok": true,
  "id": "12345",
  "data": { },
  "error": null,
  "meta": { "cid": "FLOW-1732599999-ab12" }
}
  • cid = correlation ID to trace end-to-end across Boomi/Celigo + NetSuite.

🔹 NetSuite side: RESTlet façade (GET/POST/PUT) with idempotency

/**
 * @NApiVersion 2.1
 * @NScriptType Restlet
 */
define(['N/record','N/search','N/runtime','N/task'], (record, search, runtime, task) => {

  // Simple logger helpers (redact + correlation)
  const safe = (o)=>{ try{return JSON.stringify(o);}catch(_){return '[unserializable]'} };
  const cid = ()=>`INT-${Date.now()}-${Math.random().toString(16).slice(2,6)}`;

  /** GET: read by internalid (fallback to externalid) */
  const get = (params) => {
    const run = cid();
    try {
      const id = parseInt(params.id||'0',10);
      if (id) {
        const rec = record.load({ type: record.Type.SALES_ORDER, id });
        return { ok:true, data:{ id, tranid: rec.getValue('tranid') }, meta:{cid:run} };
      }
      // fallback by externalid
      const extId = (params.externalid||'').trim();
      if (extId) {
        const soId = search.lookupFields({ type: 'salesorder', id: extId, columns: ['internalid']});
        return { ok:true, data:{ id: soId?.internalid || null }, meta:{cid:run} };
      }
      return { ok:false, error:'Provide id or externalid', meta:{cid:run} };
    } catch (e) {
      log.error('RESTLET:GET', safe({cid:run, err:e.message}));
      return { ok:false, error:e.message, meta:{cid:run} };
    }
  };

  /** POST: create SO; idempotencyKey prevents duplicates on retry */
  const post = (body) => {
    const run = cid();
    try {
      const idem = (body?.idempotencyKey||'').trim();
      if (!idem) return { ok:false, error:'idempotencyKey required', meta:{cid:run} };

      // Check existing by idempotency key (custom record/table/field you design)
      // Example: a Saved Search on customrecord_int_idem with key = idem
      const exists = search.create({
        type:'customrecord_int_idem',
        filters:[['name','is',idem]],
        columns:['internalid']
      }).run().getRange({start:0,end:1});
      if (exists.length) return { ok:true, data:{ duplicate:true }, meta:{cid:run} };

      // Create Sales Order (tiny example)
      const so = record.create({ type: record.Type.SALES_ORDER, isDynamic:true });
      so.setValue({ fieldId:'entity', value: body.customerId });
      // add line(s) etc…

      const id = so.save();

      // Mark idempotency key as used
      const token = record.create({ type:'customrecord_int_idem' });
      token.setValue({ fieldId:'name', value: idem });
      token.setValue({ fieldId:'custrecord_int_refid', value: id });
      token.save();

      return { ok:true, id, meta:{cid:run} };
    } catch (e) {
      log.error('RESTLET:POST', safe({cid:run, err:e.message}));
      return { ok:false, error:e.message, meta:{cid:run} };
    }
  };

  /** PUT: big updates → enqueue Map/Reduce and return task id */
  const put = (body) => {
    const run = cid();
    try {
      const mr = task.create({
        taskType: task.TaskType.MAP_REDUCE,
        scriptId: 'customscript_mr_bulk_update',
        deploymentId: 'customdeploy_mr_bulk_update',
        params: { custscript_mr_payload: JSON.stringify(body||{}) }
      });
      const taskId = mr.submit();
      return { ok:true, taskId, meta:{cid:run} };
    } catch (e) {
      log.error('RESTLET:PUT', safe({cid:run, err:e.message}));
      return { ok:false, error:e.message, meta:{cid:run} };
    }
  };

  return { get, post, put };
});

Why this helps

  • Middleware always hits the same contract.
  • You can scale heavy work with Map/Reduce.
  • Idempotency guards against duplicates from retries.

🔹 Boomi: design tips & patterns

Boomi building blocks you’ll use

  • NetSuite Connector (SuiteTalk / REST) for CRUD where possible
  • HTTP Client to call your RESTlets
  • Disk/SFTP for file exchanges
  • Decision / Data Process shapes for validation, mapping, enrichment
  • Try/Catch + Exception + Process Reporting for error handling
  • Atom/Cloud choice affects network reachability to on-prem targets

Recommended flow (example: Shopify → NetSuite SO)

  1. Listener (poll Shopify orders)
  2. Map fields → NetSuite model
  3. Lookup (Boomi connector) to resolve item/customer IDs
  4. Decision: create vs update
  5. HTTP Client → call NetSuite RESTlet POST (with idempotencyKey)
  6. Return new SO id; log to Process Reporting
  7. Catch and retry with exponential backoff on 5xx

Tips

  • Use document properties for cid and pass it to NetSuite.
  • Keep batch sizes small; page through source systems.
  • Centralize secrets in Boomi Environment Extensions.

🔹 Celigo (integrator.io): design tips & patterns

Celigo components you’ll use

  • Imports/Exports with NetSuite Connector (v2)
  • HTTP Imports/Exports to call RESTlets or external APIs
  • Hooks (preMap/postMap) for custom transforms
  • Lookup Tables to normalize codes (e.g., shipping, tax)
  • Error Management: retries, on-error exports, email/slack alerts

Recommended flow (example: NetSuite SO → 3PL WMS)

  1. Export Saved Search of SO pending fulfillment (paged)
  2. preMap Hook: enrich/format payload
  3. HTTP Import to 3PL API
  4. on-success: mark SO (flag) via NetSuite Import (submitFields)
  5. on-error: route to error export; notify; keep idempotency key so retried calls don’t duplicate

Tips

  • Use Flow variables for correlation id; pass to NetSuite logs.
  • Prefer NetSuite Import with submitFields for lightweight updates.
  • Use Dynamic Lookups to resolve internal IDs at runtime.

🔹 Saved Search + runPaged (middleware-friendly pagination)

/**
 * Use in RESTlet GET for reliable pagination.
 * GET ...?page=0&pageSize=200
 */
define(['N/search'], (search) => {
  const get = (p) => {
    try {
      const page = Math.max(parseInt(p.page||'0',10), 0);
      const pageSize = Math.min(Math.max(parseInt(p.pageSize||'200',10), 1), 1000);

      const s = search.load({ id: 'customsearch_export_orders' });
      const paged = s.runPaged({ pageSize });

      if (page >= paged.pageRanges.length) {
        return { ok:true, page, totalPages: paged.pageRanges.length, data: [] };
      }

      const data = paged.fetch({ index: page }).data.map(r => ({
        id: r.id,
        tranid: r.getValue('tranid'),
        entity: r.getText('entity')
      }));

      return { ok:true, page, totalPages: paged.pageRanges.length, data };
    } catch (e) {
      return { ok:false, error:e.message };
    }
  };
  return { get };
});

Why middleware loves this

  • Deterministic pages; no surprises with large result sets.
  • Works the same from Boomi or Celigo HTTP calls.

🔹 Inbound bulk processing: RESTlet → Map/Reduce

MR entry (reads payload from RESTlet param):

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/record','N/runtime'], (record, runtime) => {
  const getInputData = () => {
    try {
      const payload = runtime.getCurrentScript().getParameter({ name:'custscript_mr_payload' });
      const arr = JSON.parse(payload||'[]');  // array of orders/items/etc.
      return arr;
    } catch (e) { log.error('MR getInputData', e.message); }
  };

  const map = (ctx) => {
    try {
      const row = JSON.parse(ctx.value);
      // validate & normalize
      ctx.write({ key: row.customerId, value: JSON.stringify(row) });
    } catch (e) { log.error('MR map', e.message); }
  };

  const reduce = (ctx) => {
    try {
      // group by customer
      ctx.values.forEach(v => {
        const row = JSON.parse(v);
        // create/update records; prefer submitFields for small changes
      });
    } catch (e) { log.error('MR reduce', e.message); }
  };

  const summarize = (s) => {
    log.audit('MR summary', `usage=${s.usage}, yields=${s.yields}`);
  };

  return { getInputData, map, reduce, summarize };
});

🔹 Error handling & retries (end-to-end)

  • Boomi/Celigo: enable automatic retries for 429/5xx; set max attempts and backoff.
  • NetSuite RESTlet: return { ok:false, error, meta } and log correlation ID.
  • Idempotency: require a key; store it (custom record) with the created/updated entity id.
  • Partial failures: return per-row errors to middleware; don’t make the whole batch fail.

🔹 Monitoring & observability

  • Use iPaaS dashboards (execution counts, error rates, latency).
  • In NetSuite, write audit logs with the correlation ID.
  • Optional: persist small integration logs to a custom record for reconciliation.

🔹 Boomi vs Celigo — quick guidance

AreaBoomiCeligo
Learning curveSteeper for non-devs; very flexibleFriendly UI for NetSuite admins
Best forComplex, multi-system estates; on-prem + cloudNetSuite-centric SaaS stacks, quick wins
NetSuite fitMature connectors; strong mappingDeep NetSuite features (flows, hooks, lookups)
Cost/scaleEnterprise-gradeSMB → Mid-market friendly

(Your choice often depends on existing licenses and team skills.)

✅ Key Takeaway

  • Prefer native connectors; add a RESTlet façade when logic requires it.
  • Enforce idempotency, page large datasets, and push heavy work to Map/Reduce.
  • Centralize secrets, instrument with correlation IDs, and enable retries in the iPaaS.
  • With these patterns, both Boomi and Celigo deliver stable, debuggable, and scalable NetSuite integrations.

Leave a Reply