Developers: Microsoft Dynamics GP 2013 Service Pack 2 moves the Vendor and Customer Combiner & Modifier into core code
Now before you ask.... the answer is No, I have not spent all week playing XBox One (as mentioned on the last post). Yes, we did get one and yes the kids have been using it, but the reason I have not posted on the blog is just that I have been very busy with support cases.
Talking of support cases........
I had a case today where a developer was having issues when using the Customer Combiner. This functionality was part of PSTL (Professional Services Tools Library) and as of Microsoft Dynamics GP 2013 Service Pack 2 is now part of core code.
Note: During this article I will be referring to the Customer ID, this is the 'Customer Number' (CUSTNMBR) field. The same information is applicable to the 'Vendor ID' (VENDORID) field.
The Problem
The developer was receiving a "Violation of Primary Key Constraint" error when using the Customer Combiner. Using a SQL Profile Trace, they worked out that the problem was being caused when the Combiner's code issued an update statement on their custom table which caused the error.
UPDATE <TABLE> SET CUSTNMBR = '<NEW>' WHERE CUSTNMBR = '<OLD>'
This update statement will work perfectly if you want to move a transaction table record from the old Customer ID to the new combined Customer ID. However, it will fail when applied to a master table record as it will create a duplicate record. This duplicate issue can occur with any table that has the Customer ID field as part of a primary key or another key that does not allow duplicates.
The Solution
So you ask, "How can I avoid this issue with my custom tables in my custom code?". Well, if the Customer ID is used in a custom table which is a transactional table, there is no problem. If the custom table is a master table, then you will need to add some code into your customisation to handle the situation.
Note: The information below is based on how the code incorporated into the DYNAMICS.DIC for GP 2013 SP2 works, the details for the PSTL version of the code is different.
To understand how to add our extra custom code we need to understand how the original code works.
Customer Combiner and Modifier window
From the Customer Combiner and Modifier window, when you click Process, depending on the mode selected (Combiner or Modifier) it will execute a Dexterity global procedure which will in turn call a SQL Stored Procedure. The SQL Stored Procedure will then disable some triggers, call a second stored procedure and re-enable the triggers. The second stored procedure is where the actual work is done. This stored procedure will update all the tables in the core products (from Microsoft) as required and then run a script which will run an additional update statement against all tables in the database which have the column CUSTNMBR in them (except the core products tables already updated).
Now here is the cool bit; just before the stored procedure makes changes and just after it has made changes it calls Pre and Post scripts which can be used by developers to add additional code. This is similar to the Pre and Post scripts when working with eConnect.
The table below shows the scripts involved:
| Customer Combiner | Customer Modifier | Vendor Combiner | Vendor Modifier | |
|---|---|---|---|---|
| Dexterity Procedure | rmCustomerCombiner | rmCustomerModifier | pmVendorCombiner | pmVendorModifier |
| 1st Stored Procedure | rmCustomerCombiner | rmCustomerModifier | pmVendorCombiner | pmVendorModifier |
| 2nd Stored Procedure | rmCustomerCombinerMaster | N/A | pmChangeVendorMaster | N/A |
| Pre Stored Procedure | rmCustomerCombinerPre | rmCustomerModifierPre | pmVendorCombinerPre | pmVendorModifierPre |
| Post Stored Procedure | rmCustomerCombinerPost | rmCustomerModifierPost | pmVendorCombinerPost | pmVendorModifierPost |
Note: The Modifier scripts don't use a second stored procedure, all the code is in the first stored procedure. Use the CoreParams_SP2.txt file in the SDK (Software Development Kit) or a SP2 source dictionary to obtain the parameter list for the Dexterity global procedures.
There are two solutions to this problem:
- Either use a Dexterity Trigger to run before the Dexterity Procedures listed above to update your custom tables, so that when the SQL code runs against your tables there will be no records to update.
- Or, if you cannot use Dexterity triggers, you can update the Pre Stored Procedure to make the changes for your tables.
If you need to perform any actions after the updates have occurred, you can use an after Dexterity Trigger or the Post Stored Procedure.
The Recommendation
Now that the Combiner and Modifier functionality is part of the core code, it means that ALL Dynamics GP sites will have this feature and so it will be up to developers to ensure that their customisations are compatible. In the past not every site used the PSTL features, but that will change now that the code has become part of the core functionality.
If you can use Dexterity Triggers, this will be simpler as many Dexterity developers can trigger on the same scripts without clashing with each other. Changing the SQL Pre and Post Stored Procedures will need to be a manual process as it might have already been modified already by another developer or system administrator.
Start updating your apps.
Hope you found this useful.
David