Friday, October 17, 2008

MS CRM Address Sync Workflow

In Microsoft CRM, you can have many contacts connected to a single account. When you initially create the contact, it takes the address from the account record, but the user has the option to change the address. This is a nice feature because it supports companies that have employees who are not all located in the same building. But what happens when you change the account address? All those contacts end up staying at the old address. You could manually change them, but there is no need because you can automate this process. The Microsoft CRM Team blog has some suggestions to remedy this problem, but it requires building a custom program (which they provide) and installing it on the CRM server. This could be problematic for users of CRM On-line, so I have a solution using the workflow tool.

In the Contact entity, create a couple new attributes: one will be called “Sync with Account Address” as a bit field (yes/no), and the other is wfDateOfAddressSync (Date & Time). Allow me to digress on the name of the second attribute. I use the “wf” in front of attributes that will not be visible on a form, but are used by workflow to control actions. This makes it easy for me to distinguish them from user attributes, and serves as a reminder so that in the future I don’t delete it because it appears to be unused. Place the new bit attribute on the form in a conspicuous location in the address section and publish the changes.

In the Account entity, create a new field attribute wfDateOfAddressChange (Date & Time) and publish the changes.

Now create a workflow called Trigger Address Sync for the Account entity. This workflow should execute when a new Account is created, or when one of the address fields changes. It will update the account record and place the Workflow Execution time in the wfDateOfAddressChange attribute. Make sure the workflow Scope is set to Organization and Publish it.

Next, create a workflow for the Contact entity. Set this one up to “Start When” the a Contact is created, or if either the “Sync with Account Address” attribute or wfDateOfAddressSync attribute changes. Now add a condition to only execute the rest of the workflow if the Sync with Account Address attribute is Yes, then add a timeout condition that will wait for the wfDateOfAddressSync on the Account entity to not be equal to the Contact entities wfDateOfAddressChange attribute. The final step is to update the contacts’ address fields to be the same as the Parent (Account) address fields, and update the wfDateOfAddressSync attribute to take on the value of the Parent (Account) wfDateOfAddressChange attribute. This last item will create a loop by triggering this workflow to start again (by changing the date attribute) and wait for the next Account address change. Make sure the workflow Scope is set to Organization and Publish it.

Remember, this new Contact workflow will not start synchronizing until they have either been manually applied to existing contacts, or someone updates one of the Start When fields (mentioned above) fields.

Now go test it and have fun!

Tuesday, April 22, 2008

Change multiple records in CRM the easy way

The trick to editing more than one record is to use the Edit option under the More Actions button. If you have specific records that you want to change, use the Advanced Find feature of CRM to find the ones you want, then select them on the screen (using the checkbox, or shift-click, or Ctrl-click like you would with other Windows apps) and then click on More Actions and Edit. This will give you a blank form – any field you change there will update all the _selected_ records when you hit the save button. Warning: there is no Undo feature.

The reason I underlined the word “selected” is that all your views are limited to a certain number of records, between 25 and 250, depending on your setting under CRM’s Tools/Options/General/Records per page setting. So, for example If you have 1000 records to change, you will have to hit the Next page icon in your view and select the next group of records and do the More Actions/Edit again.

Sunday, April 13, 2008

Install and Delete MS CRM organization from deployment manager

I have been doing a lot of customizations for my clients Microsoft CRM packages version 4 has made it so much easier to manage all those different packages. I have Server 2003 (with Active Directory as my own domain) and the enterprise edition installed and the deployment manager allows me to have any number of organizations installed at one time - compared with version 3 which forced me to use a different VM for each customer which was slow and disk hungry.

For one client however, I needed two copies running at one time (for testing and development purposes) with each one installed as its own organization. The process was very simple. The client created a backup of their CRM4 version from their server and I restored it on my laptop. Then using the deployment manger, you can Import that DB as a new CRM organization. During this process it will ask you where your SSRS is located so be prepared for that. For development of reports, I created a separate virtual folder in SSRS for that client so that I never overlap my customer customizations.

During this process, it will re-assign ownership of all the records because of the way Active Directory works; the GUID's of CRM users in their domain would be different from users in my laptop's domain. For my purposes, I just let the system assign them all to my admin account. This whole reassignment process can take a while to run if you have a large database.

When I was done, I decided that I wanted to delete the extra organization, but there wasn't any evidence of an organization Delete function in deployment manager, just a Disable link. After a bit of poking around, I discovered that you must first Disable the organization, then you will get the options to either re-enable it or delete it. When you choose to delete, it will _not_ delete the database; you can do this on your own.

Wednesday, March 5, 2008

MS CRM Horizontal scroll bar

A client contacted me to complain that one of the users needed to see all the fields in a custom opportunity view (where they had asked for a lot of extra fields to be added). After a couple rounds of emails, the IT manager wrote back and said that he realized the user had a low resolution screen (1024 x 768) and the view did not have a horizontal scroll bar.

Here were some options we came up with (assuming IE 7):

1. Use the Internet Explorer Zoom feature – when you open the CRM opportunity list, hold down the control key and roll the mouse center wheel and it will zoom in/out so that you can see more data, albeit smaller.

2. Make the width of the columns narrower – this will make them all visible, but with the font at that size, the data will be mostly hidden.

3. Get him a bigger screen and use a slightly higher resolution (like 1280 x 1024) – this should make it easy to see and give you visibility of all the fields.

4. He could use an Excel add-in tool from Prosoft Systems, and he would have visibility to all the data in all 4 business areas

5. Change the default Text Size of the browser to be "Larger" so that when the view is rendered in its IFrame, it will be too large for the window width and force IE to render a horizontal scroll bar.

Hope this helps someone! If you have any other suggestions, please share them.

Wednesday, February 27, 2008

CRM 4.0 upgrade error

While I was upgrading a server from MS CRM 3 to 4, I got this error message on the System Requirements part of the installation:

The attachments missing parent object IDs in database.

Since this did not make much sense to me, I did some research and found that I could launch the setup with a command line parameter:

setupserver.exe /L c:\crmlog.txt

and after reading that log, it appeared that many activities were being scanned and it hit an error:

Check OrphanAnnotationValidator...

the key words here are 'orphan' and 'annotation' which suggested to me that there were records in the annotation table that did not connect properly to another table.

It turns out that one of my programmers had manually converted some activities into the system using SSIS and left the ObjectID field blank on some records in the AnnotationBase table, so I deleted those records using this SQL statement:

delete from dbo.AnnotationBase
where isnull(objectid ,'fd6fc64e-5165-dc11-baa3-001a6b7af693')
= 'fd6fc64e-5165-dc11-baa3-001a6b7af693'

this statement will scan the AnnotationBase table, and for each record that has a Null ObjectID field (my invalid records), it will treat that field as though it has the GIUD 'fd6fc64e-5165-dc11-baa3-001a6b7af693'
(a unique identifier) so that it can compare it with the same GUID, therefore only the records with a null ObjectID will be found and deleted.

and then I re-started the upgrade and it got me past the error so I could continue the upgrade.

SQL 2005 SP2 Error during install

I was updating my SQL Server 2005 with SP2 (SQLServer2005SP2-KB921896-x86-ENU.exe) running on Server 2003, and it kept getting an error on the installation of the Database Services. It gave me a log file to look at located at:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log

and it was HUGE! it took forever, but I finally found this message burried in the log:
---------- clip -----------
MSI (s) (98!44) [22:27:08:640]: Product: Microsoft SQL Server 2005 -- Error 28062. SQL Server Setup cannot install files to the compressed or encrypted folder: D:\Program Files\Microsoft SQL Server\. To continue, make sure that your installation directories are not compressed or encrypted, or specify a different directory, and then run SQL Server Setup again.

Error 28062. SQL Server Setup cannot install files to the compressed or encrypted folder: D:\Program Files\Microsoft SQL Server\. To continue, make sure that your installation directories are not compressed or encrypted, or specify a different directory, and then run SQL Server Setup again.
---------- clip -----------

and sure enough, someone had compressed the entire SQL server folder. So I uncompressed it and re-ran the SP2 update and it worked fine.

Hope this helps!