Using Microsoft CRM’s Filtered views with Microsoft Access


With Microsoft Dynamics CRM 3.0 a lot of work was done to allow users to get at the data captured by CRM in different ways. One of the key features in surfacing this data is the Filtered views. Filtered views expose a read-only view of the entities with in the CRM system. These views do all of the work to ensure security is properly applied and hide the complexity of the entities.


 


Now any software that can work with SQL Server views can work against the logical entities in your CRM system. CRM uses these filtered views for integration with SQL Server Reporting service and exporting to Microsoft Excel to dynamic lists.


 


Another Office application that can be used with the filtered views is Microsoft Access. Microsoft Access can be used to quickly proto-type reports or doing adhoc queries. However there are some attributes of filtered views that must be taken into account when working against filtered views in Microsoft Access which I will address below.


 


Why does Access ask about the unique identifier?


There are two reasons that Access wants to know what column is the unique identifier (Primary Key). First, this column is used to determine if the linked table can be updated, which is irrelevant when working with filtered views. Second, this column is used by Access top optimize the retrieval of data and query execution. If possible Access will try send as much of the query to the backend server; this optimization depends on knowing the unique identity of each table row. Additionally, the query designer uses this as a hint about how tables should be joined in the query, although this can always be changed.


 


How do I handle un-used columns?


One of the first things I found difficult when working with the filtered views is that they include every column for an entity even those that are not used on forms. For the out of box entities there can be quite a few un-used columns. To handle this I create an Access query for that only includes the columns I need or that I expect my users to need.


 


Why are there two Date columns?


Since CRM is designed to work in an environment where there may be users in different time zones working on the application all dates in the system are stored in Coordinated Universal Time (UTC) and displayed in the application in the user’s local time zone at render time. When building queries against filtered views and filtering on a date column always use the UTC column! This will make sure that SQL server can used indexes and raw data to execute the query. If you ever filter against the display date column SQL Server will have load every row of data into memory and then make a function call to generate the display date. When the CRM team was designing the reports for 3.0 one of the biggest performance issues were caused by filtering against the display date.


 


When to use the Lookup ID column versus the Lookup name column?


Lookup ID’s are much like the UTC date columns, in that when you are building a query between two filtered views you want to join the ID columns rather than the name column. Not only will this make a more efficient query, but names in CRM are not necessary unique.


Rich Dickinson

Comments (0)

Skip to main content