Wednesday, January 25, 2012

SELECT DISTINCT Views in Dynamics CRM

Hi everyone, I've had fun promoting the book etc, but now it's time to get into some good postings for 2012!

I'd like to start off with a unique request I had from a team member to create a view that behaves like a SELECT DISTINCT clause in MS SQL syntax.



Why do they need a DISTINCT View?
In this case, we have Accounts with associated Contact records. These Contact records can be both active and inactive, and consist of duplicates. This client wants a simple view from the Account form where they can see the unique count of Contacts (active, deactive, duplicate... doesn't matter). Instead of building a custom aspx page to query and show distinct, uneditable rows, we can build a view.

Can I build this using Advanced Find or the View Designer?
No. Neither the Advanced Find tool or the View Designer offer a DISTINCT clause. We will need to create a custom view (unmanaged), export it as part of a solution, modify the FETCH XML and reimport.

Are there Limitations to a DISTINCT View?
Yes. When dealing with a View, the user expects that by double clicking a row they can edit a record, or perhaps select multiple records and perform an action. This is enabled because there is a unique record id for each row. When creating a DISTINCT view, we will need to exclude the unique record id in order for the distinct condition to work. This blocks all of the functionality that a user expects a view to contain.

Let's Get Started...
1. I started by creating a new Solution Package that contains only the Contact entity. I named the package Distinct_Fetch.

2. Create a custom view in Dynamics CRM using the View Designer. I created a view for the Contact entity and called it Distinct Contacts. I only showed the FirstName and LastName fields in the column layout.

3. Next, I saved the view. Published my changes and exported the Solution Package.

4. Uncompress the .zip file and open the customizations.xml file in an XML editor (I used notepad... hardcore!). Seach for your View under the <savedqueries> section of the customization XML. Hint: the name of the view is stored in the <localizedname> element's "description" attribute.

5. Review your existing XML, and make the following 3 changes:
   a) Clear "contactid" from the <row> elements "id" attribute. It should be blank.
   b) Set the "distinct" attribute to "true" in the <fetch> element.
   c) Remove the <attribute name="contactid"> node from the <entity> element's attribute collection.

In the end, your <savedquery> XML should look similar to this. I have highlighted the changes:
          <savedquery>
            <IsCustomizable>1</IsCustomizable>
            <CanBeDeleted>1</CanBeDeleted>
            <isquickfindquery>0</isquickfindquery>
            <isprivate>0</isprivate>
            <isdefault>0</isdefault>
            <returnedtypecode>2</returnedtypecode>
            <savedqueryid>{7eb10c92-c547-e111-86fb-78e3b5172af3}</savedqueryid>
            <layoutxml>
              <grid name="resultset" object="2" jump="fullname" select="1" preview="1" icon="1">
                <row name="result" id="">
                  <cell name="firstname" width="100" />
                  <cell name="lastname" width="100" />

                </row>
              </grid>
            </layoutxml>
            <querytype>0</querytype>
            <fetchxml>
              <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
                <entity name="contact">
                  <order attribute="firstname" descending="false" />
                  <attribute name="lastname" />
                  <attribute name="firstname" />
<!-- removed the contactid attribute -->
                </entity>
              </fetch>
            </fetchxml>
            <LocalizedNames>
              <LocalizedName description="Distinct Contacts" languagecode="1033" />
            </LocalizedNames>
          </savedquery>


6. Save the customization.xml file, zip and import the solution back into Dynamics CRM. Make sure to publish the changes.

7. Assuming you already added 2 or more contacts with the same First and Last name, go to your new Distinct Contacts view and check the results.

This example showed how to create a DISCTINCT view using the Contact entity's FirstName and LastName fields, but this could be useful when looking up custom entities, perhaps contracts or reports that are duplicated, but the client is trying to get a clear picture of the unique values.

Cheers,