Retrieve Multiple - FetchXML with Alias

This is not a complex thing, but though of posting the code snippet since I found it important to explain a bit. When we need to retrieve data in related records few levels away from our current context, best method is using the FetchXML and use ReiveMultiple method. This will avoid us using many server calls to reach our entity.

Reason for writing this post is to explain one point. When we do complex FetchXMLs we definitely need to use Alias for linked entities. So reading the data in the code can be little different.

Check my example; I have an entity called job. Job got a lookup field to Project. Project got a lookup to User. Suppose we need to read the Mobile Number of the user (3 levels ahead) while we have only Job Id in hand, we use FetchXML as below;

internal static EntityCollection RetriveProjectAdminMobileByJobId(IOrganizationService service, Guid Id)
{
var fetchXml = string.Format(@"<fetch mapping='logical' output-format='xml-platform' version='1.0' distinct='false'>  
               <entity name='new_job'>
               <filter type='and'>
               <condition attribute='new_jobid' operator='eq' value='{0}' />
               </filter>
                   <link-entity name='new_project' to='new_jobprojectid' from='new_projectid' alias='PROJ' link-type='outer' visible='false'>
                       <link-entity name='systemuser' to='new_projectadmin' from='systemuserid' link-type='outer' alias ='PROJADMIN'>
                            <attribute name='mobilephone' />
                        </link-entity>
                   </link-entity>
               </entity>
               </fetch>", Id);
            return service.RetrieveMultiple(new FetchExpression(fetchXml));

Please notice, how I have used Aliases meaningfully. Now what we need to keep in mind is resulting fields would come with that Alias. In fact, mobile phone field is like PROJADMIN.mobilephone. Now check how I read it in C#;

var entityCollection = RetriveProjectAdminMobileByJobId(service, workOrderRef.Id);

if (entityCollection == null || entityCollection.Entities == null || entityCollection.Entities.Count <= 0)
    return;

if (entityCollection.Entities[0].Attributes.Contains("PROJADMIN.mobilephone"))
    string AdminMobile = (string)((AliasedValue)entityCollection.Entities[0].Attributes["PROJADMIN.mobilephone"]).Value;

I advice always try the FetchXmls before using. Best tool to do so is FetchXM Tester of XrmToolBox.

There is a one pitfall. I have seen some tools which are not returning the correct field names as expected. For example, I have tested same FetxhXML in DataSet creating tool in SSRS report authering extensions in VS2012 that returned like PROJADMIN-mobilephone, which is WRONG.

Code snippet to add Lookup filter

This is just a simple JavaScript to filter lookup based on existing field value in the form.

In this scenario, we will assume we needs to set Payment Method to a lookup field (i.e. new_paymentmethodid). Its user-friendly if we can filter the lookup values to show only the payments of contact (i.e. new_contactid). This code snippet will work for this.

PaymentMethodFilter = function () {
    Xrm.Page.getControl("new_paymentmethodid").addPreSearch(addPaymentMethodFilter);
}

addPaymentMethodFilter = function () {
    var contact = Xrm.Page.getAttribute('new_contactid');
    if ((contact == null) || (contact.getValue() == null) || (contact.getValue()[0] == undefined)) return;
    var paymentMethodFilter = "<filter type='and'><condition attribute='new_paymentowner' uiname='" + contact.getValue()[0].name + "' operator='eq' uitype='contact' value='" + contact.getValue()[0].id + "' /></filter>";
    Xrm.Page.getControl("new_paymentmethodid").addCustomFilter(paymentMethodFilter, "new_paymentmethod");
}

Now call PaymentMethodFilter method for onChange of new_contactid field.