Extending the Microsoft Outlook Client for Microsoft Dynamics CRM 2011 (part 4)

The client installer
In the previous blogs I have explained the Outlook Add-on code, the javascript webresource and the related dialog this webresource uses in order to process a tracked e-mail in Microsoft Dynamics CRM.

There are several ways to create an installable version of the Outlook add-on.
You can read al about in on the following MSDN location:
http://msdn.microsoft.com/en-us/library/ff937654.aspx

A simple, reliable installable version of the client can be done according the following steps:

Step 1: Compile the release version of the add-on.
Make sure you publish the release version by compiling the release version


Step 2: Run the Vsto Publish feature
By right clicking the project, choose publish


Step 3: Choose a proper location


Step 4: Provide an installation path
U can use a shared corporate location or website as well. For this purpose I will choose an installable version for a CR-ROM or DVD-ROM.



Press finish and you can start distributing your add-on!

Some ideas
Because there are many different ways customers handle e-mail, this solution provides Outlook users feedback about their e-mail more sufficient than the current Microsoft Outlook Client for Microsoft Dynamics CRM 2011 does. This add-on can be used for:

• Fulfillment of an incomplete tracked e-mail
• Follow-up e-mails efficiently with less clicks
• Provide related information (such as unpaid invoices, open service calls, important notes) instantly.
• Redirect  e-mails to the correct person in a single click
• ..

Good luck and enjoy this add-on!

Greetz,
Boudewijn

Extending the Microsoft Outlook Client for Microsoft Dynamics CRM 2011 (part 3)

Creating the Outlook Add-on

In my previous posts I have explained the general purpose of this feature and mentioned a webresource that will be included in Outlook.
In this example I will use Visual Studio 2010 and will create an Outlook 2010 Addon.
While I mention this, I must correct myself and choose an Outlook 2010 Add-in.

Usercontrol with webbrowser control
Once created we can start adding a usercontrol and give the related class a name like CRM Email Control.cs

Now we can add a webbrowser control on this usercontrol and give it a name like 'webBrowserCrm'
The url we can leave blank as it is.

 

 

  

Once we have added this user control, we can add a property Url to forward it to the webbrowser control like below:

public partial class CRM_Email_Control : UserControl
{
  public CRM_Email_Control()
  {

   InitializeComponent();

  }
  
  internal Uri Url
  {    set { webBrowserCrm.Url = value; }

  }

  private void webBrowserCrm_Navigated(object sender, WebBrowserNavigatedEventArgs e)
  {    webBrowserCrm.Visible = true;
  }   private void webBrowserCrm_Navigating(object sender, WebBrowserNavigatingEventArgs e)
  {    webBrowserCrm.Visible = false;
  } } 

As you notice, I will hide the browser when loading and show the browser once finished loading.

ThisAddin
Now we can edit the code in ThisAddin.cs.

At first we need to add the namespaces by adding:

using Microsoft.Office.Tools;
using Microsoft.Office.Interop.Outlook;
using Microsoft.Win32;

We can replace the class like the code below. The commands explain everything.

public partial class ThisAddIn
{
  private const string webresourceName = "dh_Outlookemail.html";
  private CustomTaskPane dynamicHandsEmailPane;
  private Uri currentUrl;
  private string InstalledCrmUrl;
  private List<Explorer> explorers;

  private void ThisAddIn_Startup(object sender, System.EventArgs e)
  {
      //get the crm url
    InstalledCrmUrl = Registry.GetValue(@"HKEY_CURRENT_USER\SOFTWARE\Microsoft\MSCRMClient", "WebAppUrl", null) as string;

    //when crm for outlook is not properly installed, we don't want to do anyting
    if (string.IsNullOrEmpty(InstalledCrmUrl))
      return;

    //add the outlook e-mail pane
    dynamicHandsEmailPane = this.CustomTaskPanes.Add(new CRM_Email_Control(), "DynamicHands E-mail");
    dynamicHandsEmailPane.Visible = false;
    dynamicHandsEmailPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionTop;
    dynamicHandsEmailPane.Height = 100; 
    //handle the selectionchanged of the current explorer
    Explorer exp = Application.ActiveExplorer();
    exp.SelectionChange += new ExplorerEvents_10_SelectionChangeEventHandler(exp_SelectionChange);

    //keep track of the explorer
    explorers = new List<Explorer>();
    explorers.Add(exp);

}

private void exp_SelectionChange()
{
  //get the selection
  Selection selection = Application.ActiveExplorer().Selection;

   //handle the first selected item
  foreach (Object item in selection)
  {
    if (item is MailItem)
    {
      //generate an url based upon the tracked e-mail (or untracked e-mail)
      Uri url = GetUrl(item as MailItem);

      //prevent refresh and flickering
      if (currentUrl != url)
      {
        //set url property and visibility
        dynamicHandsEmailPane.Visible = (url != null);

        //update the webcontrol url
        ((CRM_Email_Control)dynamicHandsEmailPane.Control).Url = url;

        //keep track of the current url
        currentUrl = url;
     }
   }
  break;
  }
}


internal Uri GetUrl(MailItem item)
{
  if (item == null)
    return null;

  //check if mailitem contains a crmid property
  string crmId = (item.ItemProperties["crmid"] == null ? "" : item.ItemProperties["crmid"].Value);

  //create an Uri based upon this id
  if (!string.IsNullOrEmpty(crmId))
  {
     string crmUrl =
     string.Format( "{0}WebResources/{1}{2}",
         InstalledCrmUrl,
         webresourceName,
         Uri.EscapeDataString(string.Format("?emailid={0}", crmId)));   

     return new Uri(crmUrl);
  }

  return null;
}



private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
  if (dynamicHandsEmailPane != null)
  {
    Globals.ThisAddIn.CustomTaskPanes.Remove(dynamicHandsEmailPane);
  }
  dynamicHandsEmailPane = null;
}
 

Installer
The installer will be available in the next post.

The sourcecode and related Dynamics CRM solution is available for download here:
http://sdrv.ms/13C0bSK

Happy coding!

Geetz.
Boudewijn

Extending the Microsoft Outlook Client for Microsoft Dynamics CRM 2011 (part 2)

The Microsoft Dynamics CRM Outlook client
By default the Microsoft Dynamics CRM outlook client provides users the ability to track e-mails by using the "Track" button. All users are familiar with this functionality and use this whenever an e-mail is required to be tracked by your organization.

What actually is missing here is a basic feature to register more information related to this e-mail and customer. For example, a user tracks an e-mail message and wants to fill in related information and create a follow-up task as well. It can be done when a user clicks the "View in CRM" button but it may take many clicks and open windows to actually take care of this e-mail.

Extending the client
There are many ways to extend the Outlook client but for this example I will allow the user to simply click on a follow-up button that triggers a dialog process from within a managed html webresource

Creating a dialog
Because this blog is not about creating dialog processes I will ask you to go to the following video and make sure you create a dialog process based upon the e-mail entity and you know the corresponding guid. You can get the guid by running the dialog from within Microsoft CRM and press ctrl+n. You will probably see a long url in Internet Explorer. The id is marked in bold.
https://myorg.dynamics.com/cs/dialog/rundialog.aspx/cs/dialog/rundialog.aspx?DialogId=%7bE49AC7F1-BFF5-44DF-BE2C-2E05A7946B92%7d&amp;EntityName=email&amp;ObjectId={ECDB724E-EDA9-E211-B63A-D4856451BCD1}

Creating a html webresource
As for the html webresource you can use the following code. Make sure you enter the dialog guid correctly and notice this webresouce requires a parameter "emailid" in order to run.
Before actually start embedding this webresource in Outlook, make sure it will run in your browser. Let's name this webresource dh_Outlookemail.html and test this webresource by replacing the bold guid by a correct e-mail guid and entering the following url:

https://myorg.dynamics.com/WebResources/dh_Outlookemail.html%3Femailid%3D%7b32522B09-D3A9-E211-B554-3C4A92DBC855%7d

 

<html><head><meta charset="utf-8">
<script language="javascript"> 

var emailId = null;
var dialogId = 'E49AC7F1-BFF5-44DF-BE2C-2E05A7946B92';

getParams = functionundefined){
   var href = decodeURIComponentundefined location.href );
    var vars = [], hash;
    var hashes = href.sliceundefinedhref.indexOfundefined'?') + 1).splitundefined'&');
    for undefinedvar i = 0; i < hashes.length; i++) {
        hash = hashes[i].splitundefined'=');
        vars.pushundefinedhash[0]);
        vars[hash[0]] = hash[1];
    }
    return vars;
}

ShowRelatedData = functionundefined) {
  var parameters = getParamsundefined);
  ifundefined parameters['emailid'] != null ) 
  {
      document.getElementByIdundefined 'btnStartFollupupDialog').style.display='block';
      emailId = parameters['emailid']; 
  }
}

showFollowupDialog = functionundefined) {
    var url = location.href.substringundefined0,location.href.length - location.pathname.length) + '/cs/dialog/rundialog.aspx';
    url += "/cs/dialog/rundialog.aspx?DialogId=%7b" + dialogId + "%7d&EntityName=email&ObjectId=" + emailId ;
    window.openundefinedurl, "", "status=no,scrollbars=no,toolbars=no,menubar=no,location=no");
}

</script>
</head>
<body bgcolor="#dddddd" onload="ShowRelatedDataundefined);">
  <button id="btnStartFollupupDialog" onclick="showFollowupDialogundefined);">Run follup up dialog</button><br>
</body></html>

What's next?
Part 3 (the actual Microsoft Outlook Client for Dynamics CRM 2011 Extension) and sourcecode will be available soon.

Greetz.
Boudewijn,

Extending the Microsoft Outlook Client for Microsoft Dynamics CRM 2011 (part 1)

How do you do?
It has been a while since I've logged in, nevertheless this blog should be worthwhile whenever the Microsoft Dynamics CRM Outlook Client does not completely fulfill your client's needs.

What is this about?
Well as far as the Microsoft Outlook Client exists, users can track their e-mail messages and tasks into CRM. The only thing some users are missing is a way to view more related information or functionality to actually follow up this e-mail or task with a simple click from within the Outlook Client.

Compatibility. What about it?
Well.. as long as users will adopt the Crm Outlook Client for Microsoft Dynamics CRM 2011 in Microsoft Outlook 2010, this add-on will be suitable. However there are ways to extent previous versions of this Outlook client as well.
Because this add-on uses a default webresource in Microsoft Dynamics CRM this can be used for on-premise or online. It does not matter at all.

How does this work?
Well, basically it is just an idea. The only reference that will be used in this blog is Walkthrough: Creating Your First Application-Level Add-in for Outlook. Furthermore, knowledge of creating a web resource in Microsoft Dynamics CRM will be very useful and required as well.

More information about the how to in the next blog.

Greetz, Boudewijn.

Microsoft dynamics CRM: Change collation

In order to change the collation of the Microsoft Dynamics CRM 4 database, you should use this query:

you can run this query in SQL (it is unsupported and you should test it at a dummy organisation, but it worked fine for me)
Just stop the async service and SQL agent service before running this script.


--change collation
use test_MSCRM
declare @target_collation sysname
drop function [dbo].[fn_GetFormatStrings]
exec [p_SetDbCollation] 'test_MSCRM', 'Latin1_General_CI_AI'
go
create function [dbo].[fn_GetFormatStrings]()
returns @FormatStrings table (
DateFormat nvarchar(255),
TimeFormat nvarchar(255),
NumberLanguageCode nvarchar(10),
CalendarType nvarchar(50),
NumberFormat_0_Precision nvarchar(255),
NumberFormat_1_Precision nvarchar(255),
NumberFormat_2_Precision nvarchar(255),
NumberFormat_3_Precision nvarchar(255),
NumberFormat_4_Precision nvarchar(255),
NumberFormat_5_Precision nvarchar(255),
CurrencyFormat_0_Precision nvarchar(255),
CurrencyFormat_1_Precision nvarchar(255),
CurrencyFormat_2_Precision nvarchar(255),
CurrencyFormat_3_Precision nvarchar(255),
CurrencyFormat_4_Precision nvarchar(255),
CurrencyFormat_5_Precision nvarchar(255)
)
as
begin
declare @CurrencySymbol nvarchar(10)
declare @CurrencyFormatCode int
declare @NumberLocale int
declare @CalendarType nvarchar(50)
declare @NumberGroupFormat nvarchar(50)
declare @NegativeFormatCode int
declare @NegativeCurrencyFormatCode int
declare @DateFormatString nvarchar(255)
declare @TimeFormatString nvarchar(255)
declare @NumberLanguageCode nvarchar(10)

declare @s1 nvarchar(255)
declare @s2 nvarchar(255)
declare @s3 nvarchar(255)
declare @s4 nvarchar(255)
declare @s5 nvarchar(255)
declare @s6 nvarchar(255)
declare @s7 nvarchar(255)
declare @s8 nvarchar(255)
declare @s9 nvarchar(255)
declare @s10 nvarchar(255)
declare @s11 nvarchar(255)
declare @s12 nvarchar(255)
select top 1
@CurrencySymbol =
case o.currencydisplayoption
when 0 then t.currencysymbol
when 1 then t.isocurrencycode
else t.currencysymbol
end
from
FilteredOrganization AS o inner join
FilteredTransactionCurrency AS t on o.basecurrencyid = t.transactioncurrencyid
select top 1
@CurrencyFormatCode = currencyformatcode,
@NumberLocale = localeid,
@CalendarType =
case calendartype
when 1 then 'Japanese'
when 2 then 'Korea'
when 3 then 'Taiwan'
when 4 then 'Gregorian US English'
when 5 then 'Gregorian Arabic'
when 6 then 'Gregorian Middle East French'
when 7 then 'Gregorian Transliterated English'
when 8 then 'Gregorian Transliterated French'
else 'Gregorian'
end,
@NumberGroupFormat = numbergroupformat,
@NegativeFormatCode = negativeformatcode,
@NegativeCurrencyFormatCode = negativecurrencyformatcode,
@DateFormatString = replace(dateformatstring, '/', '\' + dateseparator),
@TimeFormatString = replace(timeformatstring, ':', '\' + timeseparator)
from
FilteredUserSettings
where
systemuserid = dbo.fn_FindUserGuid()
set @NumberLanguageCode =
case @NumberLocale
when 1025 then 'ar-SA'
when 1026 then 'bg-BG'
when 1027 then 'ca-ES'
when 1028 then 'zh-TW'
when 1029 then 'cs-CZ'
when 1030 then 'da-DK'
when 1031 then 'de-DE'
when 1032 then 'el-GR'
when 1033 then 'en-US'
when 1035 then 'fi-FI'
when 1036 then 'fr-FR'
when 1037 then 'he-IL'
when 1038 then 'hu-HU'
when 1039 then 'is-IS'
when 1040 then 'it-IT'
when 1041 then 'ja-JP'
when 1042 then 'ko-KR'
when 1043 then 'nl-NL'
when 1044 then 'nb-NO'
when 1045 then 'pl-PL'
when 1046 then 'pt-BR'
when 1047 then 'rm-CH'
when 1048 then 'ro-RO'
when 1049 then 'ru-RU'
when 1050 then 'hr-HR'
when 1051 then 'sk-SK'
when 1052 then 'sq-AL'
when 1053 then 'sv-SE'
when 1054 then 'th-TH'
when 1055 then 'tr-TR'
when 1056 then 'ur-PK'
when 1057 then 'id-ID'
when 1058 then 'uk-UA'
when 1059 then 'be-BY'
when 1060 then 'sl-SI'
when 1061 then 'et-EE'
when 1062 then 'lv-LV'
when 1063 then 'lt-LT'
when 1065 then 'fa-IR'
when 1066 then 'vi-VN'
when 1067 then 'hy-AM'
when 1068 then 'az-Latn-AZ'
when 1069 then 'eu-ES'
when 1071 then 'mk-MK'
when 1074 then 'tn-ZA'
when 1076 then 'xh-ZA'
when 1077 then 'zu-ZA'
when 1078 then 'af-ZA'
when 1079 then 'ka-GE'
when 1080 then 'fo-FO'
when 1081 then 'hi-IN'
when 1082 then 'mt-MT'
when 1083 then 'se-NO'
when 1086 then 'ms-MY'
when 1087 then 'kk-KZ'
when 1088 then 'ky-KG'
when 1089 then 'sw-KE'
when 1091 then 'uz-Latn-UZ'
when 1092 then 'tt-RU'
when 1094 then 'pa-IN'
when 1095 then 'gu-IN'
when 1097 then 'ta-IN'
when 1098 then 'te-IN'
when 1099 then 'kn-IN'
when 1102 then 'mr-IN'
when 1103 then 'sa-IN'
when 1104 then 'mn-MN'
when 1106 then 'cy-GB'
when 1110 then 'gl-ES'
when 1111 then 'kok-IN'
when 1114 then 'syr-SY'
when 1122 then 'fy-NL'
when 1124 then 'fil-PH'
when 1125 then 'dv-MV'
when 1131 then 'quz-BO'
when 1132 then 'ns-ZA'
when 1134 then 'lb-LU'
when 1146 then 'arn-CL'
when 1148 then 'moh-CA'
when 1153 then 'mi-NZ'
when 2049 then 'ar-IQ'
when 2052 then 'zh-CN'
when 2055 then 'de-CH'
when 2057 then 'en-GB'
when 2058 then 'es-MX'
when 2060 then 'fr-BE'
when 2064 then 'it-CH'
when 2067 then 'nl-BE'
when 2068 then 'nn-NO'
when 2070 then 'pt-PT'
when 2074 then 'sr-Latn-CS'
when 2077 then 'sv-FI'
when 2092 then 'az-Cyrl-AZ'
when 2107 then 'se-SE'
when 2108 then 'ga-IE'
when 2110 then 'ms-BN'
when 2115 then 'uz-Cyrl-UZ'
when 2141 then 'iu-Latn-CA'
when 2155 then 'quz-EC'
when 3073 then 'ar-EG'
when 3076 then 'zh-HK'
when 3079 then 'de-AT'
when 3081 then 'en-AU'
when 3082 then 'es-ES'
when 3084 then 'fr-CA'
when 3098 then 'sr-Cyrl-CS'
when 3131 then 'se-FI'
when 3179 then 'quz-PE'
when 4097 then 'ar-LY'
when 4100 then 'zh-SG'
when 4103 then 'de-LU'
when 4105 then 'en-CA'
when 4106 then 'es-GT'
when 4108 then 'fr-CH'
when 4122 then 'hr-BA'
when 4155 then 'smj-NO'
when 5121 then 'ar-DZ'
when 5124 then 'zh-MO'
when 5127 then 'de-LI'
when 5129 then 'en-NZ'
when 5130 then 'es-CR'
when 5132 then 'fr-LU'
when 5146 then 'bs-Latn-BA'
when 5179 then 'smj-SE'
when 6145 then 'ar-MA'
when 6153 then 'en-IE'
when 6154 then 'es-PA'
when 6156 then 'fr-MC'
when 6170 then 'sr-Latn-BA'
when 6203 then 'sma-NO'
when 7169 then 'ar-TN'
when 7177 then 'en-ZA'
when 7178 then 'es-DO'
when 7194 then 'sr-Cyrl-BA'
when 7227 then 'sma-SE'
when 8193 then 'ar-OM'
when 8201 then 'en-JM'
when 8202 then 'es-VE'
when 8218 then 'bs-Cyrl-BA'
when 8251 then 'sms-FI'
when 9217 then 'ar-YE'
when 9225 then 'en-029'
when 9226 then 'es-CO'
when 9275 then 'smn-FI'
when 10241 then 'ar-SY'
when 10249 then 'en-BZ'
when 10250 then 'es-PE'
when 11265 then 'ar-JO'
when 11273 then 'en-TT'
when 11274 then 'es-AR'
when 12289 then 'ar-LB'
when 12297 then 'en-ZW'
when 12298 then 'es-EC'
when 13313 then 'ar-KW'
when 13321 then 'en-PH'
when 13322 then 'es-CL'
when 14337 then 'ar-AE'
when 14346 then 'es-UY'
when 15361 then 'ar-BH'
when 15370 then 'es-PY'
when 16385 then 'ar-QA'
when 16394 then 'es-BO'
when 17418 then 'es-SV'
when 18442 then 'es-HN'
when 19466 then 'es-NI'
when 20490 then 'es-PR'
else 'en-US'
end
select @s1 = dbo.fn_GetNumberFormatString(0, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
select @s2 = dbo.fn_GetNumberFormatString(1, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
select @s3 = dbo.fn_GetNumberFormatString(2, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
select @s4 = dbo.fn_GetNumberFormatString(3, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
select @s5 = dbo.fn_GetNumberFormatString(4, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
select @s6 = dbo.fn_GetNumberFormatString(5, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)

select @s7 = dbo.fn_GetNumberFormatString(0, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
select @s8 = dbo.fn_GetNumberFormatString(1, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
select @s9 = dbo.fn_GetNumberFormatString(2, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
select @s10 = dbo.fn_GetNumberFormatString(3, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
select @s11 = dbo.fn_GetNumberFormatString(4, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
select @s12 = dbo.fn_GetNumberFormatString(5, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
insert
@FormatStrings
values(
@DateFormatString,
@TimeFormatString,
@NumberLanguageCode,
@CalendarType,
@s1,
@s2,
@s3,
@s4,
@s5,
@s6,
@s7,
@s8,
@s9,
@s10,
@s11,
@s12
)
return
end
go
declare @target_collation sysname
set @target_collation = 'Latin1_General_CI_AI'

-- a table of 'constraint drop' commands
create table #keydrops
(
id int identity primary key, -- orders the rows
sql nvarchar(4000) -- contains the command text
)

-- a table of 'constraint add' commands
-- (must be calculated before the constraints are dropped)
create table #keymakes
(
id int identity primary key, -- orders the rows
sql nvarchar(4000), -- contains the command text
ord int -- groups the commands
)
-- a table of 'alter table alter column' commands
create table #tablechanges
(
id int identity primary key, -- orders the rows
sql nvarchar(4000) -- contains the command text
)
declare @sql nvarchar(4000)
declare @column nvarchar(131) -- 128+3
declare @i int
-- Step 1 generate drop and create index statements
declare @tablename sysname
declare @indexname sysname
declare @indid int
declare @is_unique_index nvarchar(16)
declare index_cursor cursor for
select distinct sysobjects.name, sysobjects.uid, sysindexes.name, sysindexes.indid,
case
when sysindexes.status & 2 = 2 then 'UNIQUE '
else ''
end
from sysindexes
join sysindexkeys on (sysindexes.id = sysindexkeys.id
and sysindexes.indid = sysindexkeys.indid)
join sysobjects on (sysobjects.id = sysindexes.id)
join syscolumns on (sysindexkeys.colid = syscolumns.colid and sysobjects.id = syscolumns.id)
join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
sysobjects.xtype = 'U'
and INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
and sysindexes.name not in
(select name from sysobjects where xtype = 'PK' or xtype = 'F ' or xtype = 'UQ')
and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
and syscolumns.collation is not null
and syscolumns.collation != @target_collation
declare @tableschemaid int
declare @metadataSchemaId int
set @metadataSchemaId = CONVERT(int, OBJECTPROPERTYEX(OBJECT_ID(N'MetadataSchema.Attribute'),'SchemaId'))
open index_cursor
fetch next from index_cursor into @tablename, @tableschemaid, @indexname, @indid, @is_unique_index
while (@@fetch_status = 0)
begin
if (@tableschemaid = @metadataSchemaId)
begin
set @tablename = N'MetadataSchema.' + @tablename
end
insert into #keydrops (sql) values('DROP INDEX ' + @tablename + '.' + @indexname)

select @sql = N'CREATE ' + @is_unique_index + 'INDEX ' + @indexname + N' ON ' + @tablename + N'('
select @column = index_col(@tablename, @indid, 1)
set @sql = @sql + @column
select @i = 2
select @column = index_col(@tablename, @indid, @i)
while (@column is not null)
begin
set @sql = @sql + ',' + @column
select @i = @i + 1
select @column = index_col(@tablename, @indid, @i)
end
set @sql = @sql + N')'
insert into #keymakes (sql, ord) values(@sql, 1)
fetch next from index_cursor into @tablename, @tableschemaid, @indexname, @indid, @is_unique_index
end
close index_cursor
deallocate index_cursor
-- Step 2 generate pk drop and pk create statement
declare @pkname sysname
declare pk_cursor cursor for
select distinct parent.name, sysobjects.name, sysindexes.indid
from sysobjects
join sysobjects as parent on (parent.id = sysobjects.parent_obj)
join sysindexes on (sysobjects.name = sysindexes.name)
join sysindexkeys on (sysindexes.id = sysindexkeys.id
and sysindexes.indid = sysindexkeys.indid)
join syscolumns on (sysindexkeys.colid = syscolumns.colid and parent.id = syscolumns.id)
join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
sysobjects.xtype = 'PK'
and parent.xtype = 'U'
and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
and syscolumns.collation is not null
and syscolumns.collation != @target_collation
open pk_cursor
fetch next from pk_cursor into @tablename, @pkname, @indid
while (@@fetch_status = 0)
begin
insert into #keydrops (sql) values('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @pkname)
select @sql = N'ALTER TABLE ' + @tablename + N' ADD CONSTRAINT ' + @pkname + ' PRIMARY KEY('
select @column = index_col(@tablename, @indid, 1)
set @sql = @sql + @column
select @i = 2
select @column = index_col(@tablename, @indid, @i)
while (@column is not null)
begin
set @sql = @sql + ',' + @column
select @i = @i + 1
select @column = index_col(@tablename, @indid, @i)
end
set @sql = @sql + N')'
insert into #keymakes (sql, ord) values(@sql, 2)
fetch next from pk_cursor into @tablename, @pkname, @indid
end
close pk_cursor
deallocate pk_cursor
-- Step 3 generate UNIQUE CONSTRAINT drop and UNIQUE CONSTRAINT create statement
declare @ucname sysname
declare uc_cursor cursor for
select distinct parent.name, sysobjects.name, sysindexes.indid
from sysobjects
join sysobjects as parent on (parent.id = sysobjects.parent_obj)
join sysindexes on (sysobjects.name = sysindexes.name)
join sysindexkeys on (sysindexes.id = sysindexkeys.id
and sysindexes.indid = sysindexkeys.indid)
join syscolumns on (sysindexkeys.colid = syscolumns.colid and parent.id = syscolumns.id)
join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
sysobjects.xtype = 'UQ'
and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
and syscolumns.collation is not null
and syscolumns.collation != @target_collation
open uc_cursor
fetch next from uc_cursor into @tablename, @ucname, @indid
while (@@fetch_status = 0)
begin
insert into #keydrops (sql) values('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @ucname)
select @sql = N'ALTER TABLE ' + @tablename + ' ADD CONSTRAINT ' + @ucname + N' UNIQUE ('
select @column = index_col(@tablename, @indid, 1)
set @sql = @sql + @column
select @i = 2
select @column = index_col(@tablename, @indid, @i)
while (@column is not null)
begin
set @sql = @sql + ',' + @column
select @i = @i + 1
select @column = index_col(@tablename, @indid, @i)
end
set @sql = @sql + N')'
insert into #keymakes (sql, ord) values(@sql, 2)
fetch next from uc_cursor into @tablename, @ucname, @indid
end
close uc_cursor
deallocate uc_cursor
-- Step 4: for every column in every table with a mismatched collation
-- issue an alter column command
insert into #tablechanges (sql)
select 'ALTER TABLE ' +
case
when sysobjects.uid = @metadataSchemaId then 'MetadataSchema.' + sysobjects.name
else sysobjects.name
end
+ ' ALTER COLUMN ' + syscolumns.name +
' ' + systypes.name +
case
when systypes.name = 'ntext' then ' COLLATE '
else
case
when systypes.name = 'nvarchar' and syscolumns.length = -1 then '(max) COLLATE '
else '(' + cast(syscolumns.length/2 as nvarchar(50)) + ') COLLATE '
end
end
+ @target_collation +
case
when syscolumns.isnullable = 0 then ' NOT NULL'
else ' NULL'
end
from syscolumns
join sysobjects on (sysobjects.id = syscolumns.id)
join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
syscolumns.collation is not null
and syscolumns.collation != @target_collation
and sysobjects.type = 'U'
and (systypes.name = 'nvarchar' or systypes.name = 'nchar' or systypes.name = 'ntext')
order by sysobjects.name, syscolumns.name
-- STEP 5: execute key drops
declare keydrops_cursor cursor for select sql from #keydrops order by id
open keydrops_cursor
fetch next from keydrops_cursor into @sql
while (@@fetch_status = 0)
begin
print @sql
exec (@sql)
fetch next from keydrops_cursor into @sql
end
close keydrops_cursor
deallocate keydrops_cursor
-- STEP 6: execute table changes
declare tables_cursor cursor for select sql from #tablechanges order by id
open tables_cursor
fetch next from tables_cursor into @sql
while (@@fetch_status = 0)
begin
print @sql
begin try
exec (@sql)
end try
begin catch
print 'error executing: ' + @sql
print ERROR_MESSAGE()
end catch
fetch next from tables_cursor into @sql
end
close tables_cursor
deallocate tables_cursor
-- STEP 7: execute key makes
declare keymakes_cursor cursor for select sql from #keymakes order by ord desc, id asc
open keymakes_cursor
fetch next from keymakes_cursor into @sql
while (@@fetch_status = 0)
begin
print @sql
exec (@sql)
fetch next from keymakes_cursor into @sql
end
close keymakes_cursor
deallocate keymakes_cursor
drop table #keydrops
drop table #keymakes
drop table #tablechanges

Count related items (javascript)

Jim Wang has recently
posted an article to view the totals of activities and hisoty items.


This article was really very usefull to implement for other related entities.
So I hacked into the code and made sure these counts are done asynchronously.

When items are count, the users sees the "loading" icon while waiting for the result.


CountRelatedItems = function(entityName, filterAttribute, filterValue, navObject) {
if (navObject != null) {

var originalImage = null;

//set loading image
if (navObject.childNodes != null && navObject.childNodes[0] != null) {
originalImage = navObject.childNodes[0].src;
navObject.childNodes[0].src = '/_imgs/btn_lookup_resolving.gif';
}

var xml = "" +
"" +
"" + GenerateAuthenticationHeader() +
" " +
" " +
" ";
xml += " " + entityName + "";
xml += " " +
" " +
" statuscode" +
" " +
" " +
" false" +
" " +
" And" +
" " +
" " +
" statuscode" +
" Equal" +
" " +
" 1" +
" " +
" " +
" ";
xml += " " + filterAttribute + "";
xml += " Equal" +
" " +
" " + filterValue + "" +
" " +
" " +
" " +
" " +
" " +
" " +
" " +
"" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", true);
xmlHttpRequest.onreadystatechange = function() {
if (xmlHttpRequest.readyState == 4) {
var buNodes = xmlHttpRequest.responseXML.selectNodes("//BusinessEntity/q1:statuscode"); // CRM 4.0
if (navObject != null) {
navObject.getElementsByTagName('NOBR')[0].innerText =
navObject.getElementsByTagName('NOBR')[0].innerText + " (" + buNodes.length + ")";
//set original image
if (navObject.childNodes != null && navObject.childNodes[0] != null)
navObject.childNodes[0].src = originalImage;
}

}
}
xmlHttpRequest.setRequestHeader("SOAPAction", " http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);
}
}

To call this function, for example to count the number of related contacts to an account, the following syntax can be used:

CountRelatedItems("contact", "parentaccountid", crmForm.ObjectId,
document.getElementById('navContacts'));