Published on July 31, 2023.
I've written previously about NetSuite clients who reach out to me for SuiteQL queries that are designed to help them prepare for audits, and earlier this month, that happened again. One of my clients requested queries that could be used to pull information on Bank Transfers. In this post, I'll share a few of the queries that I developed for them.
The first query that my client requested was a list of all bank transfers that had posted within a certain date range. Bank transfers are stored in the Transaction table and their transaction type is "Transfer." Here's the query that I developed for them.
SELECT Transaction.TranDate, Transaction.ID AS Transaction, Transaction.TranID, AccountingPeriod.PeriodName AS PostingPeriod, Transaction.Memo, ABS( Transaction.ForeignTotal ) AS Amount, Currency.Name AS Currency FROM Transaction INNER JOIN AccountingPeriod ON ( AccountingPeriod.ID = Transaction.PostingPeriod ) INNER JOIN Currency ON ( Currency.ID = Transaction.Currency ) WHERE ( Transaction.Type = 'Transfer' ) AND ( AccountingPeriod.StartDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-12-31', 'YYYY-MM-DD' ) )
The second query that my client requested was for the details of a specific bank transfer transaction, and specifically the GL accounts that were involved. To get that deeper level of data, I joined to the TransactionAccountingLine table. Here's that query.
SELECT Transaction.ID AS Transaction, Transaction.TranID, Transaction.TranDate, AccountingPeriod.PeriodName AS PostingPeriod, Transaction.Memo, Account.DisplayNameWithHierarchy, Transaction.ForeignTotal, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit, Currency.Name AS Currency FROM Transaction INNER JOIN AccountingPeriod ON ( AccountingPeriod.ID = Transaction.PostingPeriod ) INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Account ON ( Account.ID = TransactionAccountingLine.Account ) INNER JOIN Currency ON ( Currency.ID = Transaction.Currency ) WHERE ( Transaction.Type = 'Transfer' ) AND ( Transaction.ID = 46129882 )
And finally, my client requested an additional query that was essentially a combination of the first two queries. They wanted a query to return the details of all bank transfers that posted within a certain date range, sorted by the transaction ID, and sub-sorted based on the sequence of the transaction lines. To get the line sequence number ( TransactionLine.LineSequenceNumber ), I joined to TransactionLine table. Here's that final query.
SELECT Transaction.ID AS Transaction, Transaction.TranID, Transaction.TranDate, AccountingPeriod.PeriodName AS PostingPeriod, Transaction.Memo, Account.DisplayNameWithHierarchy, Transaction.ForeignTotal, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit, Currency.Name AS Currency FROM Transaction INNER JOIN AccountingPeriod ON ( AccountingPeriod.ID = Transaction.PostingPeriod ) INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Account ON ( Account.ID = TransactionAccountingLine.Account ) INNER JOIN Currency ON ( Currency.ID = Transaction.Currency ) INNER JOIN TransactionLine ON ( TransactionLine.Transaction = TransactionAccountingLine.Transaction ) AND ( TransactionLine.ID = TransactionAccountingLine.TransactionLine ) WHERE ( Transaction.Type = 'Transfer' ) AND ( AccountingPeriod.StartDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-12-31', 'YYYY-MM-DD' ) ) ORDER BY Transaction.ID, TransactionLine.LineSequenceNumber
Hello, I’m Tim Dietrich. I design and build custom software for businesses running on NetSuite — from mobile apps and Web portals to Web APIs and integrations.
I’ve created several widely used open-source solutions for the NetSuite community, including the SuiteQL Query Tool and SuiteAPI, which help developers and businesses get more out of their systems.
I’m also the founder of SuiteStep, a NetSuite development studio focused on pushing the boundaries of what’s possible on the platform. Through SuiteStep, I deliver custom software and AI-driven solutions that make NetSuite more powerful, accessible, and future-ready.
Copyright © 2025 Tim Dietrich.