Tuesday, July 12, 2011

Modifying Queue Item Views

While the default queue layout might be fine for most users, there are instances when it would be nice if the views for queue items could be modified.  Let's take for example, incoming email queues.  The default layout for an incoming email queue shows the title, type of activity, and the date the activity entered the queue.  What it noticeably does not show is when the email was actually received.  For an incoming email queue, this is typically the creation date.  The activity preview view will show this information, but if you have been assigned several email activities, and would like to work on them in the order the emails were received, the preview isn't a very user friendly way to accomplish this.  It would be better to add the Created On attribute to the queue item view and sort the emails accordingly.

Unfortunately in CRM 4.0, queue item is a system entity, and cannot be customized.  This includes the views.  I believe this to be an oversight on Microsoft's part, one which was corrected in CRM 2011.  CRM supports modifying views that cannot be removed in its system customizable entities, so it would seem that you should be able to modify the views on its system entities as well.

In fact, this can be done, although you have to work around CRM's restrictions to do so.  First, you have to find the Query Id for the view in question.  This can be found easily enough by navigating to the view, and selecting Advanced Find.  Advanced Find will open to this view, and the Query Id can be found in the URL.  You may need to change your Internet Explorer options to always open pop-ups in a new tab in order to insure that the URL for the window is displayed.  The Query Ids for the queue item views are {00000000-0000-0000-00AA-000010001400}, {00000000-0000-0000-00AA-000010001403}, and {00000000-0000-0000-00AA-000010001404}.


Once you have the Query Id, you can call the view manager page directly to modify the view.  The URL for the view manager is http(s)://ServerName/tools/viewEditor/viewManager.aspx?id={[Query Id]}.  From here, you modify the view in the same way you would modify any other view in the system.  You can add columns, change the sort, etc. 



After making the required modifications and saving them, you are ready to publish.  Unfortunately you cannot just select the queue item entity and publish it.  Dynamics CRM will not allow system entitites to be published.  The only way to publish your customizations is to select "Publish All Customizations from the Actions menu.  This will publish your changes to the queue item views, and as the name implies will publish any other pending changes.


Once published, you should see the changes when viewing the queues.  You will need to change all three views mentioned above to see the changes consistently.  While I've used queue item here as an example, this process will work for any of the system entities.



Wednesday, February 16, 2011

Accessing Dynamics CRM filtered views using a SQL Server account

When writing reports or dashboards for Dynamics CRM, the filtered views are very useful. They automatically filter the data based on the application defined security without having to go through the web services.  Typically, the filtered views are the only database objects exposed to the average user in Dynamics CRM, and they are the only access method aside from web services that is officially supported. They do have a major limitation though in that only a Windows account can normally be used to access them. Using a SQL server account will result in no data being returned. There are some instances where access to the database is required, but a Windows account just isn't feasible.

There is a way to access filtered views using a SQL Server user account by taking advantage of a feature built into the filtered views. There are two steps required.

First, add the SQL Server user account, giving it access to the organization database in the CRMReaderRole role.

USE [master]
GO
CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword'
GO
USE [ORGDB_MSCRM]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
GO
EXEC sp_addrolemember N'CRMReaderRole', N'MyUser'
GO

Next, obtain the GUID for the Dynamics CRM user you would like to impersonate.  This can be done by querying the SystemUser table directly, or through the Dynamics CRM user interface by displaying the user's properties and examining the page URL .  Use the Transact-SQL SET CONTEXT_INFO to set the context information to this GUID before querying the filtered view.

DECLARE  @uid uniqueidentifier
SET @uid = convert(uniqueidentifier, 'user_guid_goes_here')
SET CONTEXT_INFO @uid

SELECT TOP 1000 *
  FROM [ORGDB_MSCRM].[dbo].[FilteredContact]

The query will return the records as if you were connected using the given user's Windows account.  It should be noted that the SET CONTEXT_INFO is only required when establishing the connection, and once set will remain so for the duration of the session.

Saturday, January 29, 2011

Building a Windows Phone 7 CRM client

This Christmas I decided to treat myself by buying a new Windows Phone 7 (WP7).  Since I have my family on the T-Mobile network, I chose the HD7 phone from HTC.  While I find this phone a little too large to use comfortably in one hand, the large screen is great for gaming and media applications.  I have an Xbox Live family subscription, and the Xbox Live integration lets me check on what my family is doing on their Xboxes, send messages directly to the game consoles, and check on any game invites or turn notifications I might have received.  I’m patiently waiting for Fable Coin Golf to be released for WP7 so that I can earn money for my Xbox Fable III character through the phone.

While I think the iTunes application is more polished and easier to use than the Zune software, the subscription model followed by the Zune certainly makes building a large music library quickly much cheaper.  I don’t typically watch Netflix on my WP7, but I do watch a number of podcasts.  These both look and sound great through the HD7.  For this reason, I’ve replaced my iPod Touch with my WP7.  A tip for anyone using HTC phones, download the HTC Sound Enhancer application from the marketplace.   It makes a big difference in the sound quality, especially when using headphones.  Also, HTC’s YouTube browser is much better than any of the others on the marketplace.

Games and media aside, I have been pleasantly surprised at just how productive I have been on the WP7 in a very short period of time.  Through the people hub I can search my company’s Exchange global address book and can save contacts from there to one of my personal address lists.  The contact information from Outlook, Windows Live, Google and Facebook are seamlessly integrated into a single display.  I have my family contacts pinned to my start screen, and in addition to giving instant access to call or text them, I can also see any social status updates they have made right on my start screen.

The Office hub lets me open and edit any office document and post it to SharePoint if desired.  The SharePoint integration works great, although oddly enough it does not work if the server only supports Windows authentication.  Since getting the WP7, OneNote has actually became useful again.  My notes taken on the phone are automatically uploaded to my Windows Live SkyDrive.

The most notable absences in the WP7 are CRM and Office Communicator.  Microsoft has announced that the Lync (Office Communicator's successor) for WP7 is in the works, and the list of features includes messaging, VOIP calling, conference calls and seamless call transfers from the desktop to the phone.

While I’m certain that Dynamics CRM integration is also in the works, I naturally couldn’t wait.  My organization isn’t yet on CRM 2011, so I built a client against our on premise CRM 4.0 deployment.  The related code for this project can be downloaded here.  In order to get it to work, add a service reference to the CRMMobileClient project to your CRM deployment and name the service reference CrmService.




The first issue to overcome in building a CRM client was how to reference the CRM web services.  There isn’t a CRM mobile SDK yet, and the WP7 does not support the older style web service client suggested for accessing the CRM web services.  This leaves using a WCF client to access the web services as the only option.  But after adding the service reference to the Silverlight project, there are several missing attributes to the generated proxy class.  Most notably the generated service does not have a CrmAuthenticationTokenValue property.

Fortunately a lot of the research had already been done for me by Pierre-Adrien Forestier in his blog post How to consume CRM 4.0 Web Services from a WCF client like Silverlight applications.  This blog post describes how to add the CrmAuthenticationToken header to the outgoing requests using the OperationContextScope and a custom serializer class.   Since the WP7 development platform allows for Silverlight applications, this code seemed ideal.  Unfortunately as written it only works when using Windows integrated authentication.  There isn’t a way to pass Windows credentials on the WCF transport from the WP7, so using Windows authentication is not an option.  Basic authentication must be used on the CRM IIS server in order to access it from phone applications.  Using the TransportCredentialOnly security mode you can simply set the id and password on the client credentials object and with the custom serializer you will be able to access the CRM server over HTTP.

System.ServiceModel.BasicHttpBinding binding = new 
    System.ServiceModel.BasicHttpBinding(
    System.ServiceModel.BasicHttpSecurityMode.TransportCredentialOnly);
 
 binding.Name = "CrmServiceBinding";

service.ClientCredentials.UserName.UserName = "username";
service.ClientCredentials.UserName.Password = "password";

Unfortunately, this only works over HTTP; executing this code over HTTPS will generate an error.  The next hurtle was how to use Basic authentication securely.  Using a secure connection is recommended since Basic authentication passes the id/password in clear text, but there isn’t a built in way to pass Basic authentication credentials from the WP7.  Normally you would use code similar to the following:

System.ServiceModel.BasicHttpBinding binding = new 
    System.ServiceModel.BasicHttpBinding(
    System.ServiceModel.BasicHttpSecurityMode.Transport);
 
 binding.Name = "CrmServiceBinding";
 binding.Security.Mode = System.ServiceModel.BasicHttpSecurityMode.Transport;
 binding.Security.Transport.ClientCredentialType = 
    System.ServiceModel.HttpClientCredentialType.Basic;

Unfortunately, Silverlight does not expose the Transport property of the BasicHttpSecurity object so there is no way to configure the client proxy to automatically add the Basic authentication header to your requests.  To implement Basic authentication over HTTPS you must manually add the required header to the request in a similar fashion to adding the CrmAuthenticationToken.  The following code demonstrates.
 
public void AsyncGetContact(Guid contactId)
{
    using (new OperationContextScope((IContextChannel)Service.InnerChannel))
    {
        string myCredentials = Convert.ToBase64String(Encoding.UTF8.GetBytes(UserName + ":" + Password)); 
        // You can't configure the authentication in the transport, so the basic 
        // authentication header has to be added here.
        HttpRequestMessageProperty prop = new HttpRequestMessageProperty();
        prop.Headers["Authorization"] = "Basic " + myCredentials;
        OperationContext.Current.OutgoingMessageProperties.Add(
            HttpRequestMessageProperty.Name, prop);
        // here, we inject the CRM Authentication header 
        // The Xml formatter is just to prevent some characters from being encoded 
        // (otherwise CRM can't parse the authentication token properly)
        MessageHeader header = MessageHeader.CreateHeader("CrmAuthenticationToken", 
            "http://schemas.microsoft.com/crm/2007/WebServices""", 
            new MyCrmAuthenticationTokenSerializer(0, Organization, null));
                
        // Insert SOAP header 
        OperationContext.Current.OutgoingMessageHeaders.Add(header);

        // Create the column set to retrieve
        ColumnSet cols = new ColumnSet();
        cols.Attributes = ContactEntity.ColumnList;

        // Create the retrieve target.
        TargetRetrieveDynamic targetRetrieve = new TargetRetrieveDynamic();
        // Set the properties of the target.
        targetRetrieve.EntityName = EntityName.contact.ToString();
        targetRetrieve.EntityId = contactId;
        RetrieveRequest req = new RetrieveRequest();
        req.ColumnSet = cols;
        req.ReturnDynamicEntities = true;
        req.Target = targetRetrieve;
        Service.ExecuteAsync(req);
    }
}

Another item you might notice in the above code is that it uses the Execute method and DynamicEntity rather than the Retrieve method and the custom contact entity class.  For a line of business application, using the generated entity classes isn’t an issue, but I wouldn’t recommend it if you plan to distribute your application on the marketplace.

To test my client class I built a small test application.  This application simply allows you to enter the server, organization, user name, password, and the GUID of a contact you wish to retrieve from CRM.  This wouldn’t be overly useful for a LOB application, but does work to insure that the CRM system can be successfully queried.


An interesting anomaly I found when building this application is that the initial connection to the web service takes much longer than subsequent queries (from 8 to sometimes 30 seconds).  Stranger still is the fact that even though the call is asynchronous, rather than return immediately as expected, several seconds pass as the call is invoked.  After doing some research I’ve found that this is typical of WCF clients and that two reasons are usually the culprit.  The first is related to the client attempting to connect to a proxy before making the web service connection.  It’s doubtful that this would be the case on the WP7, and hopefully it isn’t as neither useDefaultWebProxy nor bypassProxyOnLocal are accessible to alter this behavior.   The second possible cause has to do with XML serialization.   According to http://msdn.microsoft.com/en-us/library/aa751883.aspx the startup speed of a WCF client can be improved by using the svcutil.exe tool to generate code to serialize the xml.  Unfortunately this tool does not work with Windows phone assemblies, but I'm confident that serialization code can be created by including the classes in a traditional .Net assembly, using the tool to generate the serializtion classes, and then adding these classes back to your WP7 project.   I have yet to attempt this experiment, but will be sure to post an update when I do.

To work around this issue in the UI, make sure that you call the service code from a different thread.  While it seems odd to call an asynchronous method from another asynchronous method, not doing so causes a noticeable delay as the service asynchronous call is launched.

While WCF and REST services are becoming quite widespread, there are still a number of applications that work using older style web services.  While some of the challenges to connecting to Dynamics CRM are unique, most of the issues are typical of connecting to web services.  I hope this blog entry gives some useful tips to anyone out there struggling to get their own connected applications to work properly.