Dynamics crm rest builder query for many to many relationship

Question

I need to set a contract lookup field in crm based on the account selected and business unit. This is a many to many relationship in crm .The best way i can think to do this is to create 2 querys/api calls (using crm rest builder) to be able to do this based on the criteria. the first query accesses the intersect table (account-contract table)in order to return all contracts based on the account and the seconds needs to further filter the results by looping through all the results from the first query and do a count off all the contracts that match the business unit selected. the issue im having now is that, i used an array to push all the values from the first query to be able to loop in the second one. However the array i created is not reaching the for loop thus the second query is not executing

function populateContractLookup() {
    var buisnessUnitId = getFieldValue("hc_businessunit");
    var worksiteId = getFieldValue("hc_worksite")[0].id;
    worksiteId = stripCurlies(worksiteId);


    if (buisnessUnitId != null) {
        buisnessUnitId = stripCurlies(buisnessUnitId[0].id);
        var condition = "_hc_businessunit_value eq " + buisnessUnitId + " and";

    }
    else {
        condition = "";
    }//to be able to count the how many contracts that would hav gotten populated to contract lookup field
    var validcontractid;
    var contractCount = 0;
    var contractArray = [];
    //this query gets all contracts based on account
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/hc_account_contractset?$select=contractid&$filter=accountid eq " + worksiteId, true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function () {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
               // Xrm.Utility.alertDialog(results.value.length);

                for (var i = 0; i < results.value.length; i++) {
                    var contractid = results.value[i]["contractid"];
                   // Xrm.Utility.alertDialog(contractid);
                    contractArray.push(contractid);


                }
            } else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }// Xrm.Utility.alertDialog(contractArray.length) prints to the screen here
    };
    req.send();
    //query to furthr filter the above query to get all contracts based on the buisness unit

    //Xrm.Utility.alertDialog(contractArray.length);//not printing to screen
    for (var i = 0; i < contractArray.length; i++) {

        Xrm.Utility.alertDialog("were in the loop"); //not reaching this loop
        var req = new XMLHttpRequest();
        req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/contracts?$select=contractid&$filter=contractid eq " + contractArray[i] + " and  _hc_businessunit_value eq " + buisnessUnitId, true);
        req.setRequestHeader("OData-MaxVersion", "4.0");
        req.setRequestHeader("OData-Version", "4.0");
        req.setRequestHeader("Accept", "application/json");
        req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
        req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
        req.onreadystatechange = function () {
            if (this.readyState === 4) {
                req.onreadystatechange = null;
                if (this.status === 200) {
                    var results = JSON.parse(this.response);
                    Xrm.Utility.alertDialog(results.value.length);
                    for (var i = 0; i < results.value.length; i++) {
                        contractCount++;
                    }
                } else {
                    Xrm.Utility.alertDialog(this.statusText);
                }
            }
        };
        req.send();
    }
}

.ANy ideas of why i cant access my arrary?

Answer 1

You can try switching the first call as synchronous by changing the flag like below & the result ll be available immediately before the second call.

req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/hc_account_contractset?$select=contractid&$filter=accountid eq " + worksiteId, false);

Answer 2

Your first request is asynchronous. The code after your first req.send() is executing immediately, whereas you need the result of your first request to have been returned before any other code executes.

You therefore need to wrap your second request, which is dependent on the result of the first, into a callback function. You then call this callback function in the success callback of your first request.

See this StackOverflow answer for information on callback functions.

As an aside, your second request won't work. You're trying to execute a request once per contract that was retrieved. What you want to do is build your OData filter by iterating over contractArray array and writing '(contractid eq ' + contractArray[i] + ') or' //...

As another aside, consider using a FetchXML aggregate to count records.

---

Your code might look something like this:

var buisnessUnitId = getFieldValue("hc_businessunit");
var worksiteId = getFieldValue("hc_worksite")[0].id;

function populateContractLookup() {        
    worksiteId = stripCurlies(worksiteId);

    if (buisnessUnitId != null) {
        buisnessUnitId = stripCurlies(buisnessUnitId[0].id);
        var condition = "_hc_businessunit_value eq " + buisnessUnitId + " and";
    }
    else {
        condition = "";
    }//to be able to count the how many contracts that would hav gotten populated to contract lookup field
    var validcontractid;
    var contractCount = 0;
    var contractArray = [];
    //this query gets all contracts based on account
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/hc_account_contractset?$select=contractid&$filter=accountid eq " + worksiteId, true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function () {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
               // Xrm.Utility.alertDialog(results.value.length);

                for (var i = 0; i < results.value.length; i++) {
                    var contractid = results.value[i]["contractid"];
                    // Xrm.Utility.alertDialog(contractid);
                    contractArray.push(contractid);                        
                }
                // Call your callback function.
                countContracts(contractArray);
            } else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }// Xrm.Utility.alertDialog(contractArray.length) prints to the screen here
    };
    req.send();
}    

Your callback function (though as I've mentioned above this needs rewriting):

function countContracts(contractArray) {
    for (var i = 0; i < contractArray.length; i++) {
        Xrm.Utility.alertDialog("were in the loop"); //not reaching this loop
        var req = new XMLHttpRequest();
        req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/contracts?$select=contractid&$filter=contractid eq " + contractArray[i] + " and  _hc_businessunit_value eq " + buisnessUnitId, true);
        req.setRequestHeader("OData-MaxVersion", "4.0");
        req.setRequestHeader("OData-Version", "4.0");
        req.setRequestHeader("Accept", "application/json");
        req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
        req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
        req.onreadystatechange = function () {
            if (this.readyState === 4) {
                req.onreadystatechange = null;
                if (this.status === 200) {
                    var results = JSON.parse(this.response);
                    Xrm.Utility.alertDialog(results.value.length);
                    for (var i = 0; i < results.value.length; i++) {
                        contractCount++;
                    }
                } else {
                    Xrm.Utility.alertDialog(this.statusText);
                }
            }
        };
        req.send();
    }
}