How to Sync NetSuite Sales Orders to a MySQL Revenue Ledger

Build a Spojit workflow that runs on a schedule, lists recent NetSuite sales orders, and inserts normalized rows into a MySQL revenue ledger for reporting.

What This Integration Does

Finance and operations teams often need order-level revenue in a plain SQL table so it can feed dashboards, period reconciliations, and ad-hoc queries without hitting NetSuite directly. This workflow keeps a MySQL table in step with your NetSuite sales orders: on each run it pulls the orders created since the last sweep, flattens the fields you care about (order number, customer, total, currency, status, transaction date), and writes one clean row per order into a ledger table you control.

The run model is time-based. A Schedule trigger fires on a Unix cron expression you set, lists the most recent NetSuite sales orders, loops over them, and inserts each into MySQL. Because the ledger is keyed on the NetSuite internal ID, the workflow is safe to re-run: rows already present are skipped or updated rather than duplicated. Each run leaves the ledger holding every order seen so far, and the next run only needs to catch up on what arrived in the meantime.

Prerequisites

A NetSuite connection added in Spojit under Connections, with permission to read sales orders.
A MySQL connection added under Connections, pointed at the database that holds your reporting tables, with insert and update rights.
A destination table in MySQL. This tutorial assumes a table named revenue_ledger with a unique key on the NetSuite order id (see Step 2).
Awareness of the NetSuite transaction date format used by SuiteTalk filters (for example 01-01-2025, day-month-year style as your account is configured).

Step 1: Add a Schedule trigger

Create a new workflow in the Workflow Designer and set the Trigger node type to Schedule. Add a cron expression and an IANA timezone so the sweep runs at a quiet time in your reporting region. For an overnight catch-up that runs daily at 02:00, use:

0 2 * * *
Australia/Sydney

A single Schedule trigger can hold more than one schedule, so you can add a second entry (for example a midday top-up) later. The trigger output is { scheduledAt }, which you can reference downstream as {{ trigger.scheduledAt }} if you want the run timestamp in the ledger.

Step 2: Prepare the MySQL ledger table

Before the workflow can insert rows, the ledger table must exist. Add a Connector node, choose your MySQL connection, set it to Direct mode, and select the execute-query tool. Run a one-off statement that creates the table if it is not already there. Set the query field to:

CREATE TABLE IF NOT EXISTS revenue_ledger (
  netsuite_id   VARCHAR(32) NOT NULL,
  order_number  VARCHAR(64),
  customer_name VARCHAR(255),
  total         DECIMAL(14,2),
  currency      VARCHAR(8),
  status        VARCHAR(32),
  tran_date     DATE,
  synced_at     DATETIME,
  UNIQUE KEY uq_netsuite_id (netsuite_id)
);

The unique key on netsuite_id is what makes re-runs safe. Once the table exists you can delete this node or leave it in place; CREATE TABLE IF NOT EXISTS is harmless on later runs.

Step 3: List recent NetSuite sales orders

Add a Connector node, choose your NetSuite connection in Direct mode, and select the list-sales-orders tool. Use the q field to limit the result to orders on or after a cutoff date, and limit to cap the batch size. To pull orders from the start of the current year:

q:      tranDate ON_OR_AFTER '01-01-2025'
limit:  200

This tool returns a list of order references. To page through more than one batch, increase limit or set the offset field on a follow-up call. Bind the result to a variable such as orders so later steps can read {{ orders }}.

Step 4: Loop over each order and fetch full detail

Add a Loop node in ForEach mode and point it at the list from Step 3 (for example {{ orders.items }}, matching the field your list step returns). Inside the loop body, add a Connector node on the NetSuite connection in Direct mode and select get-sales-order. Map its inputs from the current loop item:

id:                  {{ item.id }}
expandSubResources:  true

Setting expandSubResources to true inlines the order detail so you have the total, customer, currency, status, and transaction date in one object. Bind this to a variable such as order.

Step 5: Normalize the row with a Transform node

Still inside the loop, add a Transform node to reshape the NetSuite order into the exact column shape your ledger expects. Produce a single flat object that maps cleanly onto revenue_ledger:

{
  "netsuite_id":   "{{ order.id }}",
  "order_number":  "{{ order.tranId }}",
  "customer_name": "{{ order.entity.refName }}",
  "total":         "{{ order.total }}",
  "currency":      "{{ order.currency.refName }}",
  "status":        "{{ order.status.refName }}",
  "tran_date":     "{{ order.tranDate }}",
  "synced_at":     "{{ trigger.scheduledAt }}"
}

Keeping normalization in its own step means the database write stays simple, and if NetSuite field names shift you only adjust this one node. Bind the output to a variable such as row.

Step 6: Insert (or upsert) the row into MySQL

Add a Connector node on the MySQL connection in Direct mode. For a plain append, use the insert-rows tool with the table field set to revenue_ledger and the rows field set to a list containing your normalized object:

table: revenue_ledger
rows:  [ {{ row }} ]

To make re-runs idempotent against the unique key, prefer execute-query with an upsert statement instead, so an order seen twice updates rather than fails on the duplicate key:

INSERT INTO revenue_ledger
  (netsuite_id, order_number, customer_name, total, currency, status, tran_date, synced_at)
VALUES
  ('{{ row.netsuite_id }}', '{{ row.order_number }}', '{{ row.customer_name }}',
   {{ row.total }}, '{{ row.currency }}', '{{ row.status }}', '{{ row.tran_date }}', '{{ row.synced_at }}')
ON DUPLICATE KEY UPDATE
  total = VALUES(total), status = VALUES(status), synced_at = VALUES(synced_at);

That completes the loop body: each NetSuite order lands as one ledger row per run.

Step 7: Optional summary email

After the loop, add a Send Email node to confirm the sweep finished. Set Recipients to your finance distribution address, give it a templated Subject such as Revenue ledger sync {{ trigger.scheduledAt }}, and put the order count in the body. The Send Email node uses Spojit's built-in mail service, so no extra connection is needed; remember external recipients must be on the org allowlist under Settings > General > Email recipients.

Tips

Use the q filter on list-sales-orders to fetch only orders since the last run rather than the whole year. A rolling cutoff keeps each run small and fast.
If you have thousands of orders, raise limit up to its maximum and page with offset, or wrap the list call in a While loop that advances offset until a batch comes back empty.
Keep the upsert form from Step 6 as your default. It costs nothing extra and removes any worry about overlapping schedule windows creating duplicate rows.
Ask Miraxa, the intelligent layer across your automation, to scaffold the loop and Transform node for you, then fine-tune field mappings in the properties panel. A prompt like "Add a ForEach Loop over {{ orders.items }} with a Transform node that maps NetSuite order fields to my ledger columns" gets you most of the way.

Common Pitfalls

NetSuite date filters are sensitive to your account's date format. If tranDate ON_OR_AFTER returns nothing, confirm whether your account expects day-month-year or month-day-year in the q value.
Without the unique key from Step 2, overlapping schedule windows or a manual re-run will duplicate rows. The unique key plus the ON DUPLICATE KEY UPDATE statement is what keeps the ledger clean.
Timezones matter twice: the cron timezone on the Schedule trigger controls when the run fires, while tran_date comes from NetSuite. Reconcile both against the timezone your reports assume.
If NetSuite renames or restructures a field, the Transform node in Step 5 is the single place to fix it. Watch for empty customer_name or total values in the ledger as the first sign of schema drift.

Testing

Before enabling the schedule, narrow the scope and run by hand. Temporarily set limit on list-sales-orders to 3 and tighten the q filter to a recent date, then use the Run button to execute once. Open the execution logs to confirm each loop iteration fetched an order, the Transform node produced the expected flat row, and the MySQL step reported the inserts. Query SELECT * FROM revenue_ledger ORDER BY synced_at DESC LIMIT 10; to eyeball the rows, run the workflow a second time to prove the upsert does not duplicate them, then widen limit and turn the schedule on.