Hard-work-days of Freelance Developer (Microsoft Dynamics CRM, Microsoft SQL, .Net)

MVP Renewed - 2

2012-01-06T16:47:00.001+02:00

Third year in a row I have got MVP award.
This year was the most complicated in my life and I'm glad to get this award one more time.

Visual Studio 2010 - The version of clr.dll in the target does not match the one mscordacwks.dll was built for

2011-11-25T23:43:00.006+02:00

I have prepared new virtual machine for one project (WS 2008, SQL 2008, CRM 2011, VS 2010). I had to develop several plugins and I faced with issue - when I tried to debug plugin and attach to w3wp process I have got following error message - The version of clr.dll in the target does not match the one mscordacwks.dll was built for.

I googled and have found possible solution - Install SP1 for VS 2010. After installation and reboot of virtual machine I was able to attach to w3wp and debug plugins without any issue.

CRM 2011 - reports on data auditing

2011-11-07T15:03:00.002+02:00

One of forum visitor asked about building reports based on Audited data. I have never worked with it in CRM 2011 so I decided to recheck how does it store information in DB. Confusing is wrong word for the feelings I've felt... Denormalized data which is impossible to analyse with SQL statements. Anyway following query can help you to build your own reports based on this information:

Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int

Declare @Result Table(AttributeId int, Value VarChar(Max), ObjectTypeCode int, LogDateTime DateTime, RecordId uniqueidentifier, UserId uniqueidentifier, ActionId int)
Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max)

DECLARE DataAuditCursor CURSOR FOR 
Select 
    Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End
    ,a.ChangeData
    ,a.ObjectTypeCode
    ,a.CreatedOn
    ,a.ObjectId
    ,a.UserId
    ,a.[Action]
From Audit a
OPEN DataAuditCursor

FETCH NEXT FROM DataAuditCursor 
INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE CHARINDEX(',',@attributes,0) <> 0
    BEGIN
        SELECT
            @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))),
            @attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))),
            @CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-1))),
            @values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(@values))))
     
        IF LEN(@CurrentAttribute) > 0
            INSERT INTO @Result Values(CAST(@CurrentAttribute as int), @CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)
    END

    INSERT INTO @Result Values((Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as int) End), @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) 

    FETCH NEXT FROM DataAuditCursor 
    INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId
END

CLOSE DataAuditCursor;
DEALLOCATE DataAuditCursor;
 
Select 
    (Select Top 1 Name From MetadataSchema.Entity e Where r.ObjectTypeCode = e.ObjectTypeCode) EntityName
    ,(Select Top 1 a.Name From MetadataSchema.Attribute a 
    Inner Join MetadataSchema.Entity e On a.EntityId = e.EntityId and a.ColumnNumber = r.AttributeId and e.ObjectTypeCode = r.ObjectTypeCode) AttributeName
    ,u.fullname UserName
    ,r.Value OldFieldValue
    ,r.RecordId ModifiedRecordId
From @Result r
    Left Join FilteredSystemUser u On r.UserId = u.systemuserid

CRM 4.0 - detach answered email from queue

2011-08-29T04:31:00.008+03:00

One of my customers requested following functionality - detach email from queue when email is answered.

Solution consists of 2 parts - customization of email (addition of source email field and JavaScipt which will extract id of source email) and Plugin which will handle Send message.

Add custom nvarchar attribute to email as it shown on the screenshot:

Place this field to the form (this field will be hidden later with JavaScript):

Add following script to OnLoad event handler of email form:

if (crmForm.FormType == 1 || 

   (crmForm.FormType == 2 && 

   window.location.search != null &&

   window.location.search.indexOf('_InReplyToId') != -1))

{

    var query = window.location.search;

    if (query != null)

    {

        query = query.substring(1, query.length - 1);

        var queryparts = query.split("&");

        for(var i = 0; i < queryparts.length; i++)

        {

            var params = queryparts[i].split("=");

            if (params[0] == "_InReplyToId")

            {

                crmForm.all.dc_sourceemailid.DataValue = params[1].replace("%7b","").replace("%7d","");

                break;

            }

        }

    }

}



crmForm.all.dc_sourceemailid_c.style.display = "none";

crmForm.all.dc_sourceemailid_d.style.display = "none";

Save and publish email entity. Plugin part:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.Crm.Sdk;

using Microsoft.Crm.SdkTypeProxy;

using Microsoft.Crm.Sdk.Query;

using System.Text.RegularExpressions;

using System.Web.Services.Protocols;

using System.Xml;



namespace Plugins

{

    public class SendHandler : IPlugin

    {

        #region IPlugin Members



        public void Execute(IPluginExecutionContext context)

        {

            if (context.MessageName == MessageName.Send)

            {

                Guid emailId = (Guid)context.InputParameters["EmailId"];

                ICrmService crmservice = context.CreateCrmService(false);



                TargetRetrieveDynamic target = new TargetRetrieveDynamic();

                target.EntityId = emailId;

                target.EntityName = EntityName.email.ToString();



                RetrieveRequest request = new RetrieveRequest();

                request.ColumnSet = new ColumnSet(new string[] { dc_sourceemailid" });

                request.ReturnDynamicEntities = true;

                request.Target = target;



                RetrieveResponse retrieveresponse = null;

                try

                {

                    retrieveresponse = (RetrieveResponse)crmservice.Execute(request);

                }

                catch(SoapException sexc)

                {

                    throw new Exception(sexc.Detail.InnerText);

                }



                DynamicEntity _email = (DynamicEntity)retrieveresponse.BusinessEntity;





                if (_email.Properties.Contains("dc_sourceemailid"))

                {

                    Guid sourceemailid = new Guid((string)_email.Properties["dc_sourceemailid"]);

                    string fetchRequest = string.Format(@"<fetch mapping='logical'>

    <entity name='queueitem'>

        <attribute name='queueid'/>

        <filter type='and'>

            <condition attribute='objectid' operator='eq' value='{0}'/>

        </filter>

    </entity>

</fetch>", sourceemailid);



                    string fetchResponce = string.Empty;



                    try

                    {

                        fetchResponce = crmservice.Fetch(fetchRequest);

                    }

                    catch (SoapException sexc)

                    {

                        throw new Exception(sexc.Detail.InnerText);

                    }



                    XmlDocument doc = new XmlDocument();

                    doc.LoadXml(fetchResponce);



                    XmlNode queueidnode = doc.SelectSingleNode("//resultset/result/queueid");

                    if (queueidnode == null)

                        return;

                    

                    DetachFromQueueEmailRequest detachrequest = new DetachFromQueueEmailRequest();

                    detachrequest.EmailId = sourceemailid;

                    detachrequest.QueueId = new Guid(queueidnode.InnerText);



                    try

                    {

                        crmservice.Execute(detachrequest);

                    }

                    catch (SoapException sexc)

                    {

                        throw new Exception(sexc.Detail.InnerText);

                    }

                }

            }

        }



        #endregion IPlugin Members

    }

}

Build assembly and register plugin's step as it shown at screenshot:

That's it, have fun!

Microsoft SQL - How to attach database having only mdf file

2011-08-28T13:33:00.005+03:00

Quick trick - I had only mdf file of database without log file. Following t-sql script allowed me to attach mdf file to SQL:

sp_attach_single_file_db @dbname= 'NewDatabase', @physname= 'C:\Directory\DataBase.mdf'

After I ran this script I have got several messages but database was restored and worked fine.

CRM 4.0 - supported way to use stored procedures in reports

2011-08-25T19:30:00.000+03:00

It is written in SDK that it is possible to use Stored Procedures in reports and following article will describe how to do it.

The first thing I should warn you about that it is totally unsupported to create own stored procedures in the database of CRM organization. So make it supported you have to create separated database for your stored procedures.

Creation of database for SPs - open SQL Management Studio and follow next steps:

Now DB is created and you can create SP inside it:

Query is the simplest just to show the idea. Now I will design report:

This screenshot shows how to call external stored procedure.

The result of work of current report.

Next part will describe how to make this report context sensitive - as a parameter XML variable should be passed. Stored procedure will look like:

Design of the report:

And last thing before upload of the report to CRM is to give Network Service account rights to work with our SPs database:

And the last thing is to upload this report to CRM and test it:

As you can see prefiltering works fine for this case.

CRM 4.0 - Create new Organization failed with Exception: System.UnauthorizedAccessException: Access is denied

2011-08-25T10:12:00.000+03:00

I tried to create new organization at one test environment and I have got following error:

I opened log and found there following information:

14:11:35| Error| Create new Organization (Name=Test, Id=e9586456-13cf-e011-bfc2-000c29d64722) failed with Exception:
System.UnauthorizedAccessException: Access is denied.

at System.DirectoryServices.Interop.UnsafeNativeMethods.IAds.SetInfo()
at System.DirectoryServices.DirectoryEntry.CommitChanges()
at Microsoft.Crm.Setup.Server.Utility.ADUtility.GrantAccess(DirectoryEntry groupEntry, String accountName)
at Microsoft.Crm.Setup.Server.Utility.NewOrgUtility.OrganizationConfigureGroups(String languageCode, String privilegedUserGroup, String sqlAccessGroup, String userGroup, String reportingGroup, String privilegedReportingGroup, Guid organizationId)
at Microsoft.Crm.Setup.Server.Utility.NewOrgUtility.ConfigureOrganization(String organizationId, String organizationName, String userAccountName, String userFirstName, String userLastName, String userEmail, String languageCode, String privilegedUserGroup, String sqlAccessGroup, String userGroup, String reportingGroup, String privilegedReportingGroup, Boolean grantNetworkServiceAccess, Boolean autoGroupManagement, String importFileLocation, Boolean sqmOption)
at Microsoft.Crm.Tools.Admin.CreateOrganizationInstaller.Create(Guid organizationId, String organizationUniqueName, String organizationFriendlyName, String baseCurrencyCode, String baseCurrencyName, String baseCurrencySymbol, String initialUserDomainName, String initialUserFirstName, String initialUserLastName, String sqlServerName, Uri reportServerUrl, String privilegedUserGroupName, String sqlAccessGroupName, String userGroupName, String reportingGroupName, String privilegedReportingGroupName, String applicationPath, String languageId, Boolean sqmOption, String organizationCollation, MultipleTenancy multipleTenancy)
14:11:35|Warning| Attempting to cancel a long running process: ProgressWizard. This might leave the data in an invalid state and the application might not function correctly.

Based on this information I have found solution for this issue at search engines - http://support.microsoft.com/kb/950417

CRM 4.0 - how to set 'Filter On' filter to value 'All' for Activities/History

2011-08-02T16:38:00.004+03:00

This script is based on amazing article of Michael Höhne and updated for CRM 4.0.

Just insert following code to OnLoad event handler of form like contact and account.

function OverrideClickHandler(loadAreaId, comboname)
{
    var navElement = document.getElementById('nav' + loadAreaId);
    if (navElement != null)
    {
        navElement.onclick = function()
        {
            loadArea('area' + loadAreaId);
            SetView(document.getElementById('area' + loadAreaId + 'Frame'), comboname);
            }
    }
}
 
SetView = function(Iframe, comboname)
{
    if (Iframe != null )
    {
        Iframe.onreadystatechange = function()
        {
            if (Iframe.readyState == 'complete')
            {
                var frame = frames[window.event.srcElement.id];
                var viewCombo = frame.document.getElementById(comboname);

                if (viewCombo.readyState == "complete")
                {
                    SetDefaultView(viewCombo, "All");
                }
                else 
                {
                    viewCombo.onreadystatechange = function() 
                    {
                        if (this.readyState == "complete")
                        {
                            SetDefaultView(this, "All");
                        }
                    }
                }
            }
        }
    }
} 

SetDefaultView = function(viewCombo, viewName)
{
    if (viewCombo.value != viewName) 
    {
        viewCombo.value = viewName;
        viewCombo.FireOnChange();
    }
}

OverrideClickHandler("Activities", "scheduledend");
OverrideClickHandler("ActivityHistory", "actualend");

CRM 4.0 - function which returns current user lookup

2011-07-25T01:49:00.001+03:00

Just for quick access.

GetCurrentUserLookup = function()
{
    var xml = "<?xml version='1.0' encoding='utf-8'?>"+ 
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 
    GenerateAuthenticationHeader()+ 
    "<soap:Body>"+ 
    "<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 
    "<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query'"+
    " xsi:type='q1:QueryExpression'>"+ 
    "<q1:EntityName>systemuser</q1:EntityName>"+ 
    "<q1:ColumnSet xsi:type='q1:ColumnSet'>"+ 
    "<q1:Attributes>"+ 
    "<q1:Attribute>fullname</q1:Attribute>"+ 
    "<q1:Attribute>systemuserid</q1:Attribute>"+ 
    "</q1:Attributes>"+ 
    "</q1:ColumnSet>"+ 
    "<q1:Distinct>false</q1:Distinct>"+ 
    "<q1:Criteria>"+ 
    "<q1:FilterOperator>And</q1:FilterOperator>"+ 
    "<q1:Conditions>"+ 
    "<q1:Condition>"+ 
    "<q1:AttributeName>systemuserid</q1:AttributeName>"+ 
    "<q1:Operator>EqualUserId</q1:Operator>"+ 
    "<q1:Values>"+ 
    "</q1:Values>"+ 
    "</q1:Condition>"+ 
    "</q1:Conditions>"+ 
    "</q1:Criteria>"+ 
    "</query>"+ 
    "</RetrieveMultiple>"+ 
    "</soap:Body>"+ 
    "</soap:Envelope>";

    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xHReq.setRequestHeader("Content-Length", xml.length);
    xHReq.send(xml);

    var results = xHReq.responseXML.getElementsByTagName('BusinessEntity');

    if (results.length == null)
        return null;

    var lookup = new Array();
    lookup[0] = new Object();
    lookup[0].typename = "systemuser";
    lookup[0].id = results[0].selectSingleNode("./q1:systemuserid").nodeTypedValue;
    lookup[0].name = results[0].selectSingleNode("./q1:fullname").nodeTypedValue;

    return lookup;
}

How to allow users to input allowed characters in Microsoft Dynamics CRM 4.0

2011-06-12T15:52:00.003+03:00

I have already posted here how to restrict user to input special characters to text fields. Following script will allow user to input only predefined symbols to text field.

Add following script to OnLoad event handler:

FilterCharacters = function(fieldname)
{
    document.getElementById(fieldname).attachEvent("onkeypress", function()
    {
        var allowed = "qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM 1234567890,.-";
        var pressedkey = String.fromCharCode(event.keyCode);
        if (allowed.indexOf(pressedkey) != -1)
            return true;

        return false;
    });
}


if (crmForm.FormType == 1 || crmForm.FormType == 2)
{
    //call this method for each field to switch on restriction
    FilterCharacters('name');
}

Rollup activities for custom entities in Microsoft Dynamics CRM 4.0

2011-06-11T15:02:00.006+03:00

Everybody who works with Microsoft Dynamics CRM knows that it is possible to see activities for special entities like contact, account or opportunity but not for custom entities. I had task to develop such kind of functionality. I proposed easier solution - report but customer wanted to have all activities in standard grid.

I made some investigations and understood that it is possible to do with handling of retrievemultiple message. I developed this plugin for one custom entity and after I decided to make it more flexible with possibility to configure without additional development. Here is the code of plugin:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Crm.Sdk;
using System.Xml;
using Microsoft.Crm.SdkTypeProxy;
using System.Web;
using Microsoft.Crm.Sdk.Query;
using System.Web.Services.Protocols;

namespace RollupActivitites
{
    public class RetrieveMultipleHandler : IPlugin
    {
        #region Privates

        private XmlDocument _settings = null;

        #endregion Privates

        #region CTOR

        public RetrieveMultipleHandler(string config, string secureconfig)
        {
            _settings = new XmlDocument();
            _settings.LoadXml(config);
        }

        #endregion CTOR

        #region IPlugin Members

        public void Execute(IPluginExecutionContext context)
        {
            if (context.MessageName != MessageName.RetrieveMultiple || context.PrimaryEntityName != EntityName.activitypointer.ToString())
                return;

            if (!(context.CallerOrigin is ApplicationOrigin))
                return;

            HttpContext webcontext = HttpContext.Current;
            if (webcontext == null)
                return;

            string callerentitytype = null;
            string callerentityidstring = null;

            if (webcontext.Request.Params.AllKeys.Contains("oType"))
            {
                callerentitytype = webcontext.Request.Params["oType"];
                callerentityidstring = webcontext.Request.Params["oId"];
            }
            else
            {
                string requeststring = webcontext.Request.UrlReferrer.Query;
                requeststring = requeststring.Substring(1);

                string[] parts = requeststring.Split(new string[] { "=", "&" }, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < parts.Length - 1; i++)
                    if (parts[i].ToLower() == "otype")
                        callerentitytype = parts[i + 1];
                    else if (parts[i].ToLower() == "oid")
                        callerentityidstring = parts[i + 1];

            }

            if (string.IsNullOrEmpty(callerentitytype))
                return;

            Guid callerentityid = new Guid(callerentityidstring.Replace("%7b", "").Replace("%7d", ""));

            XmlNodeList childentities = _settings.SelectNodes(string.Format("//Settings/Setting[ParentEntityType={0}]", callerentitytype));
            if (childentities.Count == 0)
                return;

            ICrmService crmservice = context.CreateCrmService(false);
            List<object> childids = new List<object>();

            foreach (XmlNode child in childentities)
            {
                string childentityname = child.SelectSingleNode("./ChildEntityName").InnerText;
                string lookupfield = child.SelectSingleNode("./Lookup").InnerText;

                QueryByAttribute query = new QueryByAttribute();
                query.Attributes = new string[] { lookupfield };
                query.ColumnSet = new ColumnSet(new string[] { string.Format("{0}id", childentityname) });
                query.EntityName = childentityname;
                query.Values = new object[] { callerentityid };

                RetrieveMultipleRequest request = new RetrieveMultipleRequest();
                request.Query = query;
                request.ReturnDynamicEntities = true;

                RetrieveMultipleResponse response = null;

                try
                {
                    response = crmservice.Execute(request) as RetrieveMultipleResponse;
                }
                catch (SoapException e)
                {
                    throw new Exception(e.Detail.InnerText);
                }

                foreach (DynamicEntity childentity in response.BusinessEntityCollection.BusinessEntities)
                    childids.Add((childentity[string.Format("{0}id", childentityname)] as Key).Value);
            }

            if (childids.Count == 0)
                return;

            childids.Add(callerentityid);

            QueryExpression resultquery = context.InputParameters[ParameterName.Query] as QueryExpression;
            if (resultquery == null)
                return;

            if (resultquery.LinkEntities.Count == 0)
                return;

            LinkEntity link = resultquery.LinkEntities[0] as LinkEntity;
            ConditionExpression condition = link.LinkCriteria.Conditions[0] as ConditionExpression;
            condition.Operator = ConditionOperator.In;
            condition.Values = childids.ToArray();
        }

        #endregion
    }
}

To make it work you also have to configure it in the proper way - you should insert configure xml to unsecure configuration of step registration dialogue:

Here are registration parameters:

<?xml version="1.0" standalone="yes"?>
<Settings>
<Setting>
<ParentEntityType>10004</ParentEntityType>
<ChildEntityName>custom_child</ChildEntityName>
<Lookup>custom_parentid</Lookup>
</Setting>
</Settings>

ParentEntityType is code of entity from which you want see activities of child entities.
ChildEntityName is scheme name of child entity.
Lookup is the relational field between parent and child entities.

Result of work of this plugin:

Activities of child entity.

Activities of parent entity.

This plugin work for History too.

You can download source code and compiled assembly here:

CRM 4.0 - Error at the stage of creation of the task from plugin

2011-06-10T14:52:00.003+03:00

Today I had task to develop plugin which depends on internal logic had to create the task. I used following code inside plugin:

ICrmService crmservice = context.CreateCrmService(false);
task _task = new task();
_task.subject = "Task Subject";

try
{
    crmservice.Create(_task);
}
catch(SoapException e)
{
    throw new Exception(e.Detail.InnerText);
}

When I tested plugin it threw non-descriptive "Generic SQL error". I switched on trace and found following description of an error:

Exception when executing non-query: insert into QueueItemBase(QueueId, OrganizationId, State, Status, ObjectId, ModifiedOn, Priority, CreatedBy, ObjectTypeCode, TimeZoneRuleVersionNumber, EnteredOn, Title, ModifiedBy, QueueItemId, DeletionStateCode, CreatedOn, UTCConversionTimeZoneCode) values ('00000000-0000-0000-0000-000000000000', '7b839df8-96ea-4ee0-85bf-967a5f9060ef', 0, 2, 'a6341e54-5693-e011-aa0e-000c29d64722', '06/10/2011 11:39:51', 1, 'e490fea9-a809-4f08-b79e-392b9355b9f1', 4212, 0, '06/10/2011 11:39:51', 'Subject', 'e490fea9-a809-4f08-b79e-392b9355b9f1', 'a7341e54-5693-e011-aa0e-000c29d64722', 0, '06/10/2011 11:39:51', 0) Exception: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "queue_entries". The conflict occurred in database "orgname_MSCRM", table "dbo.QueueBase", column 'QueueId'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteNonQuery(IDbCommand command, ISqlExecutionContext context)

The issue is when you use code ICrmService crmservice = context.CreateCrmService(false) and you don't fill ownerid field of activity this activity automatically is assigned to 'System' user and 'System' user doesn't has any queues (by default any standard user has 2 personal queues - Assigned and In Progress).

So the way out is to create ICrmService instance using code
ICrmService crmservice = context.CreateCrmService(true);
or fill ownerid field of activities.

CRM 4.0 - Assigning Case to Queue

2011-01-19T14:53:00.003+02:00

I had the task to assign case (incident) to queue. MSDN has good sample. But this sample didn't solved my problem because in my case not in all situations item was assigned to "In Progress" queue of user. Following code solved my problem.

string fetchRequest = string.Format(@"<fetch mapping='logical'>
    <entity name='queueitem'>
        <attribute name='queueid'/>
        <filter type='and'>
            <condition attribute='objectid' operator='eq' value='{0}'/>
        </filter>
    </entity>
</fetch>", Guid.Empty);//Insert identifier of incident here

            string fetchResponce = string.Empty;

            try
            {
                fetchResponce = crmservice.Fetch(fetchRequest);
            }
            catch (SoapException sexc)
            {
                throw new Exception(sexc.Detail.InnerText);
            }

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(fetchResponce);

            XmlNode queueidnode = doc.SelectSingleNode("//resultset/result/queueid");

            TargetQueuedIncident target = new TargetQueuedIncident();
            target.EntityId = Guid.Empty;//Insert identifier of your incident here

            RouteRequest routerequest = new RouteRequest();
            routerequest.EndpointId = Guid.Empty;//Insert Identifier of queue here
            routerequest.RouteType = RouteType.Queue;

            if (queueidnode != null)
                routerequest.SourceQueueId = new Guid(queueidnode.InnerText);
            else
                throw new Exception("Queue was not found");

            routerequest.Target = target;

            try
            {
                crmservice.Execute(routerequest);
            }
            catch (SoapException sexc)
            {
                throw new Exception(sexc.Detail.InnerText);
            }

Offtopic: bestpractices of code development

2011-01-08T18:49:00.002+02:00

MVP Renewed

2011-01-02T02:41:00.002+02:00

Second year a row I have got MVP award. I can promise that I will try to do everything to get this award 3-rd time.

Queues - plugin which shows the count of items inside queue

2010-09-16T01:21:00.004+03:00

I developed the plugin to make it possible to see near the name of queue the total count of items inside it. But it doesn't work for Assigned and In-Progress (personal queues).

The code of the plugin:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System.Xml;

namespace QueueHelper
{
    public class QHelper : IPlugin
    {

        #region IPlugin Members

        public void Execute(IPluginExecutionContext context)
        {
            if (context.MessageName == MessageName.RetrieveMultiple &&
                context.PrimaryEntityName == EntityName.queue.ToString())
            {
                BusinessEntityCollection queues = (BusinessEntityCollection)context.OutputParameters[ParameterName.BusinessEntityCollection];
                ICrmService crmservice = context.CreateCrmService(true);

                for (int i = 0; i < queues.BusinessEntities.Count; i++)
                {
                    DynamicEntity _queue = (DynamicEntity)queues.BusinessEntities[i];
                    if (!_queue.Properties.Contains("name"))
                        return;
                    
                    Guid queueid = ((Key)_queue["queueid"]).Value;

                    string fetchrequest = string.Format(
@"<fetch mapping='logical' aggregate='true'>
    <entity name='queueitem'>
        <attribute name='queueitemid' alias='c' aggregate='count'/>
        <filter type='and'>
            <condition attribute='objecttypecode' operator='ne' value='4406'/>
            <condition attribute='queueid' operator='eq' value='{0}'/>
        </filter>
    </entity>
</fetch>", queueid.ToString());

                    string fetchresult = crmservice.Fetch(fetchrequest);
                    XmlDocument document = new XmlDocument();
                    document.LoadXml(fetchresult);

                    _queue["name"] = string.Format("{0} - total length - {1} items", _queue["name"], document.SelectSingleNode("//resultset/result/c").InnerText);
                    queues.BusinessEntities[i] = _queue;
                }

                context.OutputParameters[ParameterName.BusinessEntityCollection] = queues;
            }
        }

        #endregion members
    }
}

The registration of plugin:

And the result of work:

Here you can download source code and built assembly:

Record Counter for Microsoft Dynamics CRM 4.0 - version 2.1

2010-08-26T17:34:00.005+03:00

I published new version of record counter at the start of the year - http://a33ik.blogspot.com/2010/02/record-counter-for-microsoft-dynamics.html.

Users who used it told me about several errors. Now those error are fixed and you can download latest version here:

Also I changed the possibility to control the appearance of Record Counter line. This can be done in plugin registration tool. Registration option are shown below:

Execute step:

RetrieveMultiple step:

Poll results

2010-08-06T01:52:00.002+03:00

Results of my poll actually didn't surprise me.

Getting Longitude and Latitude using JavaScript for Microsoft Dynamics CRM 4.0 Part 3

2010-07-30T01:15:00.008+03:00

Several days ago I redeveloped geocoding solution. After several days new issues were found. So I decided to search other webservice which will solve this Geocoding solution and stopped at Bing Maps.

First thing you have to do is to get key to work with Bing services. This can be done here - https://www.bingmapsportal.com/. Register your application and copy key.

I've build my solution based on this article. The code:

RefreshCoords = function()
{
    var bingkey = "Paste your Key here";
    var address = '';
    if (crmForm.all.address1_line1.DataValue != null)
        address += crmForm.all.address1_line1.DataValue;

    if (crmForm.all.address1_line2.DataValue != null)
        address += (address == '' ? '' : ' ') + crmForm.all.address1_line2.DataValue;

    if (crmForm.all.address1_city.DataValue != null)
        address += (address == '' ? '' : ' ') + crmForm.all.address1_city.DataValue;

    if (crmForm.all.address1_stateorprovince.DataValue != null)
        address += (address == '' ? '' : ' ') + crmForm.all.address1_stateorprovince.DataValue;

    if (crmForm.all.address1_postalcode.DataValue != null)
        address += (address == '' ? '' : ' ') + crmForm.all.address1_postalcode.DataValue;

    var url = "http://dev.virtualearth.net/REST/v1/Locations?q=" + address + "&o=xml&key=" + bingkey;

    try
    {
        var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
        xHReq.Open("GET", url, false);
        xHReq.Send(null);

        var resultXml =xHReq.responseXML;
        var long = parseFloat(resultXml.selectSingleNode("//Response/ResourceSets/ResourceSet/Resources/Location/Point/Longitude").nodeTypedValue);
        var lat = parseFloat(resultXml.selectSingleNode("//Response/ResourceSets/ResourceSet/Resources/Location/Point/Latitude").nodeTypedValue);

        crmForm.all.address1_latitude.DataValue = lat;
        crmForm.all.address1_longitude.DataValue = long;
        crmForm.all.address1_latitude.ForceSubmit = true;
        crmForm.all.address1_longitude.ForceSubmit = true;
    }
    catch(e){}
}

Getting Longitude and Latitude using JavaScript for Microsoft Dynamics CRM 4.0 Part 2

2010-07-28T09:10:00.005+03:00

In my previous post I described how to make possible getting Longitude and Latitude. But chosen webservice was not able to resolve some of addresses. In this post I will describe how to get coordinates using Google API.

RefreshCoords = function()
{
    var address = '';
    if (crmForm.all.address1_line1.DataValue != null)
        address += crmForm.all.address1_line1.DataValue;

    if (crmForm.all.address1_line2.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_line2.DataValue;

    if (crmForm.all.address1_city.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_city.DataValue;

    if (crmForm.all.address1_stateorprovince.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_stateorprovince.DataValue;

    if (crmForm.all.address1_postalcode.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_postalcode.DataValue;

    try
    {
        var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
        xHReq.Open("GET", "http://maps.google.com/maps/api/geocode/xml?sensor=false&address=" + address, false);
        xHReq.Send(null);

        var resultXml =xHReq.responseXML;
        var addressComponents = resultXml.selectNodes("//GeocodeResponse/result/geometry/location");
        if (addressComponents != null && addressComponents.length > 0) 
        {
            var long = parseFloat(resultXml.selectSingleNode("//GeocodeResponse/result/geometry/location/lng").nodeTypedValue);
            var lat = parseFloat(resultXml.selectSingleNode("//GeocodeResponse/result/geometry/location/lat").nodeTypedValue);

            crmForm.all.address1_latitude.DataValue = lat;
            crmForm.all.address1_longitude.DataValue = long;
            crmForm.all.address1_latitude.ForceSubmit = true;
            crmForm.all.address1_longitude.ForceSubmit = true;
            }

    }
    catch(e){}
}

Getting Longitude and Latitude using JavaScript for Microsoft Dynamics CRM 4.0

2010-07-24T02:22:00.003+03:00

I've developed this script for my customer - it gets longitude and latitude using webservise http://rpc.geocoder.us/ based on address. The Code:

RefreshCoords = function()
{
    var address = '';
    if (crmForm.all.address1_line1.DataValue != null)
        address = crmForm.all.address1_line1.DataValue;

    if (crmForm.all.address1_line2.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_line2.DataValue;

    if (crmForm.all.address1_city.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_city.DataValue;

    if (crmForm.all.address1_stateorprovince.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_stateorprovince.DataValue;

    if (crmForm.all.address1_postalcode.DataValue != null)
        address += (address == '' ? '' : ', ') + crmForm.all.address1_postalcode.DataValue;

    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    xHReq.Open("GET", "http://rpc.geocoder.us/service/rest?address=" + address, false);
    xHReq.Send(null);

    if (xHReq.statusText == "OK")
    {
        var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
        oXmlDoc.async = false;
        oXmlDoc.loadXML(xHReq.responseText);

        try
        {
            var long = parseFloat(oXmlDoc.selectSingleNode("//rdf:RDF/geo:Point/geo:long").text);
            var lat = parseFloat(oXmlDoc.selectSingleNode("//rdf:RDF/geo:Point/geo:lat").text);

            crmForm.all.address1_latitude.DataValue = lat;
            crmForm.all.address1_longitude.DataValue = long;
        }
        catch(e){}
    }
}

I've added this function to OnLoad event handler and pasted call of this functions to fields Address1 - Line 1, Address1 - Line 2, Address 1 - City, Address 1 - State/Province, Address1 - PostalCode.

But as I know this function will work only for users from USA and UK.

Poll

2010-07-05T12:49:00.001+03:00

I'm just interested who my reader is. Could you answer the question at the right pane of the blog?

Extended handling of save event in forms with close dialog (like opportunitites)

2010-06-20T10:35:00.012+03:00

All developers who developed JavaScripts for CRM read this article. But there is no info about getting data from fields of "Close dialogue" (like opportunity close). I've made a little research on this theme and here is the result:

First of all - when you close opportunity event.Mode equals 5 (Deactivate event mode). New state, new status and close info you can get using following scripts placed in OnSave event handler of opportunity form, save form, publish entity:

if (event.Mode == 5)
{
    alert(crmFormSubmit.crNewState.value + '\n' + crmFormSubmit.crNewStatus.value + '\n' + crmFormSubmit.crActivityXml.value);
}

Create and close opportunity with won status:

You will see following result:

If you will try to close opportunity with lost status:

You will get following result:

How this can be used - for example you want make Description field required in the case when opportunity is won and competitor field when opportunity is lost. Put following script to OnSave event handler of opportunity form:

if (event.Mode == 5)
{
    var status = crmFormSubmit.crNewState.value;
    var xml = crmFormSubmit.crActivityXml.value;
    var XmlDoc = new ActiveXObject("Microsoft.XMLDOM");
    XmlDoc.async = false; 
    XmlDoc.loadXML(xml);

    //If status is won
    if (status == 1)
    {
        var descriptionnode = XmlDoc.selectSingleNode("//opportunityclose/description");
        if (descriptionnode == null || descriptionnode.nodeTypedValue == "")
        {
            alert("Opportunity can't be closed!\nFill description field in Close Opportunity Dialogue");
            event.returnValue = false;
            return false;
        }
    }
    else if (status == 2)
    {
        var competitornode = XmlDoc.selectSingleNode("//opportunityclose/competitorid");
        if (competitornode == null || competitornode.nodeTypedValue == "")
        {
            alert("Opportunity can't be closed!\nFill competitor field in Close Opportunity Dialogue");
            event.returnValue = false;
            return false;
        }
    }
}

Save form and publish opportunity entity. Then try to close opportunity.
Won scenario:

Lost Scenario:

This customizations are not documented so it seems that this is unsupported approach to customization...

Intergration Google Maps v.3 into Microsoft Dynamics CRM 4.0

2010-06-18T17:20:00.008+03:00

In this post I will describe how to integrate Google Maps v.3 with Microsoft Dynamics CRM 4.0 using IFrame customization.

1. I created simple html page with following code (I called this page MapIntegration.html):

<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no"/>
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<title>Map integration</title>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript">
  var geocoder;
  var map;
  function initialize() {
    geocoder = new google.maps.Geocoder();
    var latlng = new google.maps.LatLng(-34.397, 150.644);
    var myOptions = {
      zoom: 16,
      center: latlng,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    }
    map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
    var address = location.search;
    address = address.substring(address.indexOf('=') + 1);
    codeAddress(address);
  }
  function codeAddress(address) {
    if (geocoder) {
      geocoder.geocode( { 'address': address}, function(results, status) {
        if (status == google.maps.GeocoderStatus.OK) {
          map.setCenter(results[0].geometry.location);
          var marker = new google.maps.Marker({
              map: map,
              position: results[0].geometry.location
          });
        } else {
          alert("Geocode was not successful for the following reason: " + status);
        }
      });
    }
  }
</script>
</head>
<body style="margin:0px; padding:0px;" onload="initialize()">
<div id="map_canvas" style="width:100%; height:100%"></div>
</body>
</html>

2. Copy this page into ISV subdirectory of your Microsoft CRM website:

3. Open customization form of entity you want to integrate Google Maps in, create new tab, new section and new iframe:

4. Insert following script to OnLoad event handler:

crmForm.all.tab4Tab.onclick = function()
{
 var url = "";
 if (crmForm.all.address1_country.DataValue != null)
  url = crmForm.all.address1_country.DataValue;
 if (crmForm.all.address1_city.DataValue != null)
  url += (url == "" ? "" : ", ") + crmForm.all.address1_city.DataValue;
 if (crmForm.all.address1_name.DataValue != null)
  url += (url == "" ? "" : ", ") + crmForm.all.address1_name.DataValue;
 if (crmForm.all.address1_line1.DataValue != null)
  url += (url == "" ? "" : ", ") + crmForm.all.address1_line1.DataValue;
 if (crmForm.all.address1_line2.DataValue != null)
  url += (url == "" ? "" : ", ") + crmForm.all.address1_line2.DataValue;
 if (crmForm.all.address1_line3.DataValue != null)
  url += (url == "" ? "" : ", ") + crmForm.all.address1_line3.DataValue;
 if (url != "")
 {
  url = "/ISV/gmap/mapintegration.html?address=" + url;
  crmForm.all.IFRAME_map.src = url;
 }
}

5. Save form, publish entity, check the result:

Formatting datetime in workflows with custom workflow activity in Microsoft Dynamics CRM 4.0

2010-06-17T01:15:00.015+03:00

I've found one weird thing in workflows - when you insert datetime value inside text field you can't format it. Result of such inserting:

I decided to develop custom activity which will give possibility to format those output.
This activity has two inputs - datetime value to format (CrmDateTime property) and format (string property) and one output - formatted datetime (string). Code of custom action:

using System;
using System.Text;
using System.Collections.Generic;
using System.Workflow.Activities;
using Microsoft.Crm.Workflow;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;

namespace DateTimeFormatting
{
  [CrmWorkflowActivity("Formats date time with required format", "Formatting Routines")]
  public class FormatDateTime : SequenceActivity
  {
      protected override System.Workflow.ComponentModel.ActivityExecutionStatus Execute(System.Workflow.ComponentModel.ActivityExecutionContext executionContext)
      {
          string result = string.Empty;

          if (!string.IsNullOrEmpty(DateFormat) && datetime != null)
          {
              result =  DateTime.Parse(datetime.Value).ToString(DateFormat);
          }

          Result = result;

          return ActivityExecutionStatus.Closed;
      }

      public static DependencyProperty DateFormatProperty = DependencyProperty.Register("DateFormat", typeof(string), typeof(FormatDateTime));

      [CrmInput("Format of DateTime")]
      public string DateFormat
      {
          get
          {
              return (string)base.GetValue(DateFormatProperty);
          }
          set
          {
              base.SetValue(DateFormatProperty, value);
          }
      }

      public static DependencyProperty datetimeProperty = DependencyProperty.Register("datetime", typeof(CrmDateTime), typeof(FormatDateTime));

      [CrmInput("DateTime to format")]
      public CrmDateTime datetime
      {
          get
          {
              return (CrmDateTime)base.GetValue(datetimeProperty);
          }
          set
          {
              base.SetValue(datetimeProperty, value);
          }
      }

      public static DependencyProperty ResultProperty = DependencyProperty.Register("Result", typeof(string), typeof(FormatDateTime));

      [CrmOutput("Formatted DateTime")]
      public string Result
      {
          get
          {
              return (string)base.GetValue(ResultProperty);
          }
          set
          {
              base.SetValue(ResultProperty, value);
          }
      }

  }
}

Following screenshots show how to use this action:
1. Insert this action before using the result of formatting:

2. Click "Set Properties" button to insert datetime and format:

3. Fill "Format of DateTime" and "DateTime to Format" fields:

4. Add some workflow step formatted datetime to be used (for example sending email):

Save step and save and publish workflow. Result of work of this workflow:

Sourcecode:

Set default view for Service Calendar in Microsoft Dynamics CRM 4.0

2010-06-13T18:33:00.004+03:00

My customer asked me to set default view for Service Calendar to 'Facility/Equipment'. I haven't found any available supported solution for this task, so I decided to use unsupported approach - inserting JavaScript in page of Service Calendar.

1. You should open folder of Microsoft Dynamics CRM website.
2. Open SM Subdirectory and open for editing home_apptbook.aspx file.
3. Locate following lines:

<head>
<cnt:AppHeader runat="server" id="crmHeader"/>
</head>

4. Modify this text to following:

<head>
<cnt:AppHeader runat="server" id="crmHeader"/>
<script type="text/javascript">
OnLoadCompleted = function()
{
    var combo = document.getElementById('crmTypeSelector');

    combo.value = 'equipment';
    window.setTimeout(function() {OnViewTypeChange(combo);}, 0);
}

window.attachEvent('onload', OnLoadCompleted);
</script>
</head>

Blog of Friend and former colleague

2010-06-04T12:19:00.002+03:00

I want to recommend blog of my friend and former colleague Oleksandr Klymenko - http://darkaxe.wordpress.com/. We began work with Microsoft Dynamics CRM together and Oleksandr helped me a lot.

How to bind CRM Data to GridView on custom ASP.Net page for Microsoft Dynamics CRM

2010-05-28T20:01:00.005+03:00

I have seen this article one of my colleague Hassan Hussain. I've decided to use other approach to bind data to GridView on custom ASP.Net page - instead BusinessEntitiesCollection I used Fetch.

string fetch = @"   <fetch mapping=""logical"">
<entity name=""account"">
<attribute name=""accountid""/>
<attribute name=""name""/>
<attribute name=""address1_line1""/>
<attribute name=""address1_line2""/>
<attribute name=""address1_country""/>
<attribute name=""address1_city""/>
</entity>
</fetch>";

string result = service.Fetch(fetch);

XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
doc.DocumentElement.Attributes.RemoveAll();

StringReader sr = new StringReader(doc.OuterXml);
DataSet ds = new DataSet();
ds.ReadXml(sr);

GridView1.DataSource = ds;
GridView1.DataBind();

And the result:

Delete teams button in Microsoft Dynamics CRM 4.0

2010-05-27T07:17:00.003+03:00

There was question on AxForum - why it is not possible to delete team through interface. One of visitors - slivka_83 suggested to use SDK and Delete message. I've decided to make ISV.Customization which will add Delete button to Team grid.

        <Entity name="team">

          <Grid>

            <MenuBar>

              <Buttons>

                <Button Icon="/_imgs/ico_16_delete.gif" JavaScript = "var teams = getSelected('crmGrid');

for (var i = 0; i &lt; teams.length; i++)

{

    var xml = &quot;&lt;?xml version='1.0' encoding='utf-8'?&gt;&quot;+ 

    &quot;&lt;soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'&quot;+

    &quot; xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'&quot;+

    &quot; xmlns:xsd='http://www.w3.org/2001/XMLSchema'&gt;&quot;+ 

    GenerateAuthenticationHeader()+ 

    &quot;&lt;soap:Body&gt;&quot;+ 

    &quot;&lt;Delete xmlns='http://schemas.microsoft.com/crm/2007/WebServices'&gt;&quot;+ 

    &quot;&lt;entityName&gt;team&lt;/entityName&gt;&quot;+ 

    &quot;&lt;id&gt;&quot; + teams[i] + &quot;&lt;/id&gt;&quot;+ 

    &quot;&lt;/Delete&gt;&quot;+ 

    &quot;&lt;/soap:Body&gt;&quot;+ 

    &quot;&lt;/soap:Envelope&gt;&quot;;

    var xHReq = new ActiveXObject(&quot;Msxml2.XMLHTTP&quot;);

    xHReq.Open(&quot;POST&quot;, &quot;/mscrmservices/2007/CrmService.asmx&quot;, false);

    xHReq.setRequestHeader(&quot;SOAPAction&quot;,&quot;http://schemas.microsoft.com/crm/2007/WebServices/Delete&quot;);

    xHReq.setRequestHeader(&quot;Content-Type&quot;, &quot;text/xml; charset=utf-8&quot;);

    xHReq.setRequestHeader(&quot;Content-Length&quot;, xml.length);

    xHReq.send(xml);

}

crmGrid.Refresh();">

                  <Titles>

                    <Title LCID="1033" Text="Delete" />

                  </Titles>

                  <ToolTips>

                    <ToolTip LCID="1033" Text="Delete Selected Teams" />

                  </ToolTips>

                </Button>

              </Buttons>

            </MenuBar>

          </Grid>

        </Entity>

Attribute Mapping using Java Script

2010-04-20T13:12:00.004+03:00

I'm active visitor and answerer of several forums about Microsoft Dynamics CRM. I have seen a lot questions about mappings - how does it work. Mapping works only once - when new child record opened from parent record and when user just fills parent record lookup - of course mapping doesn't work. I've decided to fix this issue.

I've tested my script on mapping between account and contact. I've put following script to Onchange event handler of parentcustomerfield:

MapFields = function(sourceentity, targetentity, sourceentityid, invokeattributename)

{

    var xml = "" + 

    "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + 

    "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + 

    GenerateAuthenticationHeader() + 

    " <soap:Body>" +  

    " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 

    " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" + 

    " <q1:EntityName>attributemap</q1:EntityName>" + 

    " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" + 

    " <q1:Attributes>" + 

    " <q1:Attribute>targetattributename</q1:Attribute>" + 

    " <q1:Attribute>sourceattributename</q1:Attribute>" + 

    " </q1:Attributes>" + 

    " </q1:ColumnSet>" + 

    " <q1:Distinct>false</q1:Distinct>" + 

    "<q1:Criteria>"+ 

    "<q1:FilterOperator>And</q1:FilterOperator>"+ 

    "<q1:Conditions>"+ 

    "<q1:Condition>"+ 

    "<q1:AttributeName>parentattributemapid</q1:AttributeName>"+ 

    "<q1:Operator>Null</q1:Operator>"+ 

    "</q1:Condition>"+ 

    "</q1:Conditions>"+ 

    "</q1:Criteria>"+ 

    " <q1:LinkEntities>" + 

    " <q1:LinkEntity>" + 

    " <q1:LinkFromAttributeName>entitymapid</q1:LinkFromAttributeName>" + 

    " <q1:LinkFromEntityName>attributemap</q1:LinkFromEntityName>" + 

    " <q1:LinkToEntityName>entitymap</q1:LinkToEntityName>" + 

    " <q1:LinkToAttributeName>entitymapid</q1:LinkToAttributeName>" + 

    " <q1:JoinOperator>Inner</q1:JoinOperator>" + 

    " <q1:LinkCriteria>" + 

    " <q1:FilterOperator>And</q1:FilterOperator>" + 

    " <q1:Conditions>" + 

    " <q1:Condition>" + 

    " <q1:AttributeName>sourceentityname</q1:AttributeName>" + 

    " <q1:Operator>Equal</q1:Operator>" + 

    " <q1:Values>"+ 

    " <q1:Value xsi:type='xsd:string'>"+sourceentity+"</q1:Value>"+ 

    " </q1:Values>"+ 

    " </q1:Condition>" + 

    " <q1:Condition>" + 

    " <q1:AttributeName>targetentityname</q1:AttributeName>" + 

    " <q1:Operator>Equal</q1:Operator>" + 

    " <q1:Values>"+ 

    " <q1:Value xsi:type='xsd:string'>"+targetentity+"</q1:Value>"+ 

    " </q1:Values>"+ 

    " </q1:Condition>" + 

    " </q1:Conditions>" + 

    " </q1:LinkCriteria>" + 

    " </q1:LinkEntity>" + 

    " </q1:LinkEntities>" + 

    " </query>" + 

    " </RetrieveMultiple>" + 

    " </soap:Body>" + 

    "</soap:Envelope>"; 



    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP"); 

    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false); 

    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); 

    var results = xmlHttpRequest.responseXML.getElementsByTagName('BusinessEntity');



    xml = "<?xml version='1.0' encoding='utf-8'?>"+ 

    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+

    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+

    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+ 

    GenerateAuthenticationHeader() + 

    "<soap:Body>"+ 

    "<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+ 

    "<entityName>" + sourceentity + "</entityName>"+ 

    "<id>" + sourceentityid + "</id>"+ 

    "<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>"+ 

    "<q1:Attributes>";





    var mappings = new Array();



    for (var i=0; i < results.length; i++)

    {

        mappings[i] = new Object();

        mappings[i].sourceattribute = results[i].selectSingleNode('./q1:sourceattributename').nodeTypedValue;

        mappings[i].targetattribute = results[i].selectSingleNode('./q1:targetattributename').nodeTypedValue;



        xml += "<q1:Attribute>" + mappings[i].sourceattribute + "</q1:Attribute>";

    }

    



    xml += "</q1:Attributes>"+ 

    "</columnSet>"+ 

    "</Retrieve>"+ 

    "</soap:Body>"+ 

    "</soap:Envelope>";



    xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);

    xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");

    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);

    xmlHttpRequest.send(xml);

    var resultXml = xmlHttpRequest.responseXML;



    for(var i = 0; i < mappings.length; i++)

    {

        try

        {

            var currentfieldtype = crmForm.all[mappings[i].targetattribute].className;

            var sourcefieldvalue = resultXml.selectSingleNode("//q1:" + mappings[i].sourceattribute);

            if (sourcefieldvalue != null && mappings[i].targetattribute != invokeattributename)

            {

                var targetfieldvalue = null;



                if (currentfieldtype == "ms-crm-Text" || currentfieldtype == "ms-crm-SelectBox ")

                    targetfieldvalue = sourcefieldvalue.nodeTypedValue;

                else if (currentfieldtype.indexOf("ms-crm-Lookup") == 0)

                {

                    targetfieldvalue = new Array();

                    targetfieldvalue[0] = new Object();

                    targetfieldvalue[0].id = sourcefieldvalue.nodeTypedValue;

                    targetfieldvalue[0].name = sourcefieldvalue.getAttribute("name");

                    targetfieldvalue[0].type = crmForm.all[mappings[i].sourceattribute].getAttribute("lookuptypes");

                }

                else if (currentfieldtype == "ms-crm-DateTime")

                {

                    var datetimestring = sourcefieldvalue.nodeTypedValue;

                    var firstpart = datetimestring.split('T')[0];

                    var secondpart = datetimestring.split('T')[1].split('+')[0];

                    var parts =  firstpart .split('-');

                    var parts2 = secondpart.split(':')



                    targetfieldvalue = new Date();



                    targetfieldvalue.setYear(parts[0]);

                    targetfieldvalue.setMonth(parts[1] - 1);

                    targetfieldvalue.setDate(parts[2]);



                    targetfieldvalue.setHours(parts2[0]);

                    targetfieldvalue.setMinutes(parts2[1]);

                    targetfieldvalue.setSeconds(parts2[2]);

                }

                    



                crmForm.all[mappings[i].targetattribute].DataValue = targetfieldvalue;

                crmForm.all[mappings[i].targetattribute].ForceSubmit = true;

            }

        }

        catch(err) {}

    }

}



if (crmForm.all.parentcustomerid.DataValue != null && crmForm.all.parentcustomerid.DataValue[0].typename == "account")

    MapFields("account", "contact", crmForm.all.parentcustomerid.DataValue[0].id, "parentcustomerid");

CRM Usage Report

2010-04-13T21:14:00.005+03:00

One of customer wanted to have the possibility to see who and when was working in CRM. I knew that MVP David Jennaway had created such solution based on IIS's logs. No matter how I tried - I failed to implement it. Logs weren't written to log database. Also this approach doesn't work in IFD deployment scenario.

So I left this idea and begun develop own solution.

Solution will consist of 3 parts:
1. DataBase.
2. Plugin which will fill log database (it will registered on most usable messages - Execute, RetrieveMultiple, Retrieve, Create, Update, Delete).
3. Report which will display the data.

DataBase. I decided not to use the CRM database to store users' activity information because of performance. I've created table to store data. This table can be created with following script:

CREATE TABLE [dbo].[UserLog](
 [UserId] [uniqueidentifier] NULL,
 [UserName] [varchar](max) NULL,
 [OrgName] [varchar](max) NULL,
 [RecordDateTime] [datetime] NULL,
 [SourceHost] [varchar](max) NULL
) ON [PRIMARY]

GO

Plugin

Plugin function is to retrieve information about user, organization and IP address of the user's computer. The code of the plugin:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;
using System.Data.SqlClient;
using System.Web;

namespace UserActionsLogger
{
    public class UALogger : IPlugin
    {

        #region Privates

        private readonly string _connectionString = string.Empty;

        #endregion Privates

        #region CTOR

        public UALogger(string config, string secureConfig)
        {
            _connectionString = config;
        }

        #endregion CTOR

        #region IPlugin Members

        public void Execute(IPluginExecutionContext context)
        {
            //Check that author is application (not async service or webservice)
            if (context.CallerOrigin == CallerOrigin.AsyncService || 
                context.CallerOrigin == CallerOrigin.WebServiceApi)
                return;

            try
            {
                //IP retrieving
                string hostname = string.Empty;
                HttpContext webContext = HttpContext.Current;
                if (webContext != null)
                    hostname = webContext.Request.UserHostName;
                if (webContext != null && hostname != string.Empty)
                    hostname = webContext.Request.UserHostAddress;

                //User Name retrieving
                Guid curentUserId = context.UserId;
                ICrmService crmservice = context.CreateCrmService(true);
                systemuser su = (systemuser)crmservice.Retrieve(EntityName.systemuser.ToString(), curentUserId, new ColumnSet(new string[] {"fullname"}));
                string username = su.fullname;

                //Removing of system accounts
                if (username.ToUpper() == "SYSTEM" ||
                    username.ToUpper() == "INTEGRATION")
                    return;

                //Savig of the data
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand cmd = new SqlCommand("", connection))
                    {
                        cmd.CommandText = "Insert Into UserLog(UserId, UserName, OrgName, RecordDateTime, SourceHost) Values(@UserId, @UserName, @OrgName, @recordDateTime, @SourceHost)";
                        cmd.Parameters.AddWithValue("@UserId", curentUserId);
                        cmd.Parameters.AddWithValue("@UserName", username);
                        cmd.Parameters.AddWithValue("@OrgName", context.OrganizationName);
                        cmd.Parameters.AddWithValue("@RecordDateTime", DateTime.Now);
                        cmd.Parameters.AddWithValue("@SourceHost", hostname);

                        cmd.ExecuteNonQuery();
                    }

                    connection.Close();
                }
            }
            catch { }
        }

        #endregion IPlugin Members
    }
}

It is required to pass connection string to log DB in config property at the plugin's step registration. Sample how to register the plugin's step:

Report

SQL Query of the report:

--Creation of temp table to store time intervals
Create Table #TimeTable(StartDate DateTime, EndDate DateTime)

--Filling time intervals table
while @StartDate < @EndDate
Begin
    Insert Into #TimeTable 
    Values(@StartDate, DATEADD(minute, @Delta, @StartDate))

    Set @StartDate = DATEADD(minute, @Delta, @StartDate)
End

--Retrieving data for reports
Select
    Distinct
    t.StartDate
    ,t.EndDate
    ,UserName
    ,SourceHost
From 
    #TimeTable t
    Inner Join UserLog u 
        on u.RecordDateTime > t.StartDate 
        And u.RecordDateTime <= t.EndDate

--Temp table's deletion
Drop Table #TimeTable

Result:

Source code of plugin and report:

Moving Reporting Services From 80 port to other

2010-03-29T16:55:00.003+03:00

One of my customers had following problem:
SQL Server and Microsoft CRM were deployed on one server. CRM was deployed on 5555 port.

Then Apache was deployed on this server on default port. After this action reports of CRM were broken (because RS works on default 80 port). I've fixed this issue using following steps:

1. I've created dummy website on port 4444.
2. I've opened following folder \MSRS10.MSSQLSERVER\Reporting Services\ReportServer and opened file rsreportserver.config with notepad.
3. I've found following lines:

 <URLReservations>
 <Application>
  <Name>ReportServerWebService</Name>
  <VirtualDirectory>ReportServer</VirtualDirectory>
  <URLs>
   <URL>
    <UrlString>http://+:80</UrlString>
    <AccountSid>S-1-5-18</AccountSid>
    <AccountName>LocalSystem</AccountName>
   </URL>
  </URLs>
 </Application>
 <Application>
  <Name>ReportManager</Name>
  <VirtualDirectory>Reports</VirtualDirectory>
  <URLs>
   <URL>
    <UrlString>http://+:80</UrlString>
    <AccountSid>S-1-5-18</AccountSid>
    <AccountName>LocalSystem</AccountName>
   </URL>
  </URLs>
 </Application>
</URLReservations>

and change it to:

 <URLReservations>
 <Application>
  <Name>ReportServerWebService</Name>
  <VirtualDirectory>ReportServer</VirtualDirectory>
  <URLs>
   <URL>
    <UrlString>http://+:4444</UrlString>
    <AccountSid>S-1-5-18</AccountSid>
    <AccountName>LocalSystem</AccountName>
   </URL>
  </URLs>
 </Application>
 <Application>
  <Name>ReportManager</Name>
  <VirtualDirectory>Reports</VirtualDirectory>
  <URLs>
   <URL>
    <UrlString>http://+:4444</UrlString>
    <AccountSid>S-1-5-18</AccountSid>
    <AccountName>LocalSystem</AccountName>
   </URL>
  </URLs>
 </Application>
</URLReservations>

4. I've opened SQL Server Management Studio and executed following query against MSCRM_Config Database - Update organization Set SrsUrl = 'http://v1:4444/reportserver'

Where v1 - is server where SQL and SRS were installed and 4444 - port on which reportserver were deployed.

Everything began to work after reboot of server.

Independent consultant from March

2010-02-23T16:46:00.003+02:00

5'th of March will be my last working day in current company. I become independent consultant after leaving company.

If you need install/upgrade/customize Microsoft Dynamics CRM you can contact me via e-mail, ICQ, MSN or Skype.

My experience:
- preparation of IT-infrastructure for installation;
- install and administration;
- data import from other systems;
- customization and development (entities and views customization, scripts development, workflows, plugins and custom workflow activities, reports).

Record Counter for Microsoft Dynamics CRM 4.0: once more

2010-02-21T17:32:00.006+02:00

I've developed new version of record counter. Now it works not only for main grids of entities - now it works for associated views to.

Results for associated views you can see at screenshots:

Source code and complete dll you can download here:

Plugin registration:

Assembly:

Execute step registration:

RetrieveMultiple step registration:

Rollup step registration:

Full registration:

To make possible Rollup message handleable with plugins you should execute following query against organization database:

Update SdkMessageFilter 
Set IsCustomProcessingStepAllowed = 1
Where SdkMessageId in 
(Select SdkMessageId From SdkMessage Where Name = 'Rollup')

Custom workflow action which returns Opportunity Close object based on Opportunity object

2010-02-11T10:08:00.007+02:00

All data you've input into the CRM is stored in opportunityclose record when opportunity is closed. And this data is inaccessible from workflow designer because opportunityclose record is child record for opportunity. And I'll try to fix it.

I will made it using custom workflow action. So the code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Workflow.Activities;
using Microsoft.Crm.Workflow;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;

namespace OpportunityProductPlugin
{
    [CrmWorkflowActivity("Get opportunity close record", "Opportunity utiles")]
    public class OpportunityCloseGetter : SequenceActivity
    {
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            if (Opportunity != null)
            {
                IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
                IWorkflowContext workflowContext = contextService.Context;
                ICrmService crmservice = workflowContext.CreateCrmService();

                //Building the query to get opportunity close record
                QueryByAttribute query = new QueryByAttribute();
                query.Attributes = new string[] { "opportunityid" };
                query.ColumnSet = new AllColumns();
                query.EntityName = EntityName.opportunityclose.ToString();
                query.Values = new object[] { Opportunity.Value };

                //Retrieving the data
                List<BusinessEntity> entities = crmservice.RetrieveMultiple(query).BusinessEntities;

                //Check that opportunity close record exists
                if (entities.Count == 1)
                {
                    //Filling output lookup
                    OpportunityClose = new Lookup(EntityName.opportunityclose.ToString(),((opportunityclose)entities[0]).activityid.Value);
                }
            }

            return ActivityExecutionStatus.Closed;
        }

        public static DependencyProperty OpportunityProperty = DependencyProperty.Register("Opportunity", typeof(Lookup), typeof(OpportunityCloseGetter));

        [CrmInput("Opportunity")]
        [CrmReferenceTarget("opportunity")]
        public Lookup Opportunity
        {
            get
            {
                return (Lookup)base.GetValue(OpportunityProperty);
            }
            set
            {
                base.SetValue(OpportunityProperty, value);
            }
        }

        public static DependencyProperty OpportunityCloseProperty = DependencyProperty.Register("OpportunityClose", typeof(Lookup), typeof(OpportunityCloseGetter));

        [CrmOutput("OpportunityClose")]
        [CrmReferenceTarget("opportunityclose")]
        public Lookup OpportunityClose
        {
            get
            {
                return (Lookup)base.GetValue(OpportunityCloseProperty);
            }
            set
            {
                base.SetValue(OpportunityCloseProperty, value);
            }
        }
    
    }
}

How to use it - I build simple on-demand workflow:

Source code and ready-to-deploy assembly can be downloaded here.

Making Description Field Required in Close Opportunity Dialog

2010-01-20T14:51:00.005+02:00

This is unsupported customization, so be careful.

Open folder with your CRM website. Open SFA\opps subcatalog.

Open dlg_closeopp.aspx file using notepad and find there following function:

function applychanges()
{
var retval = "";


if (actualend.DataValue == null)
{
alert( LOCID_CLOSE_DATE_NOT_SUPPLIED );
actualend.SetFocus();
return;
}

if ((actualrevenue.DataValue == null) && radWon.checked)
{
alert( LOCID_ACT_REVENUE_NOT_SUPPLIED );
actualrevenue.focus();
return;
}


var xml = "<opportunityclose>";
xml += actualrevenue.DataXml;
xml += "<activityid><%= SequentialGuid.CreateGuid().ToString("B", CultureInfo.InvariantCulture).ToUpper(CultureInfo.InvariantCulture) %></activityid>";
xml += "<opportunityid>" + _iId + "</opportunityid>";
xml += description.DataXml;

xml += actualend.DataXml;
xml += "<subject>" + CrmEncodeDecode.CrmXmlEncode(_subject) + "</subject>";
if (radLost.checked)
{
with(crmLookup)
{
if(!IsNull(DataValue))
{
xml += "<competitorid>" + DataValue[0].id + "</competitorid>";
}
}
}
xml += "</opportunityclose>";

var oReturn = new Object();
var wonState = 1;
var lostState = 2;

oReturn.State = ((radWon.checked) ? wonState : lostState);
oReturn.StatusCode = ((radWon.checked) ? selWinStatus.DataValue : selLoseStatus.DataValue);
oReturn.ActivityXml = xml;

window.returnValue = oReturn;
window.close();

}

and modify it to:

function applychanges()
{
var retval = "";


if (actualend.DataValue == null)
{
alert( LOCID_CLOSE_DATE_NOT_SUPPLIED );
actualend.SetFocus();
return;
}

if ((actualrevenue.DataValue == null) && radWon.checked)
{
alert( LOCID_ACT_REVENUE_NOT_SUPPLIED );
actualrevenue.focus();
return;
}

if (description.value.replace(/^\s+|\s+$/, '') == '')
{
    alert('Input Description!');
    description.focus();
    return;
}

var xml = "<opportunityclose>";
xml += actualrevenue.DataXml;
xml += "<activityid><%= SequentialGuid.CreateGuid().ToString("B", CultureInfo.InvariantCulture).ToUpper(CultureInfo.InvariantCulture) %></activityid>";
xml += "<opportunityid>" + _iId + "</opportunityid>";
xml += description.DataXml;

xml += actualend.DataXml;
xml += "<subject>" + CrmEncodeDecode.CrmXmlEncode(_subject) + "</subject>";
if (radLost.checked)
{
with(crmLookup)
{
if(!IsNull(DataValue))
{
xml += "<competitorid>" + DataValue[0].id + "</competitorid>";
}
}
}
xml += "</opportunityclose>";

var oReturn = new Object();
var wonState = 1;
var lostState = 2;

oReturn.State = ((radWon.checked) ? wonState : lostState);
oReturn.StatusCode = ((radWon.checked) ? selWinStatus.DataValue : selLoseStatus.DataValue);
oReturn.ActivityXml = xml;

window.returnValue = oReturn;
window.close();

}

Demonstration: