Lessons Learned: CRM 4.0 Adapter for GP 10.0

We are excited about this new integration, so we jumped right in, and installed it on our production systems. Our systems engineer is a genius, and what follows are his comments related to his experience. Consider this an experiential addendum:

1. Must have GP 10 SP4 applied. (There are caveats to sp4 so be aware)

2. Must have at least rollup5 for CRM applied. (Rollup6 was out in Oct 2009 so I used rollup6)

3. Must have GPwebservices for GP10 sp4 and Management tools sp4 installed.

• Had issues with the GPwebSerice not showing up in Dynamics Security console.
• Ran a repair on the GPWebServices and the management tools and it resolved the issue.

4. Must have Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update applied to same server GPwebservices is installed. http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=766a6af7-ec73-40ff-b072-9112bab119c2#filelist

5. Must have domain service accounts created. You can determine how many you need. Recommend at least 3 svc_gpweb, svc_gpcrmadmin, svc_gpcrm.

6. GPWebservice Account - Permissions granted during install.

7. Installation account – The account that installs the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP.

a. Be a domain user and a member of the Local Administrators group on the server where Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP is to be installed.
b. Be assigned at least the dbcreator and securityadmin server roles on the SQL server where the MSDI database is to be stored

8. Integration account – The account that the Microsoft Dynamics CRM Adapter Service runs as. Permissions granted during install of Dynamics GP CRM adapter.

9. Configuration account – The account that runs the Microsoft Dynamics CRM Configuration Utility.

a. This account must be able to modify the membership of the CRM PrivUserGroup Security Group in the domain that the Microsoft Dynamics CRM Servers are joined to.
b. If this account is not in the db_datareader and db_datawriter roles for the Microsoft Dynamics CRM organization databases, you must use a SQL Server user name and password to update the Microsoft Dynamics CRM organization databases.

10. Integration GP account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics GP Web Services.
a. Permissions granted in the Dynamics Security Console

11. Integration CRM account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics CRM Web Services.
a. This account must belong to a Microsoft Dynamics CRM User that has organizational-level read privileges for system user on the CRM Organization that you plan to integrate.

12. CRM Deployment Administrator account – The account that the Microsoft Dynamics CRM Configuration Utility uses to retrieve a list of Microsoft Dynamics CRM organizations and apply customizations to the Microsoft Dynamics CRM organizations.

13. GP Adapter configuration - Had an error: application domain ...... Only way to fix it was uninstall adapter and remove MSDI database. (just removing database may have fixed it but just uninstall did not)

DynamicsGP Version 10 on Windows 7?

Will DynamicsGP Version 10 run on Windows 7?
Why yes it will, thanks for asking.

Actually, all the pain we experienced with Vista has made this a non-event. There are even printer drivers that are labeled for use with Vista that work with Windows 7. Even the 64 bit version.

I’ve loaded GPV10 on 2 Windows 7 machines with success. The first install was on an old workhorse Dell Optiplx 760 with an Intel core duo and 1 GB RAM. This machine was loaded with Windows 7 Professional Edition. The machine was slow to launch GPV10, (as should be expected) but once the app was running, performance was pretty good.

The second machine was a new Optiplex 960 with 8GB RAM running 2 X 160 GB 10K RPM HDD in a RAID 1 array configuration. This machine is running Windows 7 Ultimate Edition 64 bit. Naturally this machine launches into GPV10 a little quicker.

The only issues I ran into was the dreaded 'please verify your ODBC settings and that BCP has been correctly installed. Utilities will exit'. This is an easy fix and is caused by UAC (User Account Control) being turned on by default. Tech Support gave me this:

In Windows 7 the UAC functions a little differently as it is now a slider rather than a distinct on or off feature. Below are directions to locate and turn off this feature.Start Control Panel User Accounts User Accounts Change User Account Control SettingsWhen you open this you will see a slider, slide it to the bottom to turn it off. This should allow you to continue with your service pack installation.

The only other issue I ran into was with ODBC DSN on the Windows 7 Ultimate machine. Utilities wouldn’t recognize the DSN when it was configured on the ‘System DSN’ tab. No problem when configured on the ‘User DSN’. Permissions no doubt.

Net result. Fear not Windows 7. Go forward and dog-food this much improved OS.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

1. SQL doesn't have to be feared, it can be your friend (but should be respected)


2. Quality over Quantity is over rated - strive for both because Excel does have its limits.


3. hang out with a SQL guru, it will save on hair dye

Changing Purchase Accounts on PO Receipts

If you want to change the purchases account on a PO receiving transaction but don’t want to go back to each line item to change the account, you can add additional distribution lines with the Type = OTHER. You would enter a credit to the default PURCH account(s) and then enter a debit to the new/correct account(s). This feature can also be used if additional accounting entries related to the transaction are needed (to record use tax, for example). As long as you use the OTHER distribution type and have the total debits = total credits for the additional entries, there won’t be any distribution error messages.