By using this website, you agree to our Terms of Use (click here)
In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Sincerely,
Tim Rodman
I come from the Sage 300 world where there is a toll called PrintBoss. printBoss handles the following commonly requested functions in one app.
- print full check on blank check stock (MICR, bank info, etc.)
- print signature(s) conditionally based on dollar amount
- add logos
- create ACH files
- create positive pay file
- print and/or email remittance reports
I am struggling to accomplish all of these in Acumatica.
Which of these should I be able to do with export scenarios and native Acumatica?
Is there a customization that does all these functions? I see separate customizations from different ISV's (MaxQ-Laserchecks, SWK-positive pay and electronic funds, PC Bennett-positive pay. Does anyone have any experience with any of these and if/how they work together?
Recommendations on tools to use to accomplish laser check printing on blank check stock, creating positive pay file, and creating ACH file including remittance advice would be greatly appreciated.
Hi Debbie,
In my opinion, Acumatica has an overall philosophy: They make the technology tools, then they stop and let others build with those tools.
With Sage, the technology is not as great, but they take the time to build more real-world solutions with their technology.
That's my personal opinion at least.
PrintBoss is a great example of this. They have more solutions build-out.
Here is my take on your points:
- You can print the full check information (MICR, bank info, etc.) on check stock. See this post for more information.
- I don't see why you couldn't upload multiple signatures and use the Name field on the Cash Accounts screen to mark them as "First" and "Second", then do some joins to get both signatures, then use the VisibleExpr field in Report Designer to control whether one or both print based on dollar amount. But it's kind of clunky. The MaxQ product that you mentioned added the extra data entry fields to the Cash Accounts screen and built the reports to make it more turnkey.
- You can add logos using Report Designer.
- Acumatica can do ACH (click here), but it's kind of clunky because you have to work with an export scenario which isn't the most pleasant thing to work with.
- The SWK and PC Bennett products are the only think that I know of for positive pay. But you could also just create a report that accepts a date range as parameters. Then export the report to Excel and save as CSV.
- You can have a separate remittance report print by checking the Print Remittance Report checkbox on the Settings for Use in AP tab of the Payment Methods screen. Then set the Lines per Stub field to the number of lines that should trigger the report to fire. I'm not aware though of a way to automatically email these out to each vendor.
Tim, thanks for sending us in the right direction. Debbie and I worked together to create a GI for positive pay. here's a copy of the KB we created in our system:
Use these steps if you need to create a simple, PostivePay record set from your Acumatica check run where you can export the results to Excel, then upload that file to your bank. We will use the powerful Generic Inquiry function of Acumatica to design a query that pulls the Positive Pay records.
Instructions
- We must first understand the tables involved.
- PX.Objects.AP.APPayment (to pull a list of payment records)
- PX.Objects.AP.APContact (to pull Remit-To Name and in this example, the ATTN field because it contains the name of a factor for the original payee)
- PX.Objects.AP.APAddress (to pull and address fields, in case the bank requires)
- Table Relations
- Because remit to information is needed in this case, we are linking to APAddress and APContact from APPayment. The behavior in Acumatica's tables is that the Default Remittance information as stored on the Payment Settings tab of the vendor master is stored in the APAddress and APContact tables. However, if on a specific payment the remittance is overridden, then a new record gets added to APAddress and/or APContact depending on whether you overrode one or both.
- from APPayment, link to APAddress where remitAddressID = addressID
- from APPayment, link to APContact where remitConactID = contactID
- Define your Parameters. In our case, the customer has a single checking account and wants to upload a PostivePay file once a day. So, we defined these parameters:
- PaymentDateBegin
- PaymentDateEnd
- For Conditions, we defined how the data should get filtered
- APPayment.DocDate >= PaymentDateBegin
- APPayment.DocDate <= PaymentDateEnd
- and APPayemnt.DocType must not be a Debit Adj, Voided Refund or Voided Check
- In our case, Grouping and Sort Order was not needed
- Results Grid Output (columns of the Excel file to be uploaded as a Positive Pay file on the Bank's portal)
- Hard coded Account Number for the checking account (since we have only on checking account)
- Check Number
- Check Amount
- Vendor Name. In our example, remittance goes to a factor rather than the original payee. The bank required that we concatenate the original payee (stored in the Remittance Contact Full Name field) and factor (stored in the Remittance Attention field).
- We must test for whether the Attention field is in use. If it is NULL, then concatenation of the two fields returns a NULL even when Full Name has a value. This formula tests for that =Concat( [APContact.FullName], (IIf((IsNull([APContact.Attention], 'x'))='x',' ',Concat(' - ',[APContact.Attention]))))
- Check Date
- Generic Inquiry results show a couple examples of the results. Once the from and to payment dates are entered, results are shown.
- Check 1004 shows an example of the where the Remittance Fullname and Attention where overridden on the individual payment transaction.
- Check 1005 is an example of where the Remittance was not overridden on the payment, so the Fullname is coming from the Vendor Master remittance, but there was no value in the Attention field on the Vendor Master.
- Finally by clicking the Export to Excel button on the toolbar, an excel file is created and ready for upload to the bank portal
Thanks so much for sharing this Mark. This is awesome!
Mark,
This write up helped me greatly and appreciate you doing so! I am not a DB guy so I struggle.
I am still working with this to get everything aligned for our banks requirements.
I am not the best with building GI's, Tim has helped me in the past.
I am having an issue that I cannot remember what causes it. I have a duplication of the check multiple times in the GI?
try grouping by check number and then select max aggregate function for check amount on the results grid