Filtered Views and Picklist Labels

When you do a SQL query of the CRM database, such as for a report, the recommended, supported way to query is using the Filtered Views.

By default, these are the only database views to which CRM users have access.  The filtered views have several benefits:

1.  They support Dynamics CRM security.  When running a query of the filteredaccounts view, for example, users will only be able to see the records that they have permission to view.

2.  They retrieve the metadata for CRM picklist labels, which will make your job much easier than the alternative, working with the stringmap table.

When writing reports for CRM, it is important to use the filtered views whenever possible to avoid giving crm users access to data that they shouldn’t have.  For more details about the filtered views, recommended reading is one of the first posts on the CRM Team blog, Why Filtered Views Are Cool.

Considerations

I had an interesting experience recently where we added a user to an environment to be a report writer.  We added this user to CRM and gave her a security role.  She could query the filtered views; however, when she got the results of her query, all of the picklist names were null.

I tested the other users, and they could query the filtered views and retrieve the picklist names.

I knew that she had never logged in to CRM yet, so on a whim, I asked her to log in to CRM.  After that she tried again, and this time, the query retrieved the picklist names.

Turns out that  when you create a user in CRM, the UILanguageId in the usersettingsbase table is set to 0 for that user. When the user logs in the first time, CRM sets the uilanguageid based on the Internet Explorer language settings. This is for the case where you have users in another language that may not be able to read the base language to change their language settings. 

The picklist names you see are based on the language code the user has set.  If the uilanguageid isn't set then it could cause the picklist names not to appear.

Lesson learned:  Have users log in to CRM at least one time before they write queries/reports.

Technorati Tags: Dynamics CRM,reports,filtered views

Comments

[Donnelle]

You would not believe how much time you just saved me.