ID Modifier

idModifier

idModifier is a utility for changing ID fields throughout the system. Use idModifier to fix a mistake in an ID field, or to update to a new naming convention.

idModifier works differently than other ID conversion utilities. Other utilities have a predefined list of tables and fields that are updated. For example, when converting a Customer Number, a specific list of tables is updated, which means it can miss 3rd Party Products and even GP modules like Project Accounting and Field Service. In contrast, idModifier is “trained” by providing it with sample data. It searches the database for the sample data to identify the tables and fields containing the samples. From this it develops a target list of tables and fields that need to be updated. This method allows it to identify core GP tables, 3rd Party Product tables, and even tables belonging to customizations.

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.

IMPORTANT:

Using idModifier can cause severe database corruption if not performed carefully. Since idModifier searches for tables to update based on user provided samples, it can incorrectly include a table if the sample data is not unique, or miss a table if it is not provided with good sample data. For example, if WAREHOUSE is used as an Address Code and Location Code, idModifier will find tables including both.

It is imperative that you create a database backup prior to running the utility.

It is imperative that you thoroughly test the impact of this utility in a TEST COMPANY before running updates on a live database.

WilloWare Inc assumes no responsibility for damage to your database due to the use of this utility. Run this utility at your own risk.

Setup

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.

Adding IDs

HelpID: 502622113

Navigation: Tools >> Setup >> System >> idModifier Setup

Use the ID Add window to “train” idModifier to update an ID field in Dynamics GP. The training process works as follows:

  • Provide a sample value for the ID field you want to modify. For example, to train it to modify Item Numbers using the Fabrikam database, you might give it the Item Number “128 SDRAM”.
  • Click the FIND button to search for the sample data
  • idModifier looks at every string field in every table in the company database. When it finds a table and field containing the sample data it records the Table Name and Target Field Name.
  • The searching process is slightly different when searching a 2-part key. For example, searching for a Customer Address Code. You might provide it with AARONFIT0001 (the Customer Number) and PRIMARY (the Address Code). It searches first for “PRIMARY”. When it locates a table with a string field containing PRIMARY, it then searches all other string fields in that table to see if it can find AARONFIT0001. Only if it finds both values will it record the Table Name, Key Field name, and Target Field name.
  • Repeat the process with additional sample values until the “Total Records” (total number of Table..Field combinations) does not change.
  • Review each Table/Field located by ID Add window to ensure it located a valid table to update

ID: Enter a name for the ID you plan to change, such as ItemNumber or CustomerNumber.

Description: Provide a description for what field will be modified.

ID Type: The update can be based on a single field (such as Item Number) or two fields (such as Customer Address Code or Item Lot Number). idModifier cannot be used to update data that requires three or more fields to locate a unique value.

Key Label: If the update is based on two fields, the Key Label field will be enabled. The Key Label will appear on the idModifier window to prompt for a Key Field value. For example, in a two-part key, such as Customer Address Code, the Key Field Label would be “Customer Number”.

Target Label: Enter a label for the field that will be updated. This would be “Item Number” if updating items, or for a two-part key like Customer Address Code, the Target Label would be “Address Code”.

Key Field Value: If the update is based on two fields, use this field to provide a sample value for the first part of the key. For example, with Customer Address Code, provide a Customer Number such as AARONFIT0001.

Target Value: Provide a sample value for the field that will be updated. If updating Item Numbers, provide an Item Number such as 128 SDRAM. If the update is based on two fields, such as Customer Address Code, provide an address code (such as PRIMARY) that is valid for the Customer Number entered in the Key Field.

Show: Once a table/field is located, it is retained for the ID and either “included” or “excluded”. The Delete Row button on the scrolling window toggles a flag on the record that controls whether the record is included or excluded, but it does not “delete” the record from the ID. This allows idModifier to perform multiple searches on sample data and skip tables that have been excluded based on a prior review. Changing the Show dropdown makes either the Included or Excluded tables display in the scrolling window.

Total Records: Displays the total number of Table-Field records in the scrolling window. When “training” idModifier, provide it with multiple data samples until the Total Records count does not change.

Manually Adding Records: Tables can be manually added by keying in the Table Name and Field/Fields to update.

Reviewing Training

HelpID: 502622118

The “training” process identifies POTENTIAL updates to perform. If given representative samples to search for, it will locate matches in all GP Company Database tables. However, there is a risk of “false positives”—tables that appear to have the correct data but which SHOULD NOT be updated.

Binoculars: In the ID Add window, select a record in the scrolling window by clicking into the row, then click the Binoculars to open idModifier’s Query window. The Query window will show data from the selected table. If Key Field Value and Target Value are provided, the query will restrict the data to show only records containing the sample data.

It is CRITICAL to review each table/field identified by the training process to check if a “valid” record has been identified. For example, if you have both Address Codes and Location Codes called “WAREHOUSE”, the search process will find both records containing the Address Code WAREHOUSE and records containing the Location Code WAREHOUSE. When incorrect Table/Field records are found, select them and click the Delete Row button to move them into the Excluded List.

It is possible that the search process could identify multiple potential updates in a single table. For example, it could find Customer AARONFIT0001 with Address Code WAREHOUSE and Location Code WAREHOUSE all in the Sales Header table (as shown above). In such a case there will be two separate records. The SOP10100 – CUSTNMBR – LOCNCODE record is not valid. Select it and click the Delete Row button to move it to the Excluded List.

Pre/Post Scripts

HelpID: 502622104

A pre/post script can be attached to each update. The pre- or post-script is a SQL Script. In the ID Add scrolling window, click the checkbox below PRE or POST to open the script editor window.

When idModifier updates the selected table, it will run the PRE script before it updates the table, and run the POST script after the table is updated. The SQL script can update ANY table, not just the selected table. Attaching a pre/post script to a specific table affects the TIMING of when the script is executed, not WHAT the script performs. idModifier executes the updates in alphabetical order based on Table Name.

With that in mind, the BM010415 PRE script will run AFTER updates to all tables above it in the scrolling window. It will run before BM010415 is updated, and before all other tables following BM010415.

Before executing a script, idModifier appends a “header” to it as shown below:

For example, if you are updating the Customer Address for AARONFIT0001 from WAREHOUSE to AF1, the header would look like this:

If updating a 1-part key, such as Item Number, the header would look like this:

The pre- and post-scripts can use any of the parameters set in the header: @Key, @TargetPre, and @TargetPost. Using the simple update script shown at the beginning of this section, the script would be executed as shown below:

Using idModifier

HelpID: 502622043

Navigation: Tools >> Utilities >> System >> idModifier

idModifier can change any ID (string) field in the Dynamics GP Company database. If not used with abundant caution, planning and testing, it can severely corrupt the database. Every time the idModifier window opens, you will be asked to acknowledge that you understand and accept this risk. The warning message reads:

idModifier can mass change ID fields in Dynamics GP and 3rd Party Products.

Using idModifier incorrectly can cause severe database corruption!

* Read the manual carefully

* Test comprehensively in a TEST company

* Make a backup before performing an update

You assume ALL RISKS with the use of this utility.

Select the ID to update. See the Adding IDs section for information about how to create new IDs.

For a two-part key (such as Customer Address), provide the Key Field (such as Customer Number), and the Old and New target values (such as the Address Codes WAREHOUSE and AF1).

For a one-part key (such as Item Number), provide the Old and New target values (such as the Item Numbers 128 SDRAM and 128-SDRAM).

Click MODIFY to start the update.

You will be asked to confirm you have a backup. Then asked to confirm you are ready to update.

The update is performed as follows:

  • Check each table/field for the NEW target value. For example, if updating Item Numbers, it checks every table that will be updated to make sure the “new” Item Number does not already exist. If the new target value exists, it will be reported in the Update window:

The information in the Update window can be sent to Excel. Click the GoTo button, then select Send To Excel.

  • If the target NEW value is not found in any of the update tables, the update proceeds with the tables in alphabetical order.