CRM: Extend Filtered Views

Question

We all know that crm has such a useful feature as filtered views. They are good doubtlessly, but their main drawback that they do not provide definitions for StateCode, StatusCode, etc. for OptionSets as they provide e.g. for LookUp fields.

I want to use a filtered view to share data with side app.

What is the best way to map StatusCode value with its text definition, which is saved in StringMap table?

Can I edit system view (for e.g. Account entity) to write additional JOIN statement to StringMap table, or maybe sql server provides something kind of encapsulating views, so I can create my own view that will use system filtered view for account and also will use my custom JOINs?

UPDATE: I found the problem. I did SELECT from Opportunity view, when actually I needed FilteredOpportunity.

But still interesting, can I modify system views in CRM DB? Or can I encapsulate them (e.g. use them in another view)?

Answer 1

Both values and labels for option sets are already included in filtered views. Labels are in the language of the systemuser executing the query.

The label can always be retrieved by appending Name to your option sets (and lookup fields).

As an example, you can retrieve the Status Code from Account as follows:

SELECT StatusCode, StatusCodeName FROM FilteredAccount

With that being said, you should consider using one of the webservices for accessing CRM-data for compatibility going forward.