How many ways to spell New Jersey?

When it is time to create reports based on your data, the consistency of entity data becomes crucial. It is easy to miss or report incorrectly sales if you strictly base your aggregates on the data coming from a system that has free form data entry on these key fields. 

New Jersey, N.J, NJ, NJ., N J

All these entries will make it for shipping but not for analytical reporting.  

By using TGPValidate to add a business rule to the Customer Maintenance window (or anywhere in the system that accepts the State field) it will assist us in controlling what goes into the system to facilitate reporting and other activities. 

The included script, will prevent users from entering values to the state field not included in the standard abbreviation: 

— ================================================================
— Author:       TitaniumGP
— Created date: 2/17/2020
— Description:  TitaniumGP Validate for Customer Maintenance window 
— ================================================================
ALTER PROCEDURE [dbo].[Tgpval_rm_customer_maintenance] @vp_CustomerNumber
VARCHAR(100) = NULL,
                                                       @vp_City
VARCHAR(100) = NULL,
                                                       @vp_State
VARCHAR(100) = NULL,
                                                       @vp_Zip
VARCHAR(100) = NULL,
                                                       @vp_CountryCode
VARCHAR(100) = NULL,
                                                       @vp_Country
VARCHAR(100) = NULL,
                                                       @vp_ErrorNum       INT =
0 output,
                                                       @vp_ErrorMessage
NVARCHAR(4000) =  output,
                                                       @vp_Message
NVARCHAR(4000) =  output
AS
  BEGIN try
      SET nocount ON

      SELECT @vp_ErrorNum = 0,
             @vp_ErrorMessage = ,
             @vp_Message = 

      IF @vp_CountryCode = ‘US’
         AND Rtrim(@vp_State) <> 
         AND @vp_State NOT IN ( ‘AK’, ‘AL’, ‘AR’, ‘AZ’,
                                ‘CA’, ‘CO’, ‘CT’, ‘DE’,
                                ‘FL’, ‘GA’, ‘HI’, ‘IA’,
                                ‘ID’, ‘IL’, ‘IN’, ‘KS’,
                                ‘KY’, ‘LA’, ‘MA’, ‘MD’,
                                ‘ME’, ‘MI’, ‘MN’, ‘MO’,
                                ‘MS’, ‘MT’, ‘NC’, ‘ND’,
                                ‘NE’, ‘NH’, ‘NJ’, ‘NM’,
                                ‘NV’, ‘NY’, ‘OH’, ‘OK’,
                                ‘OR’, ‘PA’, ‘RI’, ‘SC’,
                                ‘SD’, ‘TN’, ‘TX’, ‘UT’,
                                ‘VA’, ‘VT’, ‘WA’, ‘WI’,
                                ‘WV’, ‘WY’ )
        BEGIN
            SELECT @vp_ErrorNum = 250

            SELECT @vp_ErrorMessage = ‘Invalid State Abreviation, please review’

            GOTO exitwitherror
        END

      GOTO normalexit
  END try

  BEGIN catch
      DECLARE @vp_ErrorSeverity  INT,
              @vp_ErrorState     INT,
              @vp_ErrorProcedure NVARCHAR(128),
              @vp_ErrorLine      INT

      SELECT @vp_ErrorNum = Error_number(),
             @vp_ErrorSeverity = Error_severity(),
             @vp_ErrorState = Error_state(),
             @vp_ErrorProcedure = Error_procedure(),
             @vp_ErrorLine = Error_line(),
             @vp_ErrorMessage = Error_message()

      GOTO exitwitherror
  END catch

    NORMALEXIT:

    GOTO endsp

    EXITWITHERROR:

    SELECT @vp_ErrorMessage = @vp_ErrorMessage

    ENDSP:

    RETURN ( @vp_ErrorNum ) 

You May Also Like:

How to Prevent Incorrect State or Country Codes in Dynamics GP

How to Prevent Incorrect State or Country Codes in Dynamics GP

Add your Own Business Rules in Dynamics GP Dynamics GP has several business rules, such as entering journal entries with valid accounts or invoices with a vendor that is already in the system.Other rules that users might need are not available. For example, Dynamics...

How to Update the Batch ID of Multiple Orders with TitaniumGP

How to Update the Batch ID of Multiple Orders with TitaniumGP

This document explains how to mass-update the Batch Number of orders in Dynamics GP with TitaniumGP, but it can also be used to mass-update any other field in the header, or in the user define window. One way that companies use the Batch Number in Sales Documents in...

Introducing TGP Resources

Introducing TGP Resources

Getting information from the data dictionaries has never been this easy, TGP Resources allows you to extract information from existing dictionaries for querying or to create SQL views with possible values. Use this information to create reports in SQL Reporting...

0 Comments

Submit a Comment

Your email address will not be published.