A client called me up today and asked how she can copy all of the notes written for an entity to the Windows Clipboard. (Her company is still on version 3.0.) I told her "All you have to do is click the Notes tab on the entity record, click inside the Notes area, press Ctrl+A to copy all of the text, and then press Ctrl+C to copy the text." Her response: "Um, that doesn't work."
What!? Come on, that has to work. But then it hit me... that doesn't work... I tried that in 4.0 recently and it didn't work there either.
She needed all of the notes copied out of the entity ASAP so here's the "work around" (because isn't this really a feature flaw?!) I gave her:
1) Open the entity record.
2) Click the Print button.
3) Set the printer to Microsoft XPS Document Writer.
4) Click Print and provide a file name.
5) Open that file... it loads in IE. You can then highlight the text and copy it to the clipboard.
She said, hesitantly... "Okay, um, thanks."
The next time I'm in working on her company's system I'll create a solution (likely a report) that makes copying notes to the clipboard much easier, but if you're reading this and realize that I completely missed a built-in way of copying all entity notes please let me know!
Wednesday, July 30, 2008
Saturday, July 19, 2008
Getting Fiddler to work on the CRM 4.0 VPC
After loading Fiddler for the first time on the CRM 4.0 VPC, you'll likely see an error on startup. Fiddler indicates that it cannot listen on port 8888.
Here's how to get Fiddler working on the VPC:
1) Configure Fiddler to listen on port 8889. Then restart the app.
2) Rather than browsing localhost:5555 to load CRM, use this URL instead: http://moss.litwareinc.com:5555/loader.aspx. Fiddler cannot capture requests on localhost but can when the machine name is used instead.
After making those two adjustments you should be all set!
Here's how to get Fiddler working on the VPC:
1) Configure Fiddler to listen on port 8889. Then restart the app.
2) Rather than browsing localhost:5555 to load CRM, use this URL instead: http://moss.litwareinc.com:5555/loader.aspx. Fiddler cannot capture requests on localhost but can when the machine name is used instead.
After making those two adjustments you should be all set!
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!
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.
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!
Determine attribute utilization for CRM entities
Upgrading Dynamics CRM 3.0 systems to 4.0 has kept me very busy lately. I've been involved with four upgrades in the past few months and have more on the horizon I'm sure.
On one of the upgrade projects, the client's previous IT staff built-up the Account entity to include about 300 attributes. Now, I'm not one to quickly judge this as a bad idea without getting the facts and rationale for having so many custom attributes. Although I wouldn't have designed the entity the same way (I would've used a few picklists to replace the 100+ bit fields) the current design was built, was working, and it wasn't in the budget to change that part of the system.
The problem with having unpopulated attributes is not so much the unnecessary database overhead but the complexity of the Account form when users only need to fill-in 25 or so fields for one of the records types but need to populate up to 300 fields for other account types. That leads to a lot of confusion about what data is required in the UI. Building reports, too, becomes much more difficult with so many attributes.
Here's the T-SQL script I created for this attribute utilization analysis. Again, this will help you determine what percentage of each attribute in the Account entity has a non-null value. Of course, you can tweak this code to analyze any other entity or even all entities and attributes!
set nocount on
declare @table varchar(255)
declare @col varchar(255)
declare @datatype varchar(255)
declare @sql varchar(1000)
declare @total_recs int
declare @total_non_null_recs int
declare cur cursor for
select table_name, column_name, data_type
from information_schema.columns
where table_name = 'FilteredAccount' -- tweak this
order by [table_name], [ordinal_position]
open cur
fetch next from cur into @table, @col, @datatype
while (@@fetch_status > -1)
begin
set @sql = 'declare @total_recs float; select @total_recs = count(*) from ' + @table + ';'
set @sql = @sql + 'declare @total_non_null_recs float;'
set @sql = @sql + 'select @total_non_null_recs = count(*) from ' + @table + ' where [' + @col + '] is not null;'
set @sql = @sql + 'declare @percent_data_availability int;if @total_recs > 0 begin select @percent_data_availability = round((@total_non_null_recs / @total_recs) * 100, 0) end else begin select @percent_data_availability = 0 end;'
set @sql = @sql + 'print ' + '''' + @table + ',' + @col + ',' + '''' + ' + convert(varchar(10),@percent_data_availability)'
execute (@sql)
fetch next from cur into @table, @col, @datatype
end
close cur
deallocate cur
The output of this script appears, in SQL Server Management Studio, on the Messages tab in the query results area of the query window.
The ouput includes the table name, attribute name, and an integer representing the percentage of records where the attribute has a non-null value. For example, the output below means that 78% of the address1_city attributes in all records in the FilteredAccount view has a value:
FilteredAccount,address1_city,78
In conclusion, you can use this script or something like it to quickly determine which entities and attributes in your CRM system has a value. If you see a low percentage for an attribute that you think should be much higher then that will likely lead you to take action, such as requiring values in the UI or perhaps removing the attribute altogether.
On one of the upgrade projects, the client's previous IT staff built-up the Account entity to include about 300 attributes. Now, I'm not one to quickly judge this as a bad idea without getting the facts and rationale for having so many custom attributes. Although I wouldn't have designed the entity the same way (I would've used a few picklists to replace the 100+ bit fields) the current design was built, was working, and it wasn't in the budget to change that part of the system.
But the question I had was this, "What percentage of each Account attribute has a value (is not null)?". Knowing the answer to this, and knowing what types of clients, partners, suppliers, etc. were stored in the Account entity, might lead to some decisions on whether all of the attributes are really necessary or whether a particular company type might be best spun-off to a new entity.
At the bottom of this posting I provide the T-SQL script I put together to answer the "percentage of attribute utilization" question.
After running the script, I found one classification of companies that accounted for 80% of the 150,000 Account records. But that company type only stored data in 10% of the 300 attributes! In other words, there were approximately 35,000,000 fields in SQL Server with NULL values for those Account records!
Conversely, the other types of companies stored in the Account entity utilized 90% of all account attributes. So, most account attributes were being populated, but not for a very large percentage of account records.The problem with having unpopulated attributes is not so much the unnecessary database overhead but the complexity of the Account form when users only need to fill-in 25 or so fields for one of the records types but need to populate up to 300 fields for other account types. That leads to a lot of confusion about what data is required in the UI. Building reports, too, becomes much more difficult with so many attributes.
Here's the T-SQL script I created for this attribute utilization analysis. Again, this will help you determine what percentage of each attribute in the Account entity has a non-null value. Of course, you can tweak this code to analyze any other entity or even all entities and attributes!
set nocount on
declare @table varchar(255)
declare @col varchar(255)
declare @datatype varchar(255)
declare @sql varchar(1000)
declare @total_recs int
declare @total_non_null_recs int
declare cur cursor for
select table_name, column_name, data_type
from information_schema.columns
where table_name = 'FilteredAccount' -- tweak this
order by [table_name], [ordinal_position]
open cur
fetch next from cur into @table, @col, @datatype
while (@@fetch_status > -1)
begin
set @sql = 'declare @total_recs float; select @total_recs = count(*) from ' + @table + ';'
set @sql = @sql + 'declare @total_non_null_recs float;'
set @sql = @sql + 'select @total_non_null_recs = count(*) from ' + @table + ' where [' + @col + '] is not null;'
set @sql = @sql + 'declare @percent_data_availability int;if @total_recs > 0 begin select @percent_data_availability = round((@total_non_null_recs / @total_recs) * 100, 0) end else begin select @percent_data_availability = 0 end;'
set @sql = @sql + 'print ' + '''' + @table + ',' + @col + ',' + '''' + ' + convert(varchar(10),@percent_data_availability)'
execute (@sql)
fetch next from cur into @table, @col, @datatype
end
close cur
deallocate cur
The output of this script appears, in SQL Server Management Studio, on the Messages tab in the query results area of the query window.
The ouput includes the table name, attribute name, and an integer representing the percentage of records where the attribute has a non-null value. For example, the output below means that 78% of the address1_city attributes in all records in the FilteredAccount view has a value:
FilteredAccount,address1_city,78
In conclusion, you can use this script or something like it to quickly determine which entities and attributes in your CRM system has a value. If you see a low percentage for an attribute that you think should be much higher then that will likely lead you to take action, such as requiring values in the UI or perhaps removing the attribute altogether.
Tuesday, July 8, 2008
When a system problem occurs, start here...
Since I mostly deal with the technical side of Microsoft Dynamics CRM 4.0 implementations, including planning, deployment, development (JScript, plug-ins, workflow .NET assemblies, systems integration, etc.), I am often the person to whom system problems get escalated. One of the practices that I've found beneficial is to save all MSCRM information I can get my hands on including product documentation, KB articles, blogs, code samples, newsgroups, etc. and
index all of it with dtSearch. With a product as widely sold and used as CRM 4.0, there's likely a solution (or a solid clue) out there in the cloud and having that information indexed into a single source makes for rapid problem resolution.
Another practice I've followed is being consistent with how I record and track system problems. By recording the precise problem and/or error message(s), the status of the issue, the fixes attempted, screenshots, etc. the result is typically an organized and thoughtful approach to system diagnosis and resolution. The side benefit is that I then have documentation that may help me or others in the future. Of course, if the client I'm working with has a defect tracking system then that's where this information will go, but it's good to have a separate copy, too, so that the information is easier to locate in the future.
Below is the basic template I use when faced with a system issue. After resolving the issue, I update my personal kb in dtSearch and if I ever face the same problem in the future the solution will be easy to find.
[Client] - [Project] - Issue - [Brief Problem Description]
Date Reported: [Date]
Reported By: [Person who identified the problem]
Owner: [Tim Dutcher]
Priority: [One of the following: Urgent, High, Medium, Low]
Status: [One of the following: Open, In Progress, Resolved, Reopened, Closed]
Environment: [One of the following: Dev, UAT, Production]
Keywords: [Enter keywords that will help find this document via full-text search.]
Other details to record at the top might include: Requirement # or name, total time invested, and due date.
Table of Contents
Insert TOC here.
Issue Description
Enter full description of the issue here.
Error messages
Includes errors logged to Windows Event Log, CRM log files, IIS log, Custom log
Screenshots
How to Reproduce
Preconditions
Steps
Resolution Actions and Results
Note: Before taking any actions to resolve the issue, make backups of the CRM customizations and system files (server\bin, ISV, and wwwroot), config files, databases, and other files/settings as necessary.
[Date/Time] - [Person's initials] - Action taken and results. Also list open questions (highlight in red).
Check the following resources for information. If helpful information is found then record the URL and copy the key points from the content.
- Online search
- Dynamics CRM 4.0 Implementation Guide
- Dynamics CRM newsgroups
- Dynamics CRM SDK
- Local knowledge base (indexed documents, e-mail, web content, etc.)
- Windows Event Log
- Dynamics CRM log files (\Program Files\Microsoft CRM\Trace\)
- IIS log files (\Windows\System32\Logs\)
- Fiddler
- SQL Profiler
- SysInternals Process Monitor
- CRMDiagTool4
Resolution Options
List the pros and cons of each resolution option.
Final Resolution
Provide thorough, step-by-step details to resolve the problem. List the name of all files changed in source control.
Other Details
- If debugging code, capture details of classes visited, variables, location of exception(s), commentary about code (possible areas of fault). Also record areas where additional logging would be beneficial.
Subscribe to:
Posts (Atom)