Tag: Count Related Child Record Created In Last X Days (Rollup Field Limitation)

I have recently posted a blog where i have showed to you guys how a rollup field can be useful if you need to count related child records : https://rawishblog.wordpress.com/2018/04/24/count-the-number-of-related-child-records-using-a-rollup-field/

however, everything has its limitation, in this case rollup field will not give you an option to to filter record from last x days or from a specific time period.

lets for e.g you needed to count records which are created in specific time period? may last 50 days or 90 days. Thats where you will have to move on and search for other methods – i have come up with a simple supported combination of WebApi + FetchXML.

in this example i am going count related cases for an account.

1. Create a whole number field and put this on the form
2. Use below script :

   function CountRecords() {
   
   var recordId = Xrm.Page.data.entity.getId();
   
   var fetchXml = "<fetch mapping='logical' distinct='false' aggregate='true'>"
    + "<entity name='incident'>"
    + "<attribute name='title' alias='recordcount' aggregate='count' />"
    + "<filter type='and'>"
    + "<condition attribute='createdon' operator='last-x-days' value='100' />"
    + "</filter>"
    + "<link-entity name='account' from='accountid' to='customerid' link-type='inner' alias='ab'>"
    + "<filter type='and'>"
    + "<condition attribute='accountid' operator='eq' value='" + recordId + "' />"
    + "</filter>"
    + "</link-entity>"
    + "</entity>"
    + "</fetch>";
   
   var fetch = encodeURI(fetchXml);
    var entityname = "incidents";
    var serverURL = Xrm.Page.context.getClientUrl();
    var Query = entityname + "?fetchXml=" + fetch;
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.0/" + Query, false); //
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.send();
    if (req.readyState == 4)
    {
    req.onreadystatechange = null;
    if (200 == req.status) {
    var results = JSON.parse(req.response);
    var records = results.value[0].recordcount;
    Xrm.Page.getAttribute("new_totalcasescount").setValue(records);
    }
    else {
    console.log(req.statusText);
    }
    }
    }

    

3. Call this function on on load of your account – it will fetch the related cases and fill the newly created field.
   you will the field has updated on Onload of the form :
   

few things :

• You can get the required fetch XML from advanced find if you need to apply on other entities or with other condition.
• You can use this field to put on views however it might give you the updated information as the script runs on -onload of the form and update the value. Hence , if you need that functionality – please go for a plugin.

i hope this helps.