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 – Error Message PERIOD_KEY_INVALID when submitting VAT Return to HMRC

I had a client report this issue recently which was preventing them submitting the VAT return to HMRC via the government gateway. The exact error is below

After some digging it seems the period key is passed down from HMRC when the obligations are retrieved therefore its strange that the error is produced when submitting the VAT return as it should be using the same period key.

I looked at the TX00300 table (VAT Return Header) and I can see the PeriodKey was blank for the VAT Return ID in question

I then checked a clients system who I know has managed to process their VAT return successfully and this showed a PeriodKey in the TX00300 as per below

Therefore I can only assume that after pulling down the obligations either the PeriodKey wasn’t written to the TX00300 table correctly or the client accidentally changed a date in the VAT Return window which blanked the PeriodKey. Then, when they then tried to submit the VAT return they received the “PERIOD_KEY_INVALID” error.

To resolve the issue I had the client delete and recreate the VAT return ensuring no dates were altered after selecting the obligation and this then worked fine. (I did want to work through the whole process with them so I could check the PeriodKey in the TX00300 at every stage however unfortunately they processed everything before I had chance i.e. I wanted to check the PeriodKey value after getting the obligations, and then check it again after calculating and then prior to submitting)

In was a simple fix in the end but it was interesting piecing things together.

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