Most finance teams have at least one file that “just works the way it works.” It may be a cash collection report from another department, a bank summary, or a workbook that has grown over the years. It has subtotals, grouped sections, and a column for each bank account. It makes sense to the people who use it, but no standard import template can read it.
So someone keys it in by hand. They find each customer, look up their open invoices, work out which invoices the money should go to, pick the right bank account, and enter the payment. Then they do it again for every row. When a customer pays more than they owe, they also have to create an advance and remember to apply it later.
At RKL eSolutions, we build tools that remove this work. This post walks through one of them: a custom AR Automation import that reads a very specific spreadsheet, checks it against Sage Intacct, and creates the payments for you.
Off-the-shelf tools expect data in a standard layout. Real businesses rarely work that way. Our approach starts by listening: we learn how your team works today and which files you already rely on. We then design a solution around that process, build it with standard web technology and the tools Sage Intacct provides, and support it after go-live.
Our approach: Listen, Design, Build, Integrate, Support
For this project, the tool is a Sage Intacct Platform Application. It runs inside Sage Intacct, so users never leave the system they already know, and it uses the same security and permissions.
Below is an example of the kind of file the tool handles (shown with sample data). It is a monthly cash collection workbook with:
The goal: read this file as-is, apply each payment to the right unpaid invoices in the right bank account, and turn any overpayment into an AR Advance.
The source file: custom formatting, grouped sections, and a column for each bank account
The user opens the application in Sage Intacct, chooses the file, and clicks Upload & Validate. In seconds, the tool:
The results appear on screen in a sortable, searchable table. A clear banner sums it up, for example: 3 of 12 rows could not be matched, and 9 valid rows can still be processed. Each error says exactly what went wrong, such as an unknown customer or a bank account with no mapping.
Validation results: errors are explained in plain language before anything is posted
Users don’t need to search a long spreadsheet for the problem rows. Click Download Annotated File and the tool returns the original workbook with each error row highlighted and a note on the cell explaining the issue.
The user can then make a choice:
Either way, nothing reaches Sage Intacct until a person decides it should.
The annotated file highlights the exact row that needs attention
Before posting anything, the tool shows a payment plan. This is where the logic does the heavy lifting. For each customer and bank account, the application looks up that customer’s open invoices in Sage Intacct and decides how the money should be applied:
The payment plan shows exactly how each payment will be applied, before anything is created
|
What the file shows |
What the application does in Sage Intacct |
|
Payment equals the open balance |
Creates one AR payment from the correct bank account and applies it to the customer’s open invoices, oldest due date first. |
|
Payment is more than what is owed |
Pays every open invoice, then records only the remaining amount as an AR Advance so the credit is ready for future invoices. |
|
Customer has no open invoices |
Records the full amount as an AR Advance so the cash is captured and can be applied later. |
|
Payment is less than what is owed |
Applies the money to the oldest invoices first until the payment is used up. |
|
Customer or bank cannot be matched |
Flags the row as an error, prevents it from being processed, and highlights it in a downloadable copy of the file. |
Each line of the plan shows the customer, the bank account, the amount, and the invoices that will be paid. Color-coded tags make the outliers easy to spot, such as “No invoices — AR Advance only” or “Overpayment.” The tool also fills in the details users would otherwise enter by hand:
The application also only applies payments to invoices that are still open and in the matching currency, so cash is never applied to the wrong kind of invoice.
When the plan looks right, the user clicks Create AR Records. The screen updates live as each record is created in Sage Intacct, moving from Pending to Running to OK. Successful records show their new Sage Intacct record number.
Records are created one by one with live status
If Sage Intacct rejects a record, for example because a payment date is earlier than the invoice date, the message is shown right next to that line. The other records still go through, and the summary at the top shows exactly what happened, such as 7 of 9 succeeded and 2 failed. Users know at once what needs follow-up.
Final results: successes and failures are clear at a glance
Hours back every month. A file that took hours to key in by hand is uploaded, checked, and posted in minutes.
Fewer mistakes. The tool chooses the invoices, the bank account, the date, and the check number the same way every time. No more typos, missed invoices, or payments posted to the wrong account.
Overpayments handled correctly. Extra cash is recorded as an AR Advance automatically, so it is never lost or left unapplied.
Problems found before they are posted. Validation catches unknown customers and bank accounts up front, and the annotated file shows exactly where to look.
People stay in control. Users review the plan and decide what to process. The automation does the work, but people make the call.
Time for higher-value work. Instead of entering data, your AR team can focus on collections, customer questions, and analysis.
This example shows a cash collection workbook, but the same approach works for almost any file your team receives in its own format: bank exports, lockbox reports, third-party statements, or files from other departments. We build the tool around your file and your business rules, not the other way around. We also give you a clean, visual interface that anyone on the team can use.
If your team still keys in data from a spreadsheet every month, let’s talk. RKL eSolutions can help you turn that file into a few clicks.
For more helpful tips and tricks about Sage Intacct, subscribe to our blog and stay up-to-date with our latest tutorials. We're dedicated to providing valuable resources for businesses looking to optimize their financial management with Sage Intacct.
Sage Intacct R4 2026: What You Need to Know about the Latest Release
Turn Financial Data Into Decisions with Sage Intacct’s Finance Intelligence Agent
Create Expanded Columns and a Summary for Reports in Sage Intacct