Error Raising In-Transit Transfer in Microsoft Dynamics GP

I was doing some work for a client the other day who was implementing some new functionality in Microsoft Dynamics GP. One of the areas they were working on was Inventory Control. They were testing that everything was working correctly and tried to raise an In Transit Transfer (Inventory Control area page » Transactions » in-Transit transfer Entry); when they entered the Item Number and hit tab the following error appeared:

Microsoft Dynamics GP
A get/change first operation failed on table 'IV_TRX_WORK_LINE' failed accessing SQL data.

Clicking More Info returned this:

Microsoft Dynamics GP
[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the varchar value 'ITT0000005       ' to data type int.

The error seemed somewhat unusual, as there is no reason to convert the Document Number to an int. I ran a profile using SQL Server Profiler and found the returned SQL command was the following:

SELECT TOP 25 IVDOCNBR,IVDOCTYP,ITEMNMBR,LNSEQNBR,UOFM,QTYBSUOM,TRXQTY,UNITCOST,EXTDCOST,TRXLOCTN,TRNSTLOC,TRFQTYTY,TRTQTYTY,IVIVINDX,IVIVOFIX,IVWLNMSG,DECPLCUR,DECPLQTY,USAGETYPE,Reason_Code,DEX_ROW_ID FROM ISC01.dbo.IV10001 WHERE (IVDOCNBR = ITT0000005 AND IVDOCTYP = 11) ORDER BY IVDOCTYP ASC ,IVDOCNBR ASC ,LNSEQNBR ASC

As you can see, the highlighted section is the Document Number from the window, but it is not being wrapped in single quotes which is required when selecting a string of text.

This is a bug in the window which can be reproduced at will; testing in Fabrikam works with an all numeric document Number, but as soon as you change it to alphanumeric you get the same error.

The workaround is to change the Next Document Number for In-Transit Transfer in Inventory Control Setup (Inventory Control area page » Setup » Inventory Control) to all numeric (such as 0000001) and delete any partially entered transfers. Once you’ve done these two things, you’ll be able to raise In-Transit Transfers without further problems: