Building FetchXml Dynamically

The FetchXml query language plays an important role in CRM client side javascript development.
We use it to query information from CRM, add custom lookup views and present custom views in our iframes and subgrids.

Here is an example of using FetchXml while building a FetchViewer to display custom grid inside an iframe. As you can see combining the query in such a fashion is cluttering and hard to understand.

Note: for a complete usage of FetchViewer click here.

function LoadAccounts() {
window.fetchAccounts = new FetchViewer("IFRAME_accounts");
   fetchAccounts.FetchXml  = getFetchXml();
   fetchAccounts.LayoutXml = getLayoutXml();
   fetchAccounts.Entity    = "account";
   fetchAccounts.QueryId   = "{00000000-0000-0000-00AA-000010001001}";
   fetchAccounts.RegisterOnTab(0); 
}

function getFetchXml() {
   return '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">' + 
          '<entity name="account"><attribute name="name"/><attribute name="address1_city"/>' + 
          '<attribute name="primarycontactid"/><attribute name="telephone1"/>' +
          '<attribute name="accountid"/><order attribute="name" descending="false"/>' + 
          '<filter type="and"><condition attribute="ownerid" operator="eq-userid"/>' +
          '<condition attribute="statecode" operator="eq" value="0"/></filter>' +
          '<link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="accountprimarycontactidcontactcontactid">' +
          '<attribute name="emailaddress1"/></link-entity></entity></fetch>';
}

function getLayoutXml(){
   return '<grid name="resultset" object="1" jump="name" select="1" icon="1" preview="1">' + 
          '<row name="result" id="accountid"><cell name="name" width="300" />' + 
          '<cell name="telephone1" width="100" /><cell name="address1_city" width="100" />' +
          '<cell name="primarycontactid" width="150" />'+
          '<cell name="accountprimarycontactidcontactcontactid.emailaddress1" width="150" disableSorting="1" />' + 
          '</row></grid>';
}

I personally don’t like the idea of integrating Xml into javascript code and find it much more appealing to write queries on the fly. IMO, writing FetchXml directly, especially for this type of task, has obvious advantages, such as:
1. Code stays clean and minified containing only the important parts of the query,
making the query simple to read and understand.
2. Ability to write any type of query supported by the language and not being restricted by Advanced find designer limitation
i.e. using all type of operators, creating aggregate and groupby queries and so forth.

Now, let’s change the example above and use Dynamic FetchXml Builder

function LoadAccounts() {

window.fetchAccounts = new FetchViewer("IFRAME_test1");
   fetchAccounts.FetchXml  = getFetchXml();
   fetchAccounts.LayoutXml = getLayoutXml();
   fetchAccounts.Entity    = "account";
   fetchAccounts.QueryId   = "{00000000-0000-0000-00AA-000010001001}";
   fetchAccounts.RegisterOnTab(0); 
}

function getFetchXml(){
   var fetchXml = 
    
    fetch(true) //true - distinct
      .entity("account") 
          .attributes("name","address1_city", "primarycontactid" , "telephone1", "accountid")
          .order("name", fetch.order.Asc)
          .filter()
              .condition("ownerid", fetch.op.Current_User)
              .condition("statecode", fetch.op.Equal, 0)
          .link ({
              entityName : "contact",
              to: "primarycontactid",
              from: "contactid",
              type: fetch.link.outer,
              alias: "accountprimarycontactidcontactcontactid"
          })
            .attribute("emailaddress1");
            
    return fetchXml.toString();
}

function getLayoutXml(){
   retrun layout("1", "name", "accountid")
               .column("name", 300)
               .column("telephone1", 100)
               .column("address1_city", 100)
               .column("primarycontactid", 150)
               .column("accountprimarycontactidcontactcontactid.emailaddress1", 150)
               toString();
}

To make my wish come to live I’ve crafted the following library.
It enables you to simply and effectively create FetchXml and LayoutXml without leaving the IDE.
If you wish to integrate it in your code simply create a new web resource in CRM and add it to your js.

Here is the library (to select all the code: press double click and ctrl + c)

function fetchBase(pub, prv, docElem) {
    pub.toString = function () {
        return prv.serialize(docElem);
    }

    prv.serialize = function (xmlNode) {
        if (typeof (window.XMLSerializer) !== "undefined") {
            return (new window.XMLSerializer()).serializeToString(xmlNode);
        }
        else if (typeof (xmlNode.xml) !== "undefined") {
            return xmlNode.xml;
        }
        return "";
    }
}

function layout(otc, jumpAttr, primaryKey) {
    var l = {}, docElem, row;
    var xmlDoc = (function (sXml) { if (window.DOMParser) { parser = new DOMParser(); return parser.parseFromString(sXml, "text/xml"); } else { xmlDoc = new ActiveXObject("Microsoft.XMLDOM"); xmlDoc.async = false; xmlDoc.loadXML(sXml); xmlDoc = xmlDoc.firstChild; return xmlDoc; } })
    ('<grid name="resultset" object="0" jump="" select="1" icon="1" preview="1"/>');

    l.column = function (attr, width) {
        var col = xmlDoc.createElement("cell");
        col.setAttribute("name", attr);
        col.setAttribute("width", width);
        row.appendChild(col);
        return l;
    }

    var private = {
        init: function () {
            docElem = xmlDoc.documentElement;
            docElem.setAttribute("object", otc);
            docElem.setAttribute("jump", jumpAttr);

            row = xmlDoc.createElement("row");
            row.setAttribute("name", "result");
            row.setAttribute("id", primaryKey);
            docElem.appendChild(row);
            fetchBase(l, private, docElem);
        }
    }

    private.init();
    return l;
}

function fetch(o) {
    var f = {}, docElem, entElem, cur, iAlias = 0

    var filters = { flt: 0, flt2: 0, flt3: 0, flt4: 0, flt5: 0 }
    var links = { lnk: 0, lnk2: 0, lnk3: 0, lnk4: 0 }

    var xmlDoc = (function (sXml) { if (window.DOMParser) { parser = new DOMParser(); return parser.parseFromString(sXml, "text/xml"); } else { xmlDoc = new ActiveXObject("Microsoft.XMLDOM"); xmlDoc.async = false; xmlDoc.loadXML(sXml); xmlDoc = xmlDoc.firstChild; return xmlDoc; } })
    ('<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"/>');

    f.entity = function (name) {
        if (docElem.childNodes.length) throw "entity node already exists";
        if (!name) throw "missing entity name";
        cur = entElem = xmlDoc.createElement("entity");
        cur.setAttribute("name", name);
        docElem.appendChild(cur);
        return f;
    }

    f.count = function (attributeName, alias) { return private.aggregate(attributeName, alias, "count"); }
    f.avg = function (attributeName, alias) { return private.aggregate(attributeName, alias, "avg"); }
    f.max = function (attributeName, alias) { return private.aggregate(attributeName, alias, "max"); }
    f.min = function (attributeName, alias) { return private.aggregate(attributeName, alias, "min"); }
    f.sum = function (attributeName, alias) { return private.aggregate(attributeName, alias, "sum"); }
    f.countcolumn = function (attributeName, alias, distinct) {
        if (typeof (alias) === "boolean") {
            distinct = alias;
            alias = null;
        }
        return private.aggregate(attributeName, alias, "countcolumn", distinct);
    }

    f.order = function (attr, ord) {
        if (!entElem) throw "missing entity element";
        if (!attr) throw "attribute name is missing";
        var o = xmlDoc.createElement("order");
        o.setAttribute("attribute", attr);
        o.setAttribute("descending", (ord || "asc") === "desc");
        entElem.appendChild(o);
        return f;
    }

    f.all = f.attribute = function (name) {
        private.attribute(name);
        return f;
    }

    f.attributes = function () {
        var args = Array.isArray(arguments[0]) ? arguments[0] : Array.prototype.slice.call(arguments, 0);
        for (var i = 0; i < args.length; i++)
            private.attribute(args[i]);
        return f;
    }

    f.filter = function (type)  { return private.filter(type, "flt", entElem); }
    f.filter2 = function (type) { return private.filter(type, "flt2", filters.flt); }
    f.filter3 = function (type) { return private.filter(type, "flt3", filters.flt2); }
    f.filter4 = function (type) { return private.filter(type, "flt4", filters.flt3); }
    f.filter5 = function (type) { return private.filter(type, "flt5", filters.flt4); }

    f.condition = function (attr, operator, value) {
        if (!attr) throw "condition attribute is missing";
        if (!operator) throw "condition operator is missing";
        var c = xmlDoc.createElement("condition");
        cur.appendChild(c);
        c.setAttribute("attribute", attr);
        c.setAttribute("operator", operator);
        if (typeof (value) !== "undefined") {
            if (operator !== "in" && !Array.isArray(value)) {
                c.setAttribute("value", getValue(value));
            }
            else {
                c.setAttribute("operator", fetch.op.In);
                for (var i = 0; i < value.length; i++) {
                    var v = xmlDoc.createElement("value");
                    var t = xmlDoc.createTextNode(getValue(value[i]));
                    c.appendChild(v);
                    v.appendChild(t);
                }
            }
        }

        function getValue(value) {
            if (!value.IsXrmObject) 
                return value;
            if (value.IsLookup())
                return value.GetValueId();
            else if (value.IsDateObject)
                return value.ToFormat("yyyy-MM-dd");
            return value.Get();
        }

        return f;
    }

    f.groupBy = function (attrName, alias, dateGrouping, bUserTimezone) {
        var a, args = arguments[0], argLen = arguments.length;

        if (typeof (arguments[0]) !== "object") {
            return f.groupBy({
                userTimezone: argLen != 4 ? undefined : bUserTimezone,
                dateGrouping: private.isDateGrouping(alias) ? alias : dateGrouping,
                alias: argLen != 4 ? attrName : alias,
                attrName: attrName
            });
        }

        if (!args.attrName) throw "group by attribute is missing";

        a = private.attribute(args.attrName);
        args.alias && a.setAttribute("alias", args.alias);
        args.dateGrouping && a.setAttribute("dategrouping", args.dateGrouping);
        typeof (args.userTimezone) === "boolean" && a.setAttribute("usertimezone", !!args.userTimezone);
        a.setAttribute("groupby", true);
        return f;
    }

    f.link = function (entityName, to, alias, from, type, intersect) {
        return private.link(entityName, to, from, alias, "lnk", entElem, type, intersect);
    }
    f.link2 = function (entityName, to, alias, from, type, intersect) {
        return private.link(entityName, to, from, alias, "lnk2", links.lnk, type, intersect);
    }
    f.link3 = function (entityName, to, alias, from, type, intersect) {
        return private.link(entityName, to, from, alias, "lnk3", links.lnk2, type, intersect);
    }
    f.link4 = function (entityName, to, alias, from, type, intersect) {
        return private.link(entityName, to, from, alias, "lnk4", links.lnk3, type, intersect);
    }

    var private = {
        init: function () {
            docElem = xmlDoc.documentElement;
            switch (arguments.length) {
                case 1:
                    if (typeof (o) === "boolean" || typeof (o) === "undefined")
                        docElem.setAttribute("distinct", o);
                    else if (typeof (o) === "object") {
                        o.distinct && docElem.setAttribute("distinct", o.distinct);
                        o.output && docElem.setAttribute("output-format", o.output);
                        o.mapping && docElem.setAttribute("mapping", o.mapping);
                        o.version && docElem.setAttribute("version", o.version);
                        o.count && docElem.setAttribute("count", o.count);
                    }
                    else if (typeof (o) === "number") {
                        docElem.setAttribute("count", o);
                    }
                    break;
                case 2:
                    docElem.setAttribute("distinct", arguments[0]);
                    docElem.setAttribute("count", arguments[1]);
                    break;
            }
            fetchBase(f, private, docElem);
        },
        link: function (entityName, to, from, alias, li, par, type, intersect) {
            var args = arguments[0];
            if (typeof (args) === "object") {
                return private.link(args.entityName,
                args.to, args.from, args.alias, li, par, args.type, args.intersect);
            }

            if (!entityName) throw "missing link entity name";
            if (!to) throw "link entity to attribute is required";
            alias = alias || ("link" + (++iAlias));
            cur = links[li] = xmlDoc.createElement("link-entity");
            cur.setAttribute("name", entityName);
            from && cur.setAttribute("from", from);
            cur.setAttribute("to", to);
            cur.setAttribute("alias", alias);
            type && cur.setAttribute("link-type", type);
            intersect && cur.setAttribute("intersect", intersect);
            par.appendChild(cur);
            return f;
        },
        filter: function (type, fi, par) {
            type = type || "and";
            if (!par) throw "missing entity element";
            par = cur.nodeName === "entity" ? par : cur;
            cur = filters[fi] = xmlDoc.createElement("filter");
            cur.setAttribute("type", type);
            par.appendChild(cur);
            return f;
        },
        aggregate: function (attributeName, alias, type, distinct) {
            if (!cur) throw "missing entity element";
            if (!attributeName) throw "attribute name is missing"
            docElem.setAttribute("distinct", false);
            docElem.setAttribute("aggregate", true);
            var a = xmlDoc.createElement("attribute");
            a.setAttribute("name", attributeName);
            a.setAttribute("aggregate", type);
            a.setAttribute("alias", alias || type + (++iAlias))
            distinct && a.setAttribute("distinct", distinct);
            cur.appendChild(a);
            return f;
        },
        attribute: function (name) {
            var a;
            if (!cur) throw "missing entity/link-entity element";
            if (cur.nodeName !== "entity" && cur.nodeName !== "link-entity")
                throw "can't add attributes to current parent node";
            a = xmlDoc.createElement(!name ? "all-attributes" : "attribute");
            name && a.setAttribute("name", name);
            cur.appendChild(a);
            return a;
        },
        isDateGrouping: function (value) {
            return (!!dateGroupingMap[value]);
        }
    }

    fetch.link = {
        Inner: "inner",
        Outer: "outer"
    }

    fetch.order = {
        Desc: "desc",
        Asc: "asc"
    }

    fetch.dateGrouping = {
        Day: "day",
        Week: "week",
        Month: "month",
        Quarter: "quarter",
        Year: "year",
        Fiscal_Year: "fiscal-year",
        Fiscal_Period: "fiscal-period"
    }

    var dateGroupingMap = {
        day: "Day",
        week: "Week",
        month: "Month",
        quarter: "Quarter",
        year: "Year",
        "fiscal-year": "Fiscal_Year",
        "fiscal-period": "Fiscal_Period"
    }

    fetch.op = {
        Equal: "eq",
        In: "in",
        Neq: "neq",
        Not_Equal: "ne",
        Greater_Than: "gt",
        Greater_Equal: "ge",
        Less_Equal: "le",
        Less_Than: "lt",
        Like: "like",
        Not_Like: "not-like",
        Not_In: "not-in",
        Between: "between",
        Not_Between: "not-between",
        Null: "null",
        Not_Null: "not-null",
        Yesterday: "yesterday",
        Today: "today",
        Tomorrow: "tomorrow",
        Last_7_Days: "last-seven-days",
        Next_7_Days: "next-seven-days",
        Last_Week: "last-week",
        This_Week: "this-week",
        Next_Week: "next-week",
        Last_Month: "last-month",
        This_Month: "this-month",
        Next_Month: "next-month",
        On: "on",
        On_OR_Before: "on-or-before",
        On_OR_After: "on-or-after",
        Last_Year: "last-year",
        This_Year: "this-year",
        Next_Year: "next-year",
        Last_X_Hours: "last-x-hours",
        Next_X_Hours: "next-x-hours",
        Last_X_Days: "last-x-days",
        Next_X_Days: "next-x-days",
        Last_X_Weeks: "last-x-weeks",
        Next_X_Weeks: "next-x-weeks",
        Last_X_Months: "last-x-months",
        Next_X_Months: "next-x-months",
        Older_Than_X_Months: "olderthan-x-months",
        Last_X_Years: "last-x-years",
        Next_X_Years: "next-x-years",
        Current_User: "eq-userid",
        Not_Current_User: "ne-userid",
        Equal_User_Teams: "eq-userteams",
        Equal_User_OR_User_Teams: "eq-useroruserteams",
        Equal_Bu: "eq-businessid",
        Not_Equal_Bu: "ne-businessid",
        Equal_User_LCID: "eq-userlanguage",
        This_Fiscal_Year: "this-fiscal-year",
        This_Fiscal_Period: "this-fiscal-period",
        Next_Fiscal_Year: "next-fiscal-year",
        Next_Fiscal_Period: "next-fiscal-period",
        Last_Fiscal_Year: "last-fiscal-year",
        Last_Fiscal_Period: "last-fiscal-period",
        Last_X_Fiscal_Years: "last-x-fiscal-years",
        Last_X_Fiscal_Periods: "last-x-fiscal-periods",
        Next_X_Fiscal_Years: "next-x-fiscal-years",
        Next_X_Fiscal_Periods: "next-x-fiscal-periods",
        In_Fiscal_Year: "in-fiscal-year",
        In_Fiscal_Period: "in-fiscal-period",
        In_Fiscal_Period_And_Year: "in-fiscal-period-and-year",
        In_OR_Before_Fiscal_Period_And_Year: "in-or-before-fiscal-period-and-year",
        In_OR_After_Fiscal_Period_And_Year: "in-or-after-fiscal-period-and-year",
        Begins_With: "begins-with",
        Not_Begins_With: "not-begin-with",
        Ends_With: "ends-with",
        Not_Ends_With: "not-end-with"
    }

    private.init.apply(this, arguments);
    return f;
}

Here are a few usage examples taken from MSDN. Example 1: In the following example, the FetchXML statement retrieves all accounts:

<fetch mapping='logical'> 
   <entity name='account'>
      <attribute name='accountid'/> 
      <attribute name='name'/> 
</entity>
</fetch>

fetch()
   .entity("account")
   .attributes("accountid", "name").toString();

Example 2: In the following example, the FetchXML statement retrieves all accounts where the last name of the owning user is not equal to Cannon:

<fetch mapping='logical'>
   <entity name='account'> 
      <attribute name='accountid'/> 
      <attribute name='name'/> 
      <link-entity name='systemuser' to='owninguser'> 
         <filter type='and'> 
            <condition attribute='lastname' operator='ne' value='Cannon' /> 
          </filter> 
      </link-entity> 
   </entity> 
</fetch>  

fetch()
    .entity("account").attributes("accountid", "name")
    .link("systemuser", "owninguser")
       .filter()
           .condition("lastname", fetch.op.Not_Equal, "Cannon")
    .toString();

Example 3: Fetch the average of estimatedvalue for all opportunities.

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
    </entity> 
</fetch>

fetch(false)
    .entity("opportunity")
    .avg("estimatedvalue", "estimatedvalue_avg").toString()

Example 4: Fetch multiple aggregate values within a single query.

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='opportunityid' alias='opportunity_count' aggregate='count'/> 
       <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> 
       <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/> 
    </entity> 
</fetch>

fetch(false).entity("opportunity")
          .count("opportunityid", "opportunity_count")
          .sum("estimatedvalue", "estimatedvalue_sum")
          .avg("estimatedvalue", "estimatedvalue_avg")
  .toString();

Example 5: Fetch a list of users with a count of all the opportunities they own using groupby.

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='opportunity'> 
       <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
       <attribute name='ownerid' alias='ownerid' groupby='true' /> 
    </entity> 
</fetch>

fetch(false)
      .entity("opportunity")
         .countcolumn("name", "opportunity_count")
         .groupBy("ownerid")
   .toString(); 

Let’s assume you need to add if else conditioning to example number 4 above. Since the fetchxml document is built internally you can easily apply the statement as follows:

function aggregate(type){
    var f = fetch(false).entity("opportunity");
    
    if (type === "count")
          f.count("opportunityid", "opportunity_count");
    else if (type === "sum")
          f.sum("estimatedvalue", "estimatedvalue_sum");
    else
          f.avg("estimatedvalue", "estimatedvalue_avg");
          
  return  f.toString();
}
//assuming you have a built in Fetch method on XrmForm
xrmForm.Fetch(aggregate("sum"));

I’ll provide more references in the near future. If you have any questions regarding the library and usage fill free to ask. Cheers,