Unit of Measure Schedule Troubleshooting with CRM Connector for GP

When running the CRM Connector for GP, one of the maps that integrates the two systems is a UofM Schedule to Unit Group map that transfers the GP Unit of Measure schedules into CRM.

One of the requirements for the map to run successfully is that the UoM schedules in GP can’t have repeating values in the schedule. Take for example the situation below.

Schedule for Gallon
Unit Name	Quantity	Equivalent
Gallon	1.000	Gallon
Gallon	0.018	Drum
Drum	55	Gallon
Schedule for Drum
Unit Name	Quantity	Equivalent
Drum	1	Drum
Drum	55	Gallon
Gallon	0.018	Drum

A limitation of CRM is that a Unit Group cannot contain the unit name more than once. During the map’s integration, only the first 2 lines of each schedule would be integrated, the connector stops processing with the first duplicate is detected.

Finding those duplicates can be a little tricky though. You’ll be able to see them in the log for the map in the Connector for Dynamics mapping client. However, this isn’t very descriptive. Here’s an example of an error message.

[UofM Schedule to Unit Group] has encountered an error while processing key [GAL]. A Unit named GAL already exists in the specified Unit Group which may or may not have the same Base Unit.  Microsoft Dynamics CRM does not currently permit Units to have the same name when they belong to the same Unit Group.

Once you do find the U of M schedule that’s causing problems, it’s sometimes challenging to find the duplicates. It would also be handy to know if there’s going to be any problem rows before you Activate the map.

I’ve written a script to help you find (and mark) the problem rows before you even start the integration.

Search for Duplicate UofM Schedule Rows

SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)

Return all the rows from IV40202 that are duplicates

SELECT *
FROM IV40202 WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

To make the records easier to find in GP, we can use the Long Description field for the row as an indicate.
NOTE: THIS WILL OVERWRITE THE LONG DESCRIPTION FIELD FOR ANY RECORD THAT’S A DUPLICATE!!

Update Long Description to indicate problem rows

UPDATE IV40202
SET UOFMLONGDESC = 'Duplicate'
WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

Now, if you open the Unit of Measure Schedule in the GP client and pull up one of the problem schedules, you should be able to expand the rows and see the lines that are causing or will cause problems.

Update:

In the above example tables, you’ll encounter errors when you try to delete the duplicate rows. GP doesn’t want you to have the base unit of measure in the schedule more than once, even though it allows you to enter it. In this case, you’d have to remove the extra row from the SQL table. Beware, modifying data directly in SQL is NOT supported by Microsoft.