idModifier changes ID fields throughout the system. Use idModifier to fix a mistake in an ID field, for to update to a new naming convention.
idModifier is pre-loaded with column names for the following fields:
- Customer Number
- Item Number
- Serial/Lot Number
- Site ID
- Vendor Number
For pre-loaded fields the utility has an internal list of all of the common variations of the field name used by different modules. For example, there are 20 variations of the serial/lot number field, such as:
If you have a 3rd Party Product or Customization which contains the field you are trying to change, additional fields can be manually added to the field list.
idModifier can also perform a system wide search and replace on any string field. Sales Prospect ID is a field which is not pre-loaded, so it could be modified using the “Any Field” search. With this method the system will first look for all tables that have a string field containing the specified “old value”. If the Prospect ID is “BLUEBELL”, it would look at all string fields in all tables for the value “BLUEBELL”.
PERFORM A BACKUP BEFORE STARTING! This utility will make changes at the database level to many tables. Try this in a TEST Company before making changes in LIVE.
Navigation: Tools >> Setup >> Company >> Setup GP PowerPack
Mark the box next to MODULE: idModifier, then click DONE.
Restart your Dynamics GP client to enable this feature. Other users must restart their Dynamics GP client to enable this feature.
Navigation: Tools >> Utilities >> System >> idModifier
ID Type: Select the ID Type (such as Item Number or Site ID). The system comes with a large number of pre-loaded fields for each ID Type. See Adding Fields, below.
For ID Type = Lot/Serial Number, select a serial or lot controlled Item Number. These fields will only be enabled if they are needed based on the selected ID Type.
Enter the current value (the one that needs to be fixed). The current value is entered in the “Site ID” field shown above. This field will change depending on the selected ID Type.
Enter the new value in the New Site ID (this field will change depending on the ID Type).
FIND: You must run FIND before modifying the ID. Click the FIND button to locate every table and field containing the ID in the database. This will identify not only core Dynamics GP tables that contain the “old value”, but also any custom or 3rd party product tables, as long as the ID is contained in one of the pre-loaded Fields for the ID Type.
FIND also searches all of the identified tables to see if the “new value” already exists. This prevents creating duplicate records during the update process. idModifier does not “merge” IDs. For example, if you have Sites N1 and N2 (two different “north” locations), you could not change both of them to NORTH.
The scrolling window will show a list of tables and fields identified by the search that contains the “current value”.
MODIFY: Modify cannot be run until FIND has run successfully. idModifier will run through the list tables it found in the FIND process that contain the “old value”, and update them with the “new value”.
NOTE: the ANY FIELD option will search every string field in Dynamics GP for the specified value (during FIND). This can be very time consuming as there are a very large number of string fields. However, this approach will ensure that every instance of a string value is replaced.
For example, if you need to fix an Item Number and you know that you also put the Item Number manually into one or more User Defined Fields, FIND will locate the value in the User Defined Fields. The Any Field method will locate the given Item Number no matter where it is located.
This does not, however, perform a “contains” search, so if the Item Number also exists in longer string fields that have additional information, such as descriptions or text, it will not be replaced in these fields.
Since Any Field is searching all string fields for the “old value”, it may locate the ID in the wrong fields. If WAREHOUSE is used both as an internal Site ID, and as an Address Code for customers, a search on WAREHOUSE would identify and attempt to change all instances of WAREHOUSE. It is important when using Any Field to review the FIND Results to ensure it is only picking up the desired “old value”.
There are a large number of different column names used by different modules for each one of the ID Types. After selecting ID Type, click the GoTo button and select FIELDS to view the pre-loaded field list.
The Fields window shows the pre-loaded field names for each ID Type.
If a 3rd Party Product or Customization contains the ID you are changing (i.e. Site ID), but it uses a different name for the field, add it to the Fields List.
The pre-loaded fields cannot be modified or deleted.