Archive for January, 2012

Query MS CRM XML Entity with XQuery and LINQ

In our company we have been working on a large MSCRM 2011 project. Last week, I was asked to create a report listing all the visible fields for each entity in the solution. During my search, I found a few different ways to do it, but the most fun way was querying the XML.
The XML is created when the solution is exported. It’s normally several levels deep and even when the structure would depend on the solution the XML listed below is an example of how it would looks like.

<Entities>
    <Entity>
      <Name LocalizedName="Account" OriginalName="Account">Account</Name>
      <ObjectTypeCode>1</ObjectTypeCode>
      <EntityInfo>
        <entity Name="Account">
          <LocalizedNames>
            <LocalizedName description="Account" languagecode="1033" />
          </LocalizedNames>
          <LocalizedCollectionNames>
            <LocalizedCollectionName description="Accounts" languagecode="1033" />
          </LocalizedCollectionNames>
          <Descriptions>
            <Description description="Business." languagecode="1033" />
          </Descriptions>
   <attributes>
		<attribute PhysicalName="Category">
			…
	<attribute>
   </attributes>
</entity>
      </EntityInfo>
   <FormXml>
        <forms type="main">
          <systemform>
            <formid>{b053a39a}</formid>
            <form>
              <hiddencontrols>
                <data id="addressid" datafieldname="addressid" classid="{}" />
                <data id="address2” datafieldname="address2" classid="{}" />
              </hiddencontrols>
		  <tabs>
                <tab name="general" verticallayout="true" id=”" IsUserDefined="0">
                  <labels>
                    <label description="General" languagecode="1033" />
                  </labels>
                  <columns>
                    <column width="100%">
<sections>
  <section name="account information" showlabel="false" showbar="false" id="{}" IsUserDefined="0">
                          <labels>
                            <label description="Information" languagecode="1033" />
                          </labels>
                          <rows>
                            <row>
                              <cell id="{}">
                                <labels>
                                  <label description="Name" languagecode="1033" />
                                </labels>
                                <control id="name" classid="{}" datafieldname="name" />
                              </cell>
                              <cell id="{}">
                                <labels>
                                  <label description="Phone" languagecode="1033" />
                                </labels>
                                <control id="telephone1" classid="{}" datafieldname="telephone1" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{}" showlabel="false" >
                                <labels>
                                  <label description="Contact" languagecode="1033" />
                                </labels>
                                <control id="primarycontactid" classid="{}" datafieldname="primarycontactid">
                                  <parameters>
                                    <FilterRelationshipName>customer_accounts</FilterRelationshipName>
                                    <DependentAttributeName>parentcustomerid</DependentAttributeName>
                                    <DependentAttributeType>account</DependentAttributeType>
                                    <AllowFilterOff>true</AllowFilterOff>
                                  </parameters>
                                </control>
                              </cell>
                              <cell id="{}" showlabel="true" >
                                <labels>
                                  <label description="Other Phone" languagecode="1033" />
                                </labels>
                                <control id="telepho" classid="{}" datafieldname="telep" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{}">
                                <labels>
                                  <label description="Parent" languagecode="1033" />
                                </labels>
                                <events>
                                  <event name="setparams" application="true" active="true">
                                    <InternalHandlers>
                                      <Handler handlerUniqueId=" " enabled="true" />
                                    </InternalHandlers>
                                  </event>
                                </events>
                                <control id="parentaccountid" classid="{}" datafieldname="parentaccountid" />
                              </cell>
                              <cell id="{ }">
                                <labels>
                                  <label description="Fax" languagecode="1033" />
                                </labels>
                                <control id="fax" classid="{}" datafieldname="fax" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{ }">
                                <labels>
                                  <label description="E-mail" languagecode="1033" />
                                </labels>
                                <control id="emailadd" classid="{}" datafieldname="emailadd" />
                              </cell>
                              <cell id="{ }">
                                <labels>
                                  <label description="Web Site" languagecode="1033" />
                                </labels>
                                <control id="websiteurl" classid="{}" datafieldname="websiteurl" />
                              </cell>
                            </row>
                          </rows>
                        </section>
             </sections>
                    </column>
                  </columns>
                </tab>
</tabs>
</form>
     </systemform>
        </forms>
      </FormXml>
    </Entity>
</Entities>


'

Take into consideration that we have several entities and those entities have multiple tabs, sections, controls and labels. The listing above is just an example of what MS CRM exports in XML format. It shouldn’t be consider as a real entity. Having said that, lets continue with the real fun.

My task was to find all the visible and enabled fields in the solution. I also needed to know, if the fields belong to the Account entity or any other entity and I just needed to retrieve the fields from the Main form not the Mobile form.

Before we go further, is important to say that I had to retrieve the Label description, but I had to check the cell element, “Showlabel” attribute which controls the visibility as you can see on the next example.

<cell id="{}" showlabel="true" >
  <labels>
    <label description="Other Phone" languagecode="1033" />
  </labels>
  <control id="telepho" classid="{}" datafieldname="telep" />
 </cell>


'

Like I said before, there are different ways to do this. Here are a couple examples.
The first I used was XQuery and FLWOR expressions.

Basically it searches through the XML verifies if the “showlabel” attribute is either not there or set to true and retrieves the names.


SELECT ent.vals.value('@OriginalName', 'varchar(max)') AS Entity, ent.vals.value('@datafieldname', 'varchar(max)') FieldName
FROM @xml.nodes('for $en in //Entity
return ($en/Name,(for $q in ($en/FormXml/forms/systemform/form/tabs/tab/columns/column/sections/section/rows/row/cell)
where (empty($q/@showlabel) or ($q/@showlabel=&amp;quot;true&amp;quot;))
return $q/control)) ') ent(vals)

'

It returns the Entity name in one column follow by the Fields. It looks like this:

Another way to query is using Linq to XML. The statement looks like this:


var xrmEntities =   from e in doc.Elements(&amp;quot;Entity&amp;quot;)
                        from c in e.Elements(&amp;quot;FormXml&amp;quot;).Elements(&amp;quot;forms&amp;quot;).Elements(&amp;quot;systemform&amp;quot;).Elements(&amp;quot;form&amp;quot;)
                                    .Elements(&amp;quot;tabs&amp;quot;).Elements(&amp;quot;tab&amp;quot;)
                                    .Elements(&amp;quot;columns&amp;quot;).Elements(&amp;quot;column&amp;quot;)
                                    .Elements(&amp;quot;sections&amp;quot;).Elements(&amp;quot;section&amp;quot;)
                                   .Elements(&amp;quot;rows&amp;quot;).Elements(&amp;quot;row&amp;quot;)
                                    .Elements(&amp;quot;cell&amp;quot;)
                        where (string)c.Attribute(&amp;quot;showlabel&amp;quot;) == &amp;quot;true&amp;quot; || c.Attribute(&amp;quot;showlabel&amp;quot;) == null
                        from l in c.Elements(&amp;quot;labels&amp;quot;)
                        let formName = e.Element(&amp;quot;Name&amp;quot;).Attribute(&amp;quot;LocalizedName&amp;quot;).Value
                        let fieldName = l.Element(&amp;quot;label&amp;quot;).Attribute(&amp;quot;description&amp;quot;).Value
                        orderby formName, fieldName
                        select new {Entity = formName, Field = fieldName };
    xrmEntities.Count().Dump();
    xrmEntities.Dump();

//”doc”  variable loads the XML file.

The result returned by Linq is probably easier to understand. It looks like this.

Either way returns the Fields Names so, It’s up to you to decide what to use Linq or XQuery. Both of them are good things to learn as a developer.

Tags: , , , , , , , ,


CRM 2011 – Hiding a Form’s Header and Footer

While Microsoft provides a button to minimize a form’s header, we had to fulfill a requirement to automatically minimize the header and completely hide the footer for several entities. The reasoning behind this was that most users weren’t going to be making changes to these records, so the client wanted to provide the maximum amount of viewable space. For instance, the Account form normally displays like this, but we wanted to reclaim at least that 90px of height from the ribbon:

Account Form

As it turned out, all it took was a couple simple JavaScript functions called in the form’s OnLoad event to make this happen. When looking into how to hide the ribbon, I came across this article, which lists code to hide different elements on the form and contained discussion regarding the ribbon’s minimize functionality and how to leverage it during the form’s OnLoad event. Hiding the ribbon was just a matter of firing the “onclick” event for the “minimizeribbon” button after it had rendered:

function Hide_Ribbon() 
{
  setTimeout(function () { window.top.document.getElementById(&amp;quot;minimizeribbon&amp;quot;).fireEvent(&amp;quot;onclick&amp;quot;); }, 0);
}


And hiding the footer was even easier, since you could just set a “display: none;” for the footer’s parent:

function Hide_Footer()
{
  document.getElementById(&amp;quot;crmFormFooter&amp;quot;).parentElement.style.display = &amp;quot;none&amp;quot;;
}


The result looks something like this:

Account Form - No Header or Footer

The biggest downside to this approach, though, is that the header and footer will momentarily display before the JavaScript kicks in and hides them. Still, if your users don’t need to use the ribbon on a regular basis and are starved for screen space, this is a quick and easy way to give them a little more room.

Tags: , , , ,