This article is relevant if you work with NetSuite bank deposits and are challenged to reference over 10,000 payment line records.
Background
A client approached us to leverage our leadership in supporting their high-volume, membership-driven eCommerce website. The site, which uses the Stripe Payment gateway, automatically charges credit cards to collect customer deposits for each order. With millions of customers, the daily order volume exceeds 100,000 transactions. These orders are sent to NetSuite as conventional Sales Orders with linked customer deposits.
Although the client staggered their membership auto-charge program, they still faced over 100,000 customer deposit records to reconcile daily. Stripe would deposit funds into the client’s operating account, representing daily customer deposit activity, refunds, and dispute transactions.
While the exact number is unknown, the challenge arises from NetSuite’s native bank deposit record, which doesn’t support referencing more than 10,000 line entries. We needed a solution to seamlessly manage the transaction accounting and automation for this high volume.
Best Practice Pattern for High-Volume Electronic Payment Processing
Had our firm initially designed the eCommerce integration, we would have recommended the Account Clearing Model, as outlined in my 2018 article. This pattern is further detailed in my 2022 article, Solve for High Volume NetSuite Cash Receipt Payment Patterns.
However, the client faced significant challenges with cash reconciliation and needed our help. Their existing setup used customer deposit transactions linked to the NetSuite Undeposited Funds account, leaving them with virtually no reconciliation capability.
General Challenge with NetSuite Undeposited Funds
The NetSuite Undeposited Funds account is designed to hold cash receipts before they are reconciled with the bank. Ideally, a bank deposit record references these cash receipts (customer deposit records in our case) and ties them to the amounts deposited at the bank. When done correctly, NetSuite’s native Account Reconciliation tools can be used to reconcile cash.
This pattern works well for low-volume situations but is impractical for high-volume environments. The main challenge is that the bank deposit record structure is limited, allowing it to reference fewer than 10,000 lines.
Solving for the NetSuite Bank Deposit Line Limitation Challenge
Boban D., Senior Technical Analyst, shared his concerns about overcoming the line limitation in NetSuite bank deposit records. Despite trying various SuiteScript approaches, he found no success. After listening carefully, I proposed an unconventional pattern to address the issue. Boban implemented it, and through trial and error, we discovered NetSuite script limitations with this record and ultimately solved the challenge.
The basic assumptions were as follows:
- Transaction Streaming: Transactions stream into the NetSuite system from the eCommerce site in real time, constantly creating new customer deposit records. The number of unreconciled customer deposits will keep growing unless addressed promptly.
- Stripe Payout Record: We worked directly with the Stripe API to gather a daily payout record, detailing the actual bank deposit contents. This record acted as the controller for the solution. Controller records are a powerful pattern in batch processing.
- Customer Deposit Payout References: Once we had the Stripe Payout Record, we had information to stamp each customer deposit record with a cross-reference. Now we can understand which customer deposit records are part of the daily payout. Summarize to confirm we are under control.
- Refunds & Deposits: The Stripe Payout Record provided information about refunds and disputes. We created and stamped the appropriate NetSuite records, similar to the Customer Deposit records. Again, summarize and confirm we are in control.
Click the image to see the full screen of the payout record.
With these assumptions clarified, common sense suggests the following:
Since we have the Stripe Payout Record references on the Customer Deposit records, we could create a Bank Deposit record and simply add those stamped customer deposit records as lines. This approach works in low transaction volume situations. However, with over 100,000 daily customer deposits, SuiteScript simply fails when attempting to handle the Bank Deposit record. We cannot select the desired customer deposit records without encountering errors. Even if we could select the detailed records, the bank deposit record won’t commit/save to the database if it references more than ~10,000 lines (again, exact number unknown).
The High NetSuite Bank Deposit Transaction Volume Solution
Given the assumptions, here’s the pattern we designed to solve the challenge:
- Create Arbitrary Bank Deposit Records: NetSuite’s algorithms fail with high volumes, but we found that we could reliably reference and commit 2,500 payment receipt lines. Therefore, create as many Bank Deposit records as needed until all the cash receipts are tucked into a respective bank deposit record. At this point, any new streaming in customer deposit records represents today’s undeposited funds — which we don’t care about because our Stripe Payout Record is solving to deposit yesterday’s funds.
- Review and Clean Bank Deposit Records: With 2,500 customer deposit lines in each bank deposit record, use SuiteScript to review each customer deposit record. If a customer deposit record does not reference the Stripe Payout Record being reconciled, remove that line — it is part of a different payout that will be dealt with later. Commit the bank deposit record after checking each line for relevance. This part of the algorithm was the breakthrough!
- Confirm and Summarize: Continue the previous step for each bank deposit record. Each Bank Deposit record should have fewer than 2,500 records. Summarize all the Bank Deposit records, ensuring they total the expected amount on the Stripe Payout Record.
The Stripe Payout Record acts as a control record. Click on the images to see how we use it to cross-reference all detailed transactions and bank deposit records, explaining the amounts deposited at the bank.
Helping Scale NetSuite High Transaction Environments with Strong Leadership
Our firm is assisting more clients in driving high-volume transaction processing within the NetSuite environment. The common sense approach to producing integrations must be re-evaluated when encountering high transaction volumes and loads—true for any system, not just NetSuite. While we typically avoid the native bank deposit record in favor of our suggested account-clearing pattern, we successfully worked through the perceived limitations in this case. The client was elated and could finally reconcile cash daily using built-in tools and cross-references to our payout control records. They say they finally found a firm with the leadership they need to help them with their NetSuite challenges.
These algorithms and patterns can inspire solutions in other areas where NetSuite line limitations may present challenges. We offer these algorithms without a license charge to any other NetSuite end-user account experiencing similar issues.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you have a high transaction volume challenge in NetSuite, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
