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=&quot;true&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(&quot;Entity&quot;)
from c in e.Elements(&quot;FormXml&quot;).Elements(&quot;forms&quot;).Elements(&quot;systemform&quot;).Elements(&quot;form&quot;)
.Elements(&quot;tabs&quot;).Elements(&quot;tab&quot;)
.Elements(&quot;columns&quot;).Elements(&quot;column&quot;)
.Elements(&quot;sections&quot;).Elements(&quot;section&quot;)
.Elements(&quot;rows&quot;).Elements(&quot;row&quot;)
.Elements(&quot;cell&quot;)
where (string)c.Attribute(&quot;showlabel&quot;) == &quot;true&quot; || c.Attribute(&quot;showlabel&quot;) == null
from l in c.Elements(&quot;labels&quot;)
let formName = e.Element(&quot;Name&quot;).Attribute(&quot;LocalizedName&quot;).Value
let fieldName = l.Element(&quot;label&quot;).Attribute(&quot;description&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, Export, LINQ, Linq to XML, ms crm, ms crm 2011, Query, Solution, xquery
