#GPPT Changing Address IDs for Customers and Vendors

If you’ve ever needed to change an existing Customer ID or Vendor ID you would have found that there are tools available, but if you need to change the Address ID values for addresses associated with Customers and Vendors, you would you would be in for an arduous, manual, time consuming task as there are no tools available.

This article provides free sample projects that add these tools to Microsoft Dynamics GP using GP Power Tools – Developer Tools module. The code can be downloaded and installed easily.

Sometimes when a company changes its name, the previous ID value used for Customer ID or Vendor ID no longer makes sense. Because the ID is used as the primary key for the Customer and Vendor records, it cannot be changed unless you also update everywhere it is used in the company database. Making these changes is relatively simple: install the Professional Services Tools Library (PSTL aka “PiSToL”) from the Microsoft Dynamics GP DVD image (at no charge) and use the Customer Combiner and Modifier and Vendor Combiner and Modifier utilities.

However, if you want to change the Address IDs used on your Customer and Vendor records there is no tool available.

For example: If you have used Address IDs such as PRIMARY and WAREHOUSE, but would now like to change them to HEAD OFFICE and MAIN STORE, or you would like to just number the addresses 0, 1, 2 etc.

The following GP Power Tools projects will provide the desired functionality. There is a CUST ADDR REPLACE project for customer addresses and a VEND ADDR REPLACE project for vendor addresses.

How it works

The find and replace code works by searching all tables in the current database for columns that are known to be used to store Address ID values. The result of this search is stored in a temporary table. Then, using the information from the temporary table, the code will identify how many records in each table match the source values and display the results.

If the Preview Only option is unchecked, the code will use this second result set of records that need to be changed to generate and execute a series of update statements to change the data.

For the code to work reliably, the list of fields used for Customer ID (Customer Number) and Vendor ID as well as the Address ID need to be included in the SQL query. All the field/column names for the core Microsoft Dynamics GP have been included.

Note: It is highly recommended to use the Resource Information window in GP Power Tools to search for the Field Technical Names “Customer ID”, “Customer Number”, “Vendor ID”, “Address ID” and “Address Code” to get a list of fields that include search characters. Make sure that the Search is set for Contains and not Case Sensitive. Selecting a name from the list can then provide the physical name for each field.

Once installed you will find the “Address Find and Replace” under the Additional menu on the Customer Maintenance and Vendor Maintenance windows. When selected it will open a dialog for you to select the parameters for the find and replace scripts to use. Enter a New Address ID and uncheck Preview Only to actually make changes.

Please ensure a backup has been made before executing this code with Preview Only not selected.

The Projects

Below is a screenshot of the CUST ADDR REPLACE project showing the components:

Trigger CUST ADDR REPLACE

This trigger is used to add the “Address Find and Replace” option to the Customer Maintenance window. When selected the trigger reads the current record displayed (if any) and populates then opens the Parameter List CUST ADDR REPLACE. When OK is clicked on the Parameter List, the Runtime Execute Script CUST ADDR REPLACE is executed.

Runtime Execute Script CUST ADDR REPLACE

This script will load and execute the SQL Execute Script CUST ADDR REPLACE and refresh the Customer Maintenance window if necessary.

SQL Execute Script CUST ADDR LOOKUP

This script is used by the Parameter List CUST ADDR REPLACE to provide an Address ID SQL Lookup which is dependent on the Customer ID already selected.

SQL Execute Script CUST ADDR REPLACE

This is the SQL Script which will locate table columns that are used to store Address ID values across the entire database and then display a list of where the source values have been used across the tables. If Preview Only mode is not selected, it will also run an update to find and replace the database values.

Parameter List CUST ADDR REPLACE

This is the Parameter list which provides the interface to the scripts and allows the Customer ID, and the source and target Address IDs to be selected as well as whether to only preview the number of records that will be changed.

Below is a screenshot of the VEND ADDR REPLACE project showing the components:

Trigger VEND ADDR REPLACE

This trigger is used to add the “Address Find and Replace” option to the Vendor Maintenance window. When selected the trigger reads the current record displayed (if any) and populates then opens the Parameter List VEND ADDR REPLACE. When OK is clicked on the Parameter List, the Runtime Execute Script VEND ADDR REPLACE is executed.

Runtime Execute Script VEND ADDR REPLACE

This script will load and execute the SQL Execute Script VEND ADDR REPLACE and refresh the Vendor Maintenance window if necessary.

SQL Execute Script VEND ADDR LOOKUP

This script is used by the Parameter List VEND ADDR REPLACE to provide an Address ID SQL Lookup which is dependent on the Vendor ID already selected.

SQL Execute Script VEND ADDR REPLACE

Parameter List VEND ADDR REPLACE

This is the Parameter list which provides the interface to the scripts and allows the Vendor ID, and the source and target Address IDs to be selected as well as whether to only preview the number of records that will be changed.