Discussions
Stay up-to-date with the latest news from NetSuite. You’ll be in the know about how to connect with peers and take your business to new heights at our virtual, in-person, on demand events, and much more.
New AI Community Guidelines. Please review and follow them to ensure AI use stays safe, accurate, and compliant.
Please note that on Saturday, April 11, 2026, at 8:00 PM Pacific time, our Case Management System will undergo a scheduled maintenance for approximately 30 minutes. During this time, case creation via SuiteAnswers will be unavailable and inbound calls will be routed to Customer Service.
How to get account balances as of a specific date via SuiteAnalytics or API?
I’m building an integration that pulls account balances from NetSuite to use as opening balances in our internal system. I want to retrieve balances as of a specific date — for example, the balance of each account on January 1, 2024 — and I’d like to do this programmatically, preferably via SuiteAnalytics Connect (ODBC) or SuiteQL.
I currently:
- Use TransactionAccountingLine + Transaction + Account tables in SQL
- Filter posting = 'T' and trandate <= '2023-12-31'
- Group by account to get balances up to a specific date
I also calculate Retained Earnings separately by summing P&L accounts for prior years, and assign that total to our retained earnings account (e.g., 301000). This mirrors the logic in NetSuite’s Trial Balance report.
0