Mitch Milam's Dynamics CRM and xRM Discussions

Some of my customers use Microsoft Excel or Access to pull data from the CRM system in order to produce custom reports, or in one instance, to interface with a third-party bulk-email application.

CRM 3.0 has a set of SQL Database Views, called Filtered Views, that are constructed in such a way that CRM Security roles are maintained and you can retrieve nothing using one of these views that you would not ordinarily see while using the CRM application.

One aspect of using filtered views that you must take into consideration is the fact that all data ( that you have access to ) will be retrieved from the specified Entity.  This means both Inactive and Active records.

If you don't take Inactive records into consideration your result set could be drastically different than what you expected and  you may inadvertently expend resources on inactive customers.

Luckily, CRM provides two data fields that can be used to filter your query:

So, here is how you would use the above:

--


-- Show only active Accounts


--


select * from FilteredAccount where statuscode = 1


 


--


-- Show only active Contacts


--


select * from FilteredContact where statuscode = 1


 

This code instructs SQL Server to return all rows where the statuscode is equal to 1, which is an Active Account or Contact.