In this blog post, HSD’s Senior Consultant – Kamran Dilmir will demonstrate how to query N:N relationships with Dynamics 365 web API and how to invoke associate requests using the web API

While working on Dynamics 365 projects, you may have come across a need query N:N relationship data. There was a similar requirement in one of our projects and a requirement to query data from client side using JavaScript code and Dynamics 365/Common Data Service web API. Then on the bases of the results returned, we had to associate two entities together using the same N:N relationship if those records were not already linked.

The problem we faced was that I was not able to find N:N relationship in the list of available entities and because of that I was not able to query in my N:N relationship from java script using the web api. Normally, when we query an entity (no matter custom or default) using the web API, we can simply write the name of the entity’s plural name in the following URL. For instance, if we want to see all contacts in the browser we will run the following url in the browser:

https://<instancename><region>.dynamics.com/OrgName/api/data/v9.1/contacts

But when we have to see records of our N:N relationship in the browser (for example if our relationship name is new_decisionslinkedtoreferrals), The following url will give you the error.

https:// <instancename>.<region>.dynamics.com /OrgName/api/data/ v9.1/new_decisionslinkedtoreferralss

The error message will be something like: “Resource not found for the segment”.

Let’s have a look at how we can query data from a N:N relationship data set and how we can link two records using a N:N relationship using Dynamics 365/Common Data Service web API.

This post will cover:

  • How to query data in a N:N relationship with name new_decisionslinkedtoreferrals
  • How to check if combination of ID’s record (Referralsid, Decisionid) already exist.
  • How to link the records (ComplianceReferrals, Decision) if the combination does not exist in the new_decisionslinkedtoreferrals N:N relationship data set.

(Note: Explanation and code samples are given using JavaScript code.)

Let’s assume we have a custom entity called “Decision” and it is related to a “Compliance Referrals” entity using N:N relationship, so a single referral can be related to multiple Decisions, similarly single decision can be related to multiple Compliance Referrals.

In the example below the relationship name is new_decisionslinkedtoreferrals. However, Dynamics 365 did not have the data set available via web API using its name.

Query N:N Relationship Using API:

In below code example, the fetchXML “fetchDecisionsLinkedToReferrals” query, the entity name is the name of the N:N relationship. But when we send the Get request we will use the name of the entity “new_decisionslinkedtoreferralsset”. This is the same N:N relationship but we have to add “set” at the end of the relationship schema name “new_decisionslinkedtoreferrals” to query using the web API. This is the only tricky bit that is required, As web API does not recognise new_decisionslinkedtoreferrals as an entity in case of N:N relationship.

Associate_OnSave = function () {
    
        var fetchDecisionsLinkedToReferrals = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
            "<entity name='new_decisionslinkedtoreferrals'>" +
            "<attribute name='new_decisionslinkedtoreferralsid' />" +
            "<filter type='and'>" +
            "<condition attribute='new_compliancereferralid' operator='eq' uitype='new_compliancereferralid' value='" + selectedReferralId + "'/>" +
            "<condition attribute='new_decisionid' operator='eq' uitype='new_decisionid' value='" + rowId + "'/>" +
            "</filter>" +
            "</entity>" +
            "</fetch>";

        var serverURL = parent.Xrm.Page.context.getClientUrl();
        var fetch = encodeURI(fetchDecisionsLinkedToReferrals);
        var entityname = "new_decisionslinkedtoreferralsset";
        var Query = entityname + "?fetchXml=" + fetch;
        var req = new XMLHttpRequest();
        
        req.open("GET", serverURL + "/api/data/v9.1/" + 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.onreadystatechange = function () {
            if (this.readyState === 4) {
                this.onreadystatechange = null;
                if (this.status === 200) {
                    var returned = JSON.parse(this.response);
                    var results = returned.value;
                    if (results.length == 0)
                        CreateAssociatedRequest(rowId, selectedReferralId);
                }
            }
        }
        req.send();
}

Associate Request to Link Two Entities of an N:N relationship Using API:

Associate and disassociate request usually used to link and unlink records having relationship. We can link and unlink records depending on their relationship type for example, We can link records having 1:N using update request (setting lookup). In this post we will only discuss associating N:N relationship.

Recalling our above example, if we need to associate decision record with referral entity record in our new_decisionslinkedtoreferrals N:N relationship, we can write POST request using following syntax and we can pass record id’s as objects:

CreateAssociatedRequest = function (decisionId, complianceReferralId) {
    var serverURL = Xrm.Page.context.getClientUrl();
    var associatedReferralDecision = new Object();
    var associatedReferralDecision = {
        "@odata.id": serverURL + "/api/data/v9.1/new_decisions(" + decisionId + ")"
    };

    var req = new XMLHttpRequest();
    req.open("POST", serverURL + "/api/data/v9.1/new_compliancereferrals(" + complianceReferralId + ")/new_decisionslinkedtoreferrals/$ref", 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.onreadystatechange = function () {
        if (this.readyState == 4 /* complete */) {
            req.onreadystatechange = null;
            if (this.status == 204) {
                var accountUri = this.getResponseHeader("OData-EntityId");
            } else {
                var error = JSON.parse(this.response).error;
                alert(error.message);
            }
        }
    };
    req.send(JSON.stringify(associatedReferralDecision));
}

To see how HSD’s Technology Practices can assist you with your system solution, please visit https://www.hsd.com.au/technology/ for more information.