How to Flag Overdue NetSuite Invoices and Email Customer Reminders

Build a Spojit workflow that runs every morning, queries NetSuite with SuiteQL to find invoices past their due date, and emails each customer a templated payment reminder.

What This Integration Does

Chasing overdue invoices by hand is slow and easy to forget. This workflow turns accounts-receivable follow-up into a scheduled, hands-off routine: once a day Spojit asks NetSuite for every open invoice whose due date has passed, then sends each affected customer a polite, personalized reminder that names their invoice number, amount, and how many days late it is. Your team only steps in for the genuinely stuck accounts.

The workflow starts on a Schedule trigger (a daily cron in your own timezone). On each run it issues one run-suiteql query to NetSuite, loops over the returned invoice rows, and uses a Send Email node to reminder each customer. It writes nothing back to NetSuite, so it is safe to re-run: a second run on the same day simply emails the same still-open invoices again, which is why most teams schedule it once per day. The query is the source of truth for "what is overdue", so the workflow always reflects the live state of your ledger at the moment it runs.

Prerequisites

A NetSuite connection in Spojit (Connections → Add connection → NetSuite) with permission to run SuiteQL and read invoice and customer records.
Customer email addresses populated in NetSuite so the query can return a real recipient per invoice.
Any external recipient domains you email must be on your org allowlist under Settings → General → Email recipients (the built-in Send Email service only sends to allowlisted recipients).
Confidence with NetSuite table and field names for SuiteQL. The Analytics Data Source Schema Browser in NetSuite lists the transaction, transactionline, and customer fields used below.

Step 1: Add a Schedule trigger

Create a new workflow and set its trigger. In the trigger panel choose Trigger Type → Schedule. A Schedule trigger uses a 5-field Unix cron expression plus an IANA timezone, and a single trigger can hold more than one schedule. For a weekday 9am run set the cron to 0 9 * * 1-5 and the timezone to your finance team's zone, for example Australia/Sydney. The trigger output is simply { scheduledAt }, the timestamp the run fired, which you can reference downstream as {{ scheduledAt }}.

Step 2: Query overdue invoices with SuiteQL

Add a Connector node in Direct mode, choose the NetSuite connector, and select the run-suiteql tool. Direct mode is deterministic and costs no AI credits, which is exactly what you want for a predictable read. In the query field, ask for open invoices whose due date is before today, joined to the customer so you get an email and name in the same row:

SELECT
  t.id            AS invoice_id,
  t.tranid        AS invoice_number,
  t.duedate       AS due_date,
  t.foreigntotal  AS amount_due,
  c.entityid      AS customer_name,
  c.email         AS customer_email
FROM transaction t
JOIN customer c ON c.id = t.entity
WHERE t.type = 'CustInvoice'
  AND t.status = 'CustInvoice:A'
  AND t.duedate < CURRENT_DATE
  AND c.email IS NOT NULL
ORDER BY t.duedate ASC

Here t.status = 'CustInvoice:A' targets open invoices and t.duedate < CURRENT_DATE keeps only the overdue ones. Use the optional limit field (max 1000 rows per page) and offset for pagination if your overdue list is large. Bind the result to an output variable, for example overdue.

Step 3: Loop over each overdue invoice

Add a Loop node in ForEach mode and point it at the rows returned by the query, for example {{ overdue.items }} (the field that holds the row array in your SuiteQL result). Each pass exposes one invoice as the loop item, which you can reference as {{ invoice }}. Everything you add inside the loop body runs once per overdue invoice, so this is where the per-customer reminder lives.

Step 4: Calculate how many days overdue (optional)

Inside the loop, add a Connector node in Direct mode for the Date & Time Tools utility connector and use the diff tool to compare {{ invoice.due_date }} with {{ scheduledAt }} in days. Bind the result to a variable such as daysLate so your email can say exactly how late the invoice is. If you prefer to keep the workflow lean you can skip this step and let the due date speak for itself in the message body.

Step 5: Send the reminder email

Still inside the loop, add a Send Email node. This sends from Spojit's built-in mail service, so no connection is required. Map the fields to the loop item:

Recipients: {{ invoice.customer_email }}
Subject: Reminder: invoice {{ invoice.invoice_number }} is past due
Body (plain text, templated):

Hi {{ invoice.customer_name }},

Our records show invoice {{ invoice.invoice_number }} for
{{ invoice.amount_due }} was due on {{ invoice.due_date }} and is now
{{ daysLate.days }} days overdue.

Please arrange payment at your earliest convenience, or reply to this
email if you believe this is in error.

Thank you,
Accounts Receivable

Set Reply-To to your finance inbox so customer replies reach a person. For If sending fails, choose Continue anyway so one bad address does not halt the whole run and block the remaining reminders. Remember the email counts toward your monthly email allowance. To send from your own domain instead of the built-in service, swap the Send Email node for a Connector node using the Resend or SMTP connector and its send-email tool.

Step 6: Save, enable, and let the schedule run

Save the workflow and enable it. From here the Schedule trigger fires on your cron, NetSuite returns the current overdue set, and each customer receives one reminder per run. Watch the first few runs in the execution history to confirm the row count and the emails sent match what you expect. If you want a build shortcut, ask Miraxa, the intelligent layer across your automation, something like: "Add a Loop over {{ overdue.items }} and connect each pass to a Send Email node addressed to {{ invoice.customer_email }}." Miraxa can scaffold the nodes on the canvas, then you fine-tune the fields in the properties panel.

Tips

Keep the SuiteQL WHERE clause as the single definition of "overdue" so business rules (grace periods, excluded subsidiaries) live in one place. Add AND t.duedate < CURRENT_DATE - 7 to give customers a week of grace before the first nudge.
Use the limit and offset fields on run-suiteql to page through large overdue lists rather than pulling thousands of rows in a single call.
Set the Schedule timezone to your finance team's zone, not UTC, so "9am" means 9am to the people reading the reports.
For high-value accounts you can add a Condition node inside the loop that checks {{ invoice.amount_due }} and routes large balances to a Human approval node before any email goes out.

Common Pitfalls

Re-runs resend reminders. The workflow does not mark invoices as "reminded" in NetSuite, so every run emails every still-open overdue invoice. Schedule it once per day, not hourly.
Missing customer emails. Rows where c.email is null are dropped by the query's c.email IS NOT NULL filter, so those customers are silently skipped. Run a separate audit if you need to catch them.
Status and field names drift. SuiteQL status codes and table fields vary by account configuration. Verify t.status, t.type, and date fields against your NetSuite Analytics Data Source Schema Browser before trusting the results.
Allowlist blocks sends. If reminders silently fail, confirm the recipient domains are on your org email allowlist under Settings → General → Email recipients.

Testing

Before enabling the schedule, validate on a tiny scope. Temporarily add FETCH FIRST 1 ROWS ONLY to the SuiteQL query (or set limit to 1) and point the Send Email Recipients field at your own address instead of {{ invoice.customer_email }}. Run the workflow with the Run button and inspect the execution history: confirm the query returned the expected invoice, the loop ran once, and the reminder email arrived with the variables resolved correctly. Once the message reads the way you want, restore the real recipient and remove the row limit, then enable the Schedule trigger.