Dynamics GP – Error Message Invalid length parameter passed to LEFT or SUBSTRING function in Edit Payables Check Window

I encountered this error when a client was trying to select a vendor in the “Edit Payables Cheque Batch” window. A screen shot of the error is below

The error occurred when trying to select a vendor from the list in the left hand pane of the window.

To troubleshoot this I created a SQL trace to capture all the SQL statements and also any error messages when the error occurred. This highlighted the exact error and offending SQL statement as per below:

Next I copied the offending SQL statement to SQL management studio and ran it in a query window. (I do have to tweak it a little and I also checked it wasn’t going to do any updates or inserts that could cause data issues). This gave me the same error being returned by Dynamics GP

Now it was a question of finding all the LEFT or SUBSTRING statements, commenting them out, and re-running the query until it worked. After some trial and error I found when commenting out the highlighted portion of the SQL statement below it executed without error

Therefore I now know the issue is with the A.KEYSOURC field. At this point I suspected it was blank and therefore the LEN calculation is producing a 0. The alias A has been given to the PM20100 table so I queried this table for the vendor in question and as suspected there is a blank KEYSOURC for this vendor.

I therefore deleted this blank record using a simple SQL script

DELETE PM20100 WHERE VENDORID='<InsertVendorid>' AND KEYSOURC=''

And now I can successfully select the vendor in the “Edit Payables Cheque Batch” window.

I hope this helps explain the issue and also how it can be fixed if anyone else encounters this in the future.

Thanks for reading!

Dynamics GP – The remittance reprint is including a remittance for a different vendor

A client reported that when reprinting a remittance via the “Recreate Check Stub” option it was correctly reprinting the remittance however another remittance for a totally different vendor was also printing at the same time?

The option being used for the remittance reprint is below

To troubleshoot I looked at the main table used on the “Reprint Check Remittance” layout in report writer.

As you can see from the image above this is the “PM Remittance Temp” table which on investigation is the PM80950 table.

When I queried this table in SQL I found a single row and the Vendor ID matched that of the remittance that was being tagged onto the reprint.

I therefore removed this row from the table by running a DELETE statement in SQL and now when I reprint the remittance it prints correctly.

I hope this helps someone else in the future if they come across the same issue.

Thanks for reading

Dynamics 365 Business Central – A quick walk through a Payables Payment Run using Payment Journals

Introduction

Over the last few days I’ve been working through a vendor payment run in Dynamics 365 Business Central and thought it useful to document my journey.

In this step by step post I’ll be generating payments using the “Suggest Vendor Payments” feature and then also going through the process of removing an invoice from a payment as you would in real life.

Setup

To keep things simple before I started I created two vendors, V00080 and V00090, and posted a couple of documents on each vendor. I’ll use these as the basis for my walk through.

On the vendor I set the “Payment Method” as “Cheque” as per below (I’ll likely look at another post for EFT\BACS in the future)

Setup of one of the Vendors I created for my walk through

The documents I posted onto the vendors created the following vendor ledger entries which I’ll be working with:

As you can see I have two invoices on vendor V00080 with a total of £180.00 and two invoices and one credit note on V00090 giving a total of £240.00.

Payment Journal

In order to pay these documents I’ll be using the “Payment Journal” which can be found by clicking the search option and typing “Payment Journal”

Once in the “Payment Journal” page the first thing to select is the batch. As you can see I’ve selected one called PAYMENTRUN


If we drill down on the batch you can see it determines the balancing account and also the number series to use. I’ve select “Bank Account” as the balancing account so the system will create “Bank Account Ledger Entries” that I can later reconcile via the bank reconciliation.

Once we have decided on the batch we can turn to creating the payments.

Suggested Vendor Payments

The process that completes the initial swoop looking for transactions to pay based on a set of criteria is “Suggest Vendor Payments”

On clicking “Suggest Vendor Payments” you are presented with the following options

I’ll focus on the ones I’ll be using as part of my payment run for vendors V00080 and V00090

Last Payment Date:- This is the due date cut off. I’m going to set this to 31/07/19 as I don’t want to pay any invoices after this date.

Find Payment Discounts:- I’m going to switch this on because if there are any payment discounts, I want the payments that are created to reflect them.

Summarise per Vendor:- I’m going to switch this on because I only want to create one payment journal entry per vendor rather than one entry per invoice I’m paying.

Posting Date:- I’m going to set this to 14/08/19 as this is the date I’ll be paying the invoices.

Balance Account Type:- I’m going to set this to “Bank Account” as I want the system to create bank account ledger entries so I can reconcile the payment via bank reconciliation.

Bal Account No:- I’m selecting “CHECKING” as this is the bank I’ll be paying from.

Payment Type:- I’m selecting “Computer Cheque” as on this occasion I’ll be printing cheques.

Vendor No:- I’ll be entering a range of V00080..V00090 as I only want to pay those two vendors

Therefore my options are:

When i click OK I get the following payments generated for me.

Now the system has generated the payments we can go ahead and tweak them for what we actually want to pay.

Editing the Payments

As you can see the system has generated a Payment for Vendor V00080 of £60.00 and a Payment for Vendor V00090 of £240.00

To view the invoices that have been applied on the payment you click “Process > Apply Entries”

If I select this option on the payment of £60.00 on Vendor V00080 you can see the invoices applied:

As you can see above the system has applied INV00002 but hasn’t applied INV0001. This is because INV0001 has a due date of 01/08/19 which falls outside of the due date cut off I entered into the “Last Payment Date” field in the “Suggest Vendor Payments” window. I’m happy with this so I’ll click ok and move to the next payment.

If I highlight the payment for Vendor V00090 and click “Process > Applied Entries” I get the following screen:

The first thing to note is the system has included the credit memo on the payment run. This is great and exactly what I’d expected.

Next I’m going to remove INV90001 from the payment run as I’m not ready to pay this Invoice just yet. To do this I’ll highlight the line and click “Process > Set Applies-to-ID”. Doing this blanks out the “APPLIES-TO-ID” field and also sets the “AMOUNT TO APPLY” and “APPLN. AMOUNT TO APPLY” to £0.00 on the invoice as per below

This all seems fine however when you click OK it would appear the payment amount hasn’t been updated on the Payment Journal to reflect the change I made?

It would seem I have to manually update this or the system would still post a payment for £240.00 onto the account. i.e. the posted payment would remain open and there would be an amount remaining of £30.00 on it.

I therefore edit the payment amount on the header to £210.00 to reflect the new payment amount:

Printing the Cheques

To print the cheques you just need to select “Cheque > Print Cheques”

On clicking this option you are presented with the following options:

I’m going to leave all the defaults and click “Print” and select my printer.

After doing this the flag “Cheques Printed” is checked.

Posting the Payments

Once you have printed the cheques and are ready to post you can just click “Post\Print> Post” as per below

This will create the Vendor Ledger Entries and also Bank Account Ledger Entries as our “Balancing Account Type” is set to “Bank Account”.

If I now view the “Vendor Ledger Entries” I can see the payments have been posted and applied to the invoices.

Conclusion

I hope you have found this write up on the payment run useful. I’m sure I’ve left out some options that can be used and being new to the Dynamics 365 Business Central \ NAV world I’d be very interested to hear of other peoples experiences.

Thanks or reading.