#GPPT Preventing Invalid Characters in Customer, Vendor and Item IDs

This is another “Must Have” free customization example for you Microsoft Dynamics GP system when you have GP Power Tools – Developer Tools module.

When creating new Customer, Vendor and Item cards, it is possible to include certain characters in the ID fields which can cause issues with Microsoft Dynamics GP, its ISV products and integrating products.

For example: a single quote (‘) can cause the early termination of a string when incorrectly passed to a stored procedure or used in pass through SQL code without using the SQL_FormatStrings() function. Also a backquote (`) can cause problems with the Microsoft Dynamics GP Report Writer as the character has special meaning to the report writer.

Rather than defining a large list of illegal characters, it is easier to create a list of valid allowed characters and identify when any characters outside of that list are included in the field and then take action to ensure that the field is corrected.

The allowed characters are as below:

• Numbers 0 to 9
• Letters A to Z
• Space
• Symbols . (Period/Full Stop), _ (Underscore) and – (Dash)

The code also will remove any leading or trailing spaces and ensure that the field is not empty.

Note: In standard Microsoft Dynamics GP, it is possible to enter an empty value or start with a space, if you enter a character as the first character, then a space and optional other characters. Then delete the first character to leave a leading space or only a space. This code will prevent this invalid data being entered.

Below is a screenshot of the VALIDATE ID project showing the components:

Trigger VALIDATE ID CUST, VALIDATE ID, VEND, VALIDATE ID ITEM

These triggers on the Customer Maintenance, Vendor Maintenance and Item Maintenance windows run after the ID field: Customer ID, Vendor ID or Item Number (respectively) are entered, just before the standard GP code runs to validate that no illegal characters are used. If illegal characters are found, the appropriate message is displayed, and the field will need to be entered again.

Note: Additional symbols can be added into the allowed list by inserting them between the double quotes where the commented in the code.

SQL Execute Scripts VALIDATE ID CUST, VALIDATE ID, VEND, VALIDATE ID ITEM

These SQL scripts will check the Customer Master (RM00101), Vendor Master (PM00200) and Item Master (IV00101) tables for any existing records containing illegal characters in the ID field.

Note: Additional symbols can be added into the allowed list by inserting them before the period symbol as commented in the code. The Dash must remain the last symbol in the list to ensure that SQL Server does not interpret it as meaning “to” as in 0-9.

Messages VALIDATE ID CUST, VALIDATE ID, VEND, VALIDATE ID ITEM

These messages are displayed by the triggers if illegal characters are found in the ID fields. They can be updated to handle multiple languages on an international or multi-lingual system.

Downloading and Installing

Download the example code, import using the Project Setup window (without any project showing, select the path to the xml file and click Import):

• GP Power Tools Settings Validate Id.dbg.zip

The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.

More Information

For more information see:

• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

Hope you find this example useful.

David

This article was originally posted on http://www.winthropdc.com/blog.