Fixing Microsoft Dynamics GP and Illegal Characters error messages

By now you probably read David Musgrave's series on Microsoft Dynamics GP and Illegal Characters and have come to realize that the single quote character is perhaps one of the biggest culprits in the errors you are receiving when running reports such as your age trial balances in both receivables and payables. It's enough for the single quote character to be in your customer ID, vendor ID, or even document numbers to cause some error while running a report or inquiring a transaction.

The following is an actual case reported by a user on the Dynamics GP newsgroup when running the Payables Historical Aged Trial Balance report:

The stored procedure pmHistoricalAgedTrialBalance returned the following
result: DMBS: -127, Microsoft Dynamics GP: 0.

After some research the user realized they had a few document numbers that contained the illegal single quote character, for example INV'23002. Note that this case is specific to the document number.

So, what to do if you are a victim of the single quotes?

1) You will need to first identify the tables possibly affected. In particular, you will need to search all tables in the database that may contain the column storing the data -- in the case of the example, DOCNUMBR. For this you can use a script I published before here. You may also use the script provided by David Musgrave on Part 2 of his series, which would allow you to search through all your company databases if experiencing the issue in more than one company.

2) You will then need to identify the specific records affected by a single quote. Once the tables have been identified, you can do the following:

SELECT * FROM PM00400 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10201 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10300 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM10400 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM20000 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM30200 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM50100 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM PM80200 WHERE DOCNUMBR LIKE '%''%'
SELECT * FROM GL20000 WHERE ORDOCNUM LIKE '%''%'

NOTE: The GL20000 and GL30000 tables will store this information in the ORDOCNUM column.

3) Once you identify the document numbers you will need to have a plan to correct them. Your plan should include making sure the new resulting document number is not going to create a duplicate situation with an already existing record.

4) Update the affected records.

UPDATE PM00400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10201 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10300 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM10400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM20000 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM30200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM50100 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE PM80200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'
UPDATE GL20000 SET ORDOCNUM = 'NEW_DOC_NUM' WHERE ORDOCNUM = 'XY''Z'

Note the use of a double single quote to format the document number string properly and avoid an early string termination.

Since this situation can present itself in other modules, be sure to identify the specific module affected and the tables that may be impacted based on the data column storing the informatin. Hope this helps and complements the articles and information on Developing for Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/