An Easy Way to Generate a Large Cash Receipts EFT Batch from a Large Invoices Batch

If you are looking for a convenient way to generate EFTs from batches of SOP invoices, check out my step-by-step breakdown below. It’s a little heavy on screenshots, but hopefully, they help visually guide you through the process.

First, create a Lockbox using the “Lockbox Maintenance” window.


Then create an invoice batch, and before posting the invoices, run the following Smartlist:


Send the Smartlist to Excel and verify your line total equals the total of your batch. Your spreadsheet should resemble the image below:


Highlight the “Document Amount” column and the “Subtotal” column in your spreadsheet, and then open the “Format Cells” window. Click the “Number” tab, select “Number,” and make sure you have two decimal places and the “Use 1000 Separator (,)” box is not checked. Click “OK.”


Next, highlight the “Document Date” column your spreadsheet, open the “Format Cells” window, click the “Number” tab, select “Date,” and choose the MM/DD/YY format. Click “OK.”


Save your spreadsheet as a CSV file in the “Lockbox” folder, which is located in the “ClientShare” folder.


Your file should look like the one below:


Once you confirm that you have the right file, you can post the invoices.

Click the “Transactions” button, then open the “Sales” drop-down menu and select “Lockbox Entry” (highlighted below). This will open the “Lockboxes” window.


Select the Bank ACH Lockbox to open the “Lockbox Entry” window.


Then enter a Batch ID. I suggest INVEFT followed by the date (MM/DD/YYYY).


Once you finish entering the Batch ID, a window will pop up asking you if you want to add the batch. Click “Add.”


This should open the “Receivables Batch Entry” window. Verify that the “Checkbook ID” is correct, and click “Save” (highlighted below).


After you click “Save,” you should be taken back to the “Lockbox Entry” window. Click the “Transactions” button (highlighted below) to open the “Lockbox Transactions” window.


If everything is correct (green check marks all the way down), click the “Create Batch” button (highlighted below). This will create the Cash Receipts Batch.


Next, you can go to SQL to run the following query: update RM10201 set EFTFLAG=1 where EFTFLAG=0 and BACHNUMB Like ‘INVEFT%’. This should set the Cash Receipts to EFT (see the highlighted checkbox below).


Then go back to the “Receivables Batch Entry” window. Make sure the System Date is set to the exact date that you want to generate the EFT file, and then click the “Post” button (highlighted below).


Click the “Transactions” button, then open the “Sales” drop-down menu and select “Generate EFT File” to open the “Generate EFT Files” window.


Again, make sure the System Date is the exact date you want to generate the EFT file for the bank, and then click the “Generate EFT File” (highlighted below) to generate the file one more time.


The file should be located at E:\ClientShare\ACH and called “Collections” plus whatever you named your EFT format.

Finally, upload the file to the bank for processing.

I hope you found this helpful, and if you have any questions, feel free to contact me.


About sdnprs

Graduated from the University of Wisconsin earning a Bachelor of Arts degree in Accounting. I have been with SDN since 1989 . My previous work experience includes five years as a controller and prior experience in a large corporation as a financial analyst. As a computer consultant, I am involved in all aspects of serving the clients computer and information analysis needs. My experience in consulting extends over twenty years with a wide variety of commercial and non-profit organizations. I work with clients in wholesale distribution, construction, manufacturing, health care, and various financial and service organizations. I work directly with management to ensure their reporting requirements are met. As an implementer of Microsoft Great Plains and CRM software, I have received certification on SQL Great Plains implementation and installation, Frx Forecaster , Scribe Software, Salespad, Key2Act Job Cost and Service Management and Certified for Sage Intacct the AICPA recommended product. As a Certified Public Accountant, I am a member of the American Institute of Certified Public Accountants and of the Jacksonville Chapter of the Florida Institute of Certified Public Accountants.
This entry was posted in Dynamics GP and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s