Useful Out-of-the-Box SQL Functions for MSCRM

There are some very useful SQL functions that are built into the MSCRM database that are available to be used in the select queries to write reports, pull data into Excel, or just to check the data in your database. Some of the more notable functions, at least from what I think, are the following:

1. F_START_OF_WEEK (gets the first day of a week, and you can specify which day you want to be the start of your week, Monday, Tuesday, etc)
2. fn_LastXMonth (this is what Advanced Find uses, this is fairly easy to replicate with SQL but it gets even easier if there is a function to be used)
3. fn_FindUserGuid (this gets the logged in user's GUID, this is how Advanced Find figures out "Equals Current User")
4. fn_GetMaxUserPrivilege (tells you whether a user has sufficient rights to a specific privilege,)
5. fn_RptBracket (puts numbers in groups, for instance fn_RptBracket(75, 50) would return 50 - 99 . This translates to placing the number 75 into numbers 1-49, 50-99, 100-149, etc. Basically the first number is the number you want to group and the second is the interval at which you want your groupings. Play with it a bit to see how it works. It could be very useful for reporting)

To find all of the functions open up SQL Management Studio and expand the MSCRM database, then expand "Programmability", expand "Functions", and finally, expand "Scalar-valued Functions" to see the full list. Right click a function and choose "Script Function As", "Create To", "New Query Editor Window" to look at the code behind the function.

Some very cool things to find and use, hopefully this makes some report queries much more simple and other data check queries easier to write.

David Fronk
Dynamic Methods Inc.

Reassociating MSCRM Users with Active Directory Users

Back in the version 1.0 and 1.2 days fixing CRM user associations was a huge pain. Users would get deleted from Active Directory or CRM users would need to be reassociated with a different user in Active Directory. These tasks were either impossible to perform or nearly impossible to recover from. In these instances, MS Support had to get involved to reassociate records owned by that user through the back end with some of their utilities.

When version 3.0 came out finally users had the ability to use Deployment Manager to reassociate CRM users to Active Directory users. This helped a lot.

In version 4.0 the ability to reassociate CRM users with Active Directory users through Deployment Manager has gone away. When I discovered this I was very upset because I support numerous companies and inevitably, between all my clients, I will get a handful of these type of issues a year. So, having that utility was a reasonably big deal to me. After some poking around I found that reassociating users in MSCRM 4.0 is actually even easier than it was in 3.0. Since I was expecting some special command or utility I never even noticed it.

If you open up a user's card in CRM in all other versions the Username field has been locked down as soon as you save the record. In 4.0 this is not the case. All you have to do to reassociate the user record with a different Active Directory record is change the username. The autopopulate of the fields still occurs and everything gets reassociated.

Now you're ready to rock and roll and keep all of your users between MSCRM and Active Directory in sync.

David Fronk
Dynamic Methods Inc.

MSCRM 4.0 Rollup 1 is now available

This rollup fixes over some 80 issues with the server, client and email router (each a separate download and install). We've applied this in-house and had no issues as of yet, just improvement. To download click below:

http://www.microsoft.com/downloads/details.aspx?FamilyID=57c6267b-3b13-49dd-bfed-3cc83633aea7&displaylang=en

Also, please note that not all recent hotfixes made it into this rollup. I am aware of at least 2, a hotfix for the deletion service and one for issues with the Word Mail Merge connector. But each of those hotfixes are over 100MB.

I'm a fan of rollup 1 so far but if you're applying to fix specific issues just make sure that the appropriate hotfix is included so that you do actually fix the issue you are having.

David Fronk
Dynamic Methods Inc.

Disabling all fields on a form...dynamically

There have been a number of times where I have had clients request that when certain fields are set one way or another they would actually like to disable the entire form. Or they want to disable the entire form except for a status field, or something like that. For some clients this was a HUGE pain in the neck because every field would have to be done manually. And whenever a new field was added if the script wasn't updated then that new field would be updateable while everything else would stay locked down.

Well, I found this code posted by Michael Höhne from Stunnware and it was exactly what I've been trying to do.


for (var index in crmForm.all) {
var control = crmForm.all[index];
if (control.req && (control.Disabled != null)) {
control.Disabled = true;
}
}

That will disable them all. Pretty sweet. Then if they want to leave a status field or something enabled just manually enable them afterwards:

crmForm.all.new_status.Disabled = true;

And you're all set.

David Fronk
Dynamic Methods Inc.

Sending Multiple Emails with Attachments

I've received multiple requests on how to add attachments to "canned" emails that you send out. Typically this is requested for the Quick Campaign. While you cannot add an attachment to Quick Campaign emails there is another way to accomplish our goal.

Workflow gives you the ability to send emails and it also allows you to send emails with attachments out of CRM. In workflow you can choose as a step the "Send Email" option and the "Set Properties". You'll see the "Attachment" tab and you can add multiple attachments to the email. Set the workflow to be triggered "On Demand" and you can then send out your emails with their attachments where you like. It is unfortunately only 250 at a time but that's better than none.

David Fronk
Dynamic Methods Inc.

Microsoft CRM Accelerators Released

Just announced Thursday during the CRMUG Workshop: Using the CRM Platform for Building Complex Business Applications - three new Accelerators have been released for users of Microsoft Dynamics CRM.
According to Reuben Krippner, Senior Product Manager - Microsoft Dynamics CRM, the "CRM Accelerators are a range of add-on solutions developed for Microsoft Dynamics CRM 4.0 customers and partners. Each accelerator is available at no cost and will showcase how the Microsoft Dynamics CRM 4.0 platform can be configured and extended to broaden marketing, sales and service capabilities."
Available now are accelerators for:• Notifications• Event Management• Enterprise Search
Five other accelerators are scheduled to be released prior to year-end.
Each accelerator available for download may consist of the following:• Customizations (entities, forms, views)• Workflow definitions• Business Intelligence elements such as custom reports (.RDL's)• Functional code samples (strictly adhering to SDK guidelines)• Documentation for installing, operating, localizing and extending the solution• An automated installerEach accelerator will be fully supported as per any other customization for Microsoft Dynamics CRM that follows SDK guidelines. Additionally, all samples are supplied with full source-code so they can be extended further to meet specific customer requirements. You can download the accelerators and supporting resources now here. Also available are discussion threads and feedback channels.

David Fronk
Dynamic Methods Inc.

CRM Accelerators Update

Events Management is now available for download. For those of you without a PartnerSource log in try the following link:

http://www.codeplex.com/crmaccelerators

I don't have any more dates for you, so we'll all just have to keep our eyes open to see when the rest get posted.

David Fronk
Dynamic Methods Inc.