Dynamics CRM WEB API- Select field from entity reference

Question

I am trying to select a field from another CRM entity by using an expand with my odata query.

Query string: studios$?Select=studioid,studioname,titleid&$Expand=

Title id is a field in custom entity called title. How do I write an expand clause to expand title and select titleid?

Any help is appreciated!

Muhammed Saad Muhammed Saad · Accepted Answer · 2018-10-10T21:34:10

first you can refer to this article from docs.microsoft explaining in detail the $expand feature, however for simplicity is followed by the relation name with targeted entity then the fields you want to select in parentheses

for ex. $expand={relation_name}($select=filed1,field2)

which might be something like this
$expand=new_new_entity1_new_entity2($select=new_name,createdon)

Notice: the values you get from expand relation will be actually navigation URL to actual data, so you don't expect [new_name,createdon] from new_entity2 to be retrieved, what you will get will be a url to OData those values and for that you might below helping function 1 will do it for you

Also you can use XRM Rest Builder that's an amazing solution when you install on the crm you will find it's button in solutions view. that solution is a tool helps you to design your OData query with nice GUI so that you don't need to write and refine your OData query which is faster and easier.

Helping function 1

`

function OdataExpand(expandUrl) {
    var result = null;
    var req = new XMLHttpRequest();
    req.open("GET", expandUrl, false);
    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);
                result = results;
            } else {
                console.log("OdataExpand Error : ");
                console.log(this);
            }
        }
    };
    req.send();
    return result;
}

`

but if you want to get all the data with one call and maybe you want to expand to more than one entity i recommend that you use Fetch XML simply design the fetch you need using advanced find tool to extract the fetchXML then pass it to Helping Function 2 that should return the full data but be aware that max fetch length in this approach is limited to 2,048 characters [Max Length for GET Request].

Helping Function 2
`

function ExecuteFetchXMLQuery(dataSet, fetchQuery) {
    var encodedFetchXML = encodeURIComponent(fetchQuery);
    var result;
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.0/" + dataSet + "?fetchXml=" + encodedFetchXML, false);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");
    req.onreadystatechange = function () {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200 || (this.status >= 200 && this.status <= 206)) {
                console.log("Success Executing Fetch");
                var results = JSON.parse(this.response);
                result = results;
            }
            else {
                console.log("Error Executing Fetch");
                console.log(this);
                console.log(this.statusText);
            }
        }
    };
    req.send();
    return result;}

`

Dynamics CRM WEB API- Select field from entity reference

2 Answers