User Query/Saved Query Views in CRM 2011

Problem:
I came across situation where i needed to grab all the user-defined private views for a particular entity within the organization.

Solution:

1. First of all there are two types of views, one that is defined by user that is user's private view and other is system defined or public views that is available to all users.
2. The user defined views are stored in "UserQuery" and the system defined views are stored in "SavedQuery" within database. 
3. To get the all user defined views for the specific entity you can execute following sql query against your database.   

             select Name as ViewName ,

             OwnerIdName as Owner,

             case statecode when 0 then 'Active'

                                        when 1 then 'Inactive'

             end as Status,

             UserQuery.Description

             from UserQuery

             where ReturnedTypeCode= #entitycode(integer)

             order by OwnerIdName, Name

         Here the ReturnTypeCode is the entity code that indicates the entity that is returned as result of the                view. So if you need to get all the user defined view for account entity then you must right the 

         entity code for account entity.

4. You can grab the system views by going into customizations. Say you want to get the system views for account entity then you can go to settings-> customization-> Entities-> Accounts-> Views.
5. However you can also get them from the database. You can execute following sql query against the database.

                select Name as ViewName ,

                SavedQuery.CreatedByName as  Owner,

                case statecode when 0 then 'Active'

                                           when 1 then 'Inactive'

                end as Status,

                SavedQuery.Description

                from SavedQuery

                where ReturnedTypeCode=10034

                order by CreatedByName, Name

Hope this helps,

Thanks

To create default custom view for a lookup field in CRM 2011 using Javascript.

Problem: I came across situation where I need to create a default custom view when the page is loaded for a lookup field. Along with that I also didn’t wanted to disable the view selector option so user cannot select any other view except my custom view.

 Solution:

To achieve this target I knew that we need a javascript that executes when the form is loaded.

1.      First step is to create a web resource that can hold this javascript. (click here learn how tocreate a web resource)

2.      Once your web resource is ready we need to form the fetchxml query that grabs the required data based on our requirement.

3.      So you can simply go to advanced finds and form the query logic to grab your data. Suppose you want all grab all opportunities linked with specific account for the enity. (This is pretty simple task custom views are used for more complex task).

4.      Once the query is form hit the “Download fetchxml” button on top ribbon in Advanced Finds. This will give you a fetchxml of the query. You need to copy this in your javascript code.

5.      Next step is to form the layoutxml , this determines which columns from the entity is display in your lookup result. Finally your code will look like this.

function LoadCustomView()
{
  //get the guid of account
  var acctId = Xrm.Page.getAttribute("lookupfieldnameforAccount").getValue()[0].id;

  //Fetch xml from the advanced find. Need to format this
  var fetchxml=
"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
  "<entity name='opportunity'>" +
     "<attribute name='name' />" +
     "<attribute name='customerid' />" +
     "<attribute name='opportunityid' />" +
     "<order attribute='customerid' descending='false' />" +
     "<link-entity name='account' from='accountid' to='customerid' alias='aa'>" +
     "<filter type='and'>" +
      "<condition attribute='accountnumber' operator='eq' value='" + acctId + "' />" +
     "</filter>"+
   "</link-entity>" +
  "</entity>" +
"</fetch>"

//Layout xml shows that row and column structure in view
var layoutXml =
"<grid name='opportunities' object='10017' jump='name' select='1' icon='1' preview='1'>" +
   "<row name='opportunity' id='opportunityid'>" +
          "<cell name='name' width='200' />" +
          "<cell name='customerid' width='200' />" +
     "</row>" +
 "</grid>";


var viewId = "{00000000-0000-0000-0000-000000000000}"; //make sure this is guid
var entityName = "opportunity";//name of entity for which we are defining the custom view
var viewDisplayName = "Any name you want to show up"; //Name that show up in view selector

//pass all parameters to function and last value true makes this view as default view.

Xrm.Page.getControl('lookupfieldnameforopp').addCustomView
       (viewId, entityName, viewDisplayName, fetchXml, layoutXml, true); 
}

·       So first I grab the account id and use that in Fetchxml to grab all opportunities associated with 
       that account.
·         Make sure this lookup field is set properly on form. The view selector should show "show all 
       views".

6.      Now you want to disable the view selector panel so that user cannot select any other view that except the default custom view you provide. For that you need to add following line after your addcustomview line.

                       document.getElementById("lookfieldforopp").setAttribute("disableViewPicker","1");

7.      Now you can save and publish the web resource and check the out your view by opening up the page.


Hope this helps,
Thanks