Changing Unit of Measure Schedules in Dynamics CRM when integrated with Dynamics GP using the Connector

Environment: Dynamics CRM 2011 / Dynamics GP 2010 / Connector for Microsoft Dynamics

Recently a client changed the base unit of measure from Box to Each for one of their unit of measure schedules. The connector, of course, wouldn’t properly integrate the new mapping because it had already been assigned to product lists. The sequence to get it changed is as follows -

GP is the master for the Unit of Measure schedule, so start there.

• In GP, find any products that are using the Unit of Measure schedule you want to change and switch them to another schedule. Note that you’ll be warned that you will have to recreate the price lists and purchasing options for this product.
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Group
• Delete the Unit of Measure schedule in GP
• In CRM, deactivate and then delete the old Unit Group
• In GP, create the new Unit of Measure schedule with the correct Base Unit
• Run the Unit of Measure Schedule to Unit Group map and check the log to verify that the change was brought over successfully.
• In CRM, verify that the new unit group is available.
• In GP, change the products to the correct Unit of Measure schedule
• Recreate the Price Lists and Purchasing options for those products
• Run the Price Level to Price List map in the Connector. After it runs, verify that your products in CRM are switched to the new Unit Groups.

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.

Removing a Company from GP

If a company was deleted by detaching or deleting the DB from SQL, there will be orphaned records in the DYNAMICS database.

In order to remove the company, use the following script -

/* ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS

database referencing databases that no longer exist on the SQL Server.

Requirements:

Company database you wish to have cleaned out of the tables in the DYNAMICS

database must be removed from the SQL server before running this script.

Ensure that all your databases have been restored or they will be erased

from the DYNAMICS database.

*/

set nocount on

/* Remove all references in the company master (SY01500) for databases that

Do not exist on the SQL Server */

delete DYNAMICS..SY01500 where INTERID not in

(select name from master..sysdatabases)

/* Clear out all tables in DYNAMICS database that have a CMPANYID field

that no longer matches any Company ID's in the SY01500 */

USE DYNAMICS

declare @CMPANYID char(150)

declare CMPANYID_Cleanup CURSOR for

select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'

+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'CMPANYID'

and o.name <> 'SY01500' order by o.name

OPEN CMPANYID_Cleanup

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

while (@@FETCH_STATUS <>-1)

begin

exec (@CMPANYID)

FETCH NEXT from CMPANYID_Cleanup into @CMPANYID

end

DEALLOCATE CMPANYID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a companyID field

that no longer matches any Company ID's in the SY01500 */

USE DYNAMICS

declare @companyID char(150)

declare companyID_Cleanup CURSOR for

select 'delete ' + o.name + ' where companyID not in (0,-32767)'

+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'companyID'

and o.name <> 'SY01500'

set nocount on

OPEN companyID_Cleanup

FETCH NEXT from companyID_Cleanup into @companyID

while (@@FETCH_STATUS <>-1)

begin

exec (@companyID)

FETCH NEXT from companyID_Cleanup into @companyID

end

DEALLOCATE companyID_Cleanup

go

/* Clear out all tables in DYNAMICS database that have a db_name field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @db_name char(150)

declare db_name_Cleanup CURSOR for

select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'' and db_name <> ''''

and db_name not in (select INTERID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'db_name'

set nocount on

OPEN db_name_Cleanup

FETCH NEXT from db_name_Cleanup into @db_name

while (@@FETCH_STATUS <>-1)

begin

exec (@db_name)

FETCH NEXT from db_name_Cleanup into @db_name

end

DEALLOCATE db_name_Cleanup

GO

set nocount on

/* Clear out all tables in DYNAMICS database that have a dbname field

that no longer matches any company names (INTERID) in the SY01500 */

USE DYNAMICS

declare @dbname char(150)

declare dbname_Cleanup CURSOR for

select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''

and DBNAME not in (select INTERID from DYNAMICS..SY01500)'

from sysobjects o, syscolumns c

where        o.id = c.id

and o.type = 'U'

and c.name = 'DBNAME'

set nocount on

OPEN dbname_Cleanup

FETCH NEXT from dbname_Cleanup into @dbname

while (@@FETCH_STATUS <>-1)

begin

exec (@dbname)

FETCH NEXT from dbname_Cleanup into @dbname

end

DEALLOCATE dbname_Cleanup

GO

set nocount on

/* Remove all stranded references from the other Business Alerts table that

no longer exist in the SY40500 */

delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)

GO

GP: Retrieve List of Company Names & IDs

Here’s a script to return you a quick list of the company ids (which are also the database name) and the company names for the companies defined in the DYNAMICS database

SELECT INTERID,CMPNYNAM FROM SY01500
ORDER BY CMPNYNAM

GP: Reset the System Password

Working as a GP Consultant, I occasionally run into a situation where a client doesn’t remember their System Password for GP. You know, the one that let’s you add users, change company information, the admin level tasks in GP.

The only supported solution is to contact MBS Support, who will walk you thru a process to gather information to send them.

There’s an easier way. Run the following script in SQL to reset the system password to blank. You can then go back into Setup and set it to whatever you want.

update DYNAMICS.dbo.SY02400
set PASSWORD = 0x00202020202020202020202020202020
WHERE DMYPWDID = 1

GP: Error setting User Access to Company

In Dynamics GP, administrators have the ability to control what companies users can access. This also controls what users see when the click the drop-down company field during login.

The menu instructions for this post are for GP 9.0. Newer versions provide the same functionality, although menu layout may be slightly different.

To change User Access for a user, click Tools / Setup / System / User Access. Clicking a username will display all of the available databases on the right and you can click the checkbox to allow or disallow access to specific companies for that user.

Every once in a while, especially have migrating to a new server or merging companies, you’ll get an error message “The user could not be added to one or more database”

A typical reason for this is that on the SQL side of the installation, the GP user already exists as a SQL user on the database, but GP doesn’t know about it. When you click the checkbox, GP tries to add that user to the database users. Since the object already exists, SQL kicks back an error that GP doesn’t know how to deal with.

To resolve the error, open SQL Management Studio and connect to your GP SQL instance. Then, run the following script, replacing <dbname> with the Database name of the company you’re trying to add the user to and replacing <username> with the user’s SQL login id. Our example is for company “Jim’s Bike Shop” that has a database name of JBS and the user id being jfallon.

USE <dbname>
DROP USER <userid>

So for our example, the code would be

USE JBS
DROP USER jfallon

Go back into GP and you should now be able to add the user to the company.

Troubleshooting SSRS With Dynamics GP

Installing and getting SQL Reporting Services to work with Dynamics GP is a fairly straightforward process, but there can be some tricks involved. The following link provides a good bit of information about configuring and troubleshooting the SQL Reporting Services Deployment Wizard.

SSRS with Dynamics GP FAQ

GP Technical Airlift Notes - Day 1 - GP11 ?

Keynote tidbits

Direction

• Product will be increasingly 'role-based', mapping the product to actual business processes.
• Product will be integrated more tightly with Office product line.
• Product design will be handled through Visual Studio. Standardized customization means increased supportability.

Add and extend:

• Role based => Enhanced user experience
• Portals and worflow => process-centric design
• BI => ad hoc, self service informationg athering
• web service => simplified / standardized accessibility

Direction

(A) Check out "Statement of Direction"

MS will spend $7B in R&D this year

GP looking to increase value to customers who subscribe to Enhancements by releasing Feature Packs between product releases. Next Feature Pack will be a step-in to GP 11, which will be built around Office 14.

16,000 QuickBooks customers last year moved from QuickBooks Pro to QuickBooks Enterprise. They should be GP customers. GP will begin aggressively marketing to QB customers.

New VPC is out.

Feature Pack Features

• Excel report builder - Additional data sources available. Preview columns before export. Add restrictions and calculations. Summarize a Excel Report for multiple companies into a single workbook.
• Forcasting enhancements
• Reporting
  • Personal BI - Report Writer, Excel Reports, Smartlists
  • Team BI - Excel Services, SQL Reporting Services, Sharepoint
  • Org BI - PerformancePoint Server, FRx
  • (A) - Check on PerformancePoint

Scope

(A) Check out Pinnacle Award winner stories to find out what cutting edge solutions are being done.

Demos

Upgrade to GP from QuickBooks. Old vs New

!! - GP 10 Feature pack includes rapid migration tool which allows you to bring in ALL data (including transactions !) in one single export / import routine.

During migration, all master records can be exported to Excel and vetted before importing into GP.

(20 minute upgrade)

Start a new company from scratch

Demo of Rapid Configuration Tool

Using Excel templates, allows for extremely rapid initial setup. End of setup, brings up list of all errors which link directly to cells in original Excel documents

Shipping 13 templates out of box for verticals.

Rapid Config tool hits ~78 windows and over 1300 Fields during setup.

(20 minute setup)

Fun Moments

Pissed off ISV barely controls his fury that his product feature will be built into GP 11 as a native feature - almost blowing his NDA.

MOSS Implementation

Eric Gjerdevig - egjerdevig@summitgroupsoftware.com

Joe Tews - jtews@summitgroupsoftware.com

Business Data Catalog - Map to back-end data that allows that data to be presented in Sharepoint as a list.

Method 1 - Business Data Catalog:

• Imported into SSP in MOSS Central Admin
• After Import, create new site. Add new web part Business Data List / Business Data Related List
• Modify to select source for data for each.
• After data resolves, Edit, Connections, Get data from...

Method 2 - Excel Web Access:

• MOSS Central Admin / Operations / Services on Server / enable Excel Calc Services
• Open SSP page
• Edit Excel Services Settings
• Fill in unattended service account
• Back to SSP / Enter trusted file location
• Upload Excel doc to MOSS library. Open document and publish to excel services (Office key / publish)
• Select what items get published
• Save to publish
• Open page, add Excel Web Access Web Part
• Select workbook created

KPI's

Search

Sources:

• Internal MOSS sites
• External websites
• File Shares
• Line of Business applications
• Exchange public folders

Workflow

Approval process that defines how documents move through an organization.

• Consistent processes
• Automatic notification
• Accessible through MOSS / Outlook
• Reporting

Participant Roles:

• Administrate
• Manage
• Originate
• Approve
• Delegate
• Carbon Copy

Customizing Workflow

Christina Phillips - christinag@theknastergroup.com

Tim Oines - tim.oines@microsoft.com

General Notes

• Requires MOSS
• MDW MOSS Site
• Documents originate in GP (NOT like requisition mgt in Business Portal where documents originate outside of the system)
• Approvals via GP, Outlook or MOSS
• Flexible approval structure
  • Multiple approvers
  • Routing based on document criteria
• Workflow SDK now available on partnersource
  • Integration Guide
  • Web Service Reference
  • 2 Samples (new + extending)
• Creates SQL role DYNWORKFLOWGRP to allow security via MOSS site. However, authentication happens via WSS Timer Service account

Terms

• Workflow Contract - Definition of the workflow. What components are available.
• Workflow Contract Registration - registration of assemblies with the GAC
• Workflow events - events that occur which need to be acted upon
• Dynamics Security Store - stores web services permissions for operations and tasks using auth mgr tool
• Form Controller - responds to gp client events, manages communications with web services, adds message bars, controls and history bar to dynamics window
• Form Factory - .NET code that runs before dexterity windows are opened to determined whether information is affected by a workflow

Out of the Box Workflows (6)

• Batch Approvals
  • GL
  • Payables M
  • Rreceivables Mgt
• Sales Order Processing
  • Credit Limit Override
  • Quote Approval
• Purchase Order Processing
  • Purchase Order Approval

Beyond Out of the Box

• Create a new workflow
  • Payables Check Approval
  • Sales Credit Memo Approval
• Extend existing
  • Add custom fields to standard workflow
  • Integrate 3rd party products

Building a new workflow

1. Make app dictionary changes
2. Install data and config changes
3. Add workflow columns to database
4. Add a web service
5. Create a client workflow assembly
6. Create a server workflow assembly
7. Create a document viewer
8. Update the Dynamics Security Service
9. Update Microsoft SQL Server Security
10. Deploy the Server Workflow Assembly
11. Deploy the Web Service
12. Deploy the Client Workflow Assembly

Business Intelligence

• Jennifer Harwood, Microsoft
• Brian Meier, Microsoft
• Gary Tronson, Microsoft

Excel Report Builder

• Users create or modify data connections and refreshable excel based reports
• Combine data from multiple companies into a single workbook
• Allow publishing to MOSS or other network locations

Setup:

• Install Smartlist Builder
• Tools / Smartlist Builder / Refresh Cache
• Grant Security to tables, views. Tools / Smartlist Builder / Security
  • Update cache every time new views are created and assign security, otherwise they will not be exposed to SLB
  • Tools / Setup / System / Security Tasks / SLB window
• Open Excel Report Builder
• Begin building your report
• If you publish the report, grant security to the report wherever you publish it.
• If you can see the report, but get errors about access - grant access for the data connection in SQL. SQL Mgt Studio / Security / Logins / Make sure the user's Windows account has rights to the databases.

SQL Reporting Services (Jennifer)

Partnersource / Product Releases / Download reports that can be opened in Visual Studio for exploration or modification

Make sure to rename reports if modifying existing reports so that they are not overwritten by service packs or upgrades

Model - a diagram of data that includes tables and views which is used as the foundation of the report. automatically creates aggregates of data (date becomes day, week, month, etc.)

Rest of presentation canceled due to trying to run the demo via Terminal Services over a wifi connection that was anemic.

Integrating BI into Dynamics GP

Demo - create a list in purchasing using Business Data as the source to pull in vendor list and purchase order list. list tracks purchasing disputes. then build a kpi that tracks the number of disputes to show red/yellow/green.