Thursday, July 17, 2008

Build a combined activity view in Dynamics CRM 4.0

I'm often asked whether it's possible in 4.0 to view all activity related to an entity, regardless of whether the activity item has been sent, completed, or closed? Another common question is whether it's possible to view the sender and recipient(s) of an e-mail in a CRM grid view.

Perhaps those capabilities will appear in 5.0 but in 4.0 you'll have to build or buy a solution for this. One of my clients decided to have my team build this functionality and the screen below gives you a glimpse of what it turned-out like. Note that I had to gray-out some of the entries to protect private information.


Besides listing open and closed (History) activities in a single grid, we also built a preview pane to allow users to quickly view e-mail content and other activity details.

I'm not authorized to provide the source code for this solution but I can provide an overview of the solution in case you want to build something like this.

Application development: Visual Studio 2008 targeting .NET 3.0

ASPX overview: The ASPX page uses the standard ASP.NET GridView control. The middle section of the page includes a vertical resizing "control" (a table with a background image and linked JScript to provide sizing functionality). The preview pane consists of a textarea and iframe. The textarea is used to show non-HTML activity details (i.e., Tasks) and e-mail content is shown in the IFRAME.

Query: We debated whether to call the CRM Web Service to query for activities or whether it was better to use SQL (ADO.NET) via classes in System.Data.SqlClient. For performance reasons we decided to use SQL. The query consists of one query for each activity type combined with the SQL SELECT UNION clause. In the screenshot, you'll see a combobox that allows activity type filtering. We pass that selection into a parameter into the SQL query.

Grid View Columns: The activity grid view contains the following columns: Activity Type, Created On, Subject, Description (e.g., a substring of the body of an e-mail), Source (e-mail sender), To (recipient(s)), Status, Owner, Created By, Modified By, Last Updated, Priority, and Resolution. Users can sort on any of those columns.

Other Features: We also provided the ability to export the grid to Excel and the ability to delete activities that are open/pending. That last feature was requested because the client turned off the ability to delete activities for all user roles but they wanted users to be able to delete open or pending activity, such as an e-mail that was drafted but not sent.

Lastly, we deployed the solution as a virtual directory under the Microsoft Dynamics CRM 4.0 Web application. I know this is "unsupported" by Microsoft but the client had already built custom pages and placed them in a virtual under the CRM root so we went with their approach.

Let's just hope that 5.0 includes lots of new UI capabilities to make this type of view possible. And drag-and-drop column reordering, colorized rows (based on business logic), and inline editing would also be great to have!

1 comment:

  1. Hi Tim,

    I have a custom page just like yours. This is a searchgrid finding associated contacts and accounts. The page is using sql and standard grid, just like yours seem to do. In development environment (and our internal application) everything works out fine. But not in the the production environment. MS Support has been envolved but has found the authentication error I get.
    It appears tohave something to do with kerberus, but I'm not sure anymore.
    Have you a simple way to use the webservice as approach for creating a dataset, that can be used?
    Then i might try that instead.

    Kind regards

    ReplyDelete