How to Build a Nightly Inventory Valuation Report from NetSuite to FTP

Build a Spojit workflow that runs every night, pulls each item's on-hand quantity and average cost from NetSuite, values the inventory line by line, writes the totals to a CSV, and drops the file on an FTP server for your warehouse and finance teams.

What This Integration Does

Finance and warehouse teams often need a fresh, dated snapshot of what the inventory on the shelf is actually worth: quantity on hand multiplied by unit cost, summed across every item, ready first thing in the morning. Doing this by hand means logging into NetSuite, exporting a saved search, opening it in a spreadsheet, totalling the columns, and emailing or uploading the file. This workflow does the whole thing on a schedule so the valuation file is waiting on the FTP drop before anyone arrives. Because it reads cost and quantity straight from NetSuite with a single run-suiteql query, the numbers always match the system of record.

The workflow is started by a Schedule trigger on a nightly Unix cron, so there is no manual step and no webhook to call. Each run pulls the current item list with quantities and costs, computes an extended value per line and a grand total with the math connector, builds a CSV with the csv connector, and uploads it to FTP with a dated filename. Runs do not depend on each other: every run produces a fresh, self-contained file named for that date, so re-running (or a missed night caught up later) simply writes another dated file and never corrupts a previous one. If the FTP upload fails the run is marked failed in the execution history, leaving the prior day's file untouched.

Prerequisites

A NetSuite connection in Spojit (added under Connections) with permission to run SuiteQL and read item records.
An FTP connection in Spojit pointing at the server and folder where the report should land, with write permission to that directory.
The csv and math connectors, which are built-in utility connectors and need no credentials.
Knowledge of which NetSuite item field holds your unit cost (this tutorial uses average cost) and which holds quantity on hand. Confirm the exact field names in the NetSuite Analytics Schema Browser before you start.
A target FTP directory path, for example /reports/inventory/, agreed with the warehouse and finance teams.

Step 1: Add a Schedule trigger for the nightly run

Open a new workflow in the Spojit Workflow Designer and add a Trigger node. Set Trigger Type to Schedule. Enter a 5-field Unix cron and an IANA timezone. To run at 2:00 AM every day, use:

0 2 * * *
Australia/Sydney

The cron runs in the timezone you pick, so 2:00 AM is local time, not UTC. A Schedule trigger can hold more than one schedule if you later want a second time. The trigger output is { scheduledAt }, which you can reference downstream as {{ trigger.scheduledAt }} to stamp the report with its run time. For a deeper walkthrough of cron fields and timezones, see Setting Up a Schedule Trigger.

Step 2: Pull quantities and costs with NetSuite run-suiteql

Add a Connector node in Direct mode on the netsuite connector and pick the run-suiteql tool. SuiteQL lets you read exactly the columns you need in one call, which is far cheaper than listing every item record. In the query field, select item id, name, quantity on hand, and average cost, filtering out inactive items:

SELECT id, itemid, displayname, quantityonhand, averagecost
FROM item
WHERE isinactive = 'F' AND quantityonhand > 0
ORDER BY itemid

Set limit to a value such as 1000 (the maximum rows per page) and leave offset at 0 for the first page. Map the node result to a variable, for example items. The returned rows are available as a list you can reference downstream. Confirm your real field names in the NetSuite Analytics Schema Browser, because customized accounts may expose cost under a different field.

Step 3: Value each line with the math connector

Add a Loop node in ForEach mode over the item rows from Step 2 (for example {{ items.items }}, the list of returned records). Inside the loop body, add a Connector node in Direct mode on the math connector and pick the calculate tool. This evaluates an expression with named variables, which is the clean way to multiply units by cost per row. Set the expression to:

qty * cost

and pass the variables object using the current loop item, for example { "qty": {{ item.quantityonhand }}, "cost": {{ item.averagecost }} }. The tool returns the extended line value in result. Carry the item id, name, quantity, cost, and this line value forward so you can build a row per item in the next step. If you would rather avoid a per-row loop, you can instead compute the extended values in a single Transform node (see Step 4) and use math only for the grand total.

Step 4: Shape the rows and total with Transform and math

Add a Transform node to assemble a clean list of objects, one per item, with the columns you want in the CSV: item code, name, quantity on hand, unit cost, and extended value. This is also where you can format dates or rename keys so the header row reads the way finance expects. Reference the line values you produced in Step 3, and include the run date from {{ trigger.scheduledAt }} if you want it in every row.

To compute the grand total of all extended values, add another Connector node in Direct mode on the math connector with the sum tool. Pass the array of line values as numbers; it returns the portfolio value in result. Optionally add a math currency step to format that total as a readable string such as $1,284,930.00 by setting number to the total and currency to your code (for example AUD) for a summary line or for the upload email.

Step 5: Build the CSV file

Add a Connector node in Direct mode on the csv connector and pick the from-json tool. Feed it the list of row objects you built in the Transform node. Because each object shares the same keys, the connector turns them into a CSV with a header row and one line per item, for example:

itemCode,name,quantityOnHand,unitCost,extendedValue
SKU-1001,Widget A,120,4.50,540.00
SKU-1002,Widget B,80,12.25,980.00

Map the CSV text output to a variable such as csvText. If you want the running total appended as a final line, add it to the row list before this step, or keep the total only in the notification so the data file stays purely tabular and easy to import.

Step 6: Upload the file to FTP

Add a Connector node in Direct mode on the ftp connector and pick the upload-file tool. Set the remote path to a dated filename so each night produces a distinct file and nothing is overwritten, for example:

/reports/inventory/inventory-valuation-{{ trigger.scheduledAt }}.csv

Pass the csvText from Step 5 as the file content. If your folder structure uses dated subfolders, add an ftp create-directory step first so the upload never fails on a missing path. To let the teams know the file landed (and to include the formatted grand total), add a Send Email node after the upload: set Recipients to the warehouse and finance addresses, a templated Subject such as Inventory valuation for {{ trigger.scheduledAt }}, and a short Body that states the total and the FTP path. Remember that external recipients must be on the org allowlist under Settings > General > Email recipients. Save and enable the workflow.

Tips

If you have more items than one SuiteQL page returns, page through with offset in a loop (increase by your limit each pass) and concatenate the pages before valuing them.
Push as much of the math into NetSuite as you reasonably can: SuiteQL can return quantityonhand * averagecost as a computed column, leaving the math connector to only sum the grand total and currency-format it.
Use the math round tool on the total if you want whole-dollar reporting, and keep two decimals in the per-line values for auditability.
Ask Miraxa, the intelligent layer across your automation, to scaffold the canvas with a prompt like "Add a Schedule trigger at 2 AM, a netsuite run-suiteql node, a math sum node, a csv from-json node, and an ftp upload-file node", then fine-tune each node in the properties panel.

Common Pitfalls

Cron runs in the timezone you set, not UTC. A nightly job at 0 2 * * * in Australia/Sydney fires at a different absolute time than the same cron in America/New_York, so confirm the timezone matches where finance expects the cut-off.
Field names differ between accounts. averagecost and quantityonhand are common, but customized accounts may rename or split these; verify in the NetSuite Analytics Schema Browser before relying on the query.
Items with a null or zero cost will value to zero. Decide whether to exclude them in the SuiteQL WHERE clause or flag them in the report so finance can investigate, rather than silently shipping a low total.
Overwriting the same FTP filename every night destroys history. Always template the date into the remote path so each run leaves its own dated file.

Testing

Before enabling the nightly schedule, narrow the SuiteQL query to a handful of rows by adding FETCH FIRST 10 ROWS ONLY, then run the workflow with the Run button (the Schedule trigger still lets you trigger a manual run for testing). Open the execution history and check each step: confirm run-suiteql returned the expected columns, that the math line values and sum total are correct against a hand-calculation of those ten items, and that the CSV header and rows look right. Verify the file appears in the FTP folder with the dated name and opens cleanly in a spreadsheet. Once the small-scope file checks out, remove the row limit and enable the schedule.