Search code examples
client-side-scriptingdynamics-365

Dynamics 365 Web API: Saved Query does not retrieve related entity's all columns

In Web API(client side), trying to retrieve records of OpportunityProduct entity using SavedQuery(view).

Able to retrieve existing product's name(Product entity's lookup values), however, same result does not contain all attributes of Product entity, although they are added onto the View I'm retrieving and value of those attribute are not empty.

Here's the code, used to retrieve SavedQuery:

        var products = [];
        var req = new XMLHttpRequest();
        req.open("GET", window.parent.Xrm.Page.context.getClientUrl() + "/api/data/v9.0/opportunityproducts?savedQuery=********-****-****-****-************", true);
        req.setRequestHeader("OData-MaxVersion", "4.0");
        req.setRequestHeader("OData-Version", "4.0");
        req.setRequestHeader("Accept", "application/json");
        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);
                    console.log(results);
                    for (var i = 0; i < results.value.length; i++) {
                        var product = {
                            "Number": results.value[i]["productnumber"],
                            "Name": results.value[i]["[email protected]"],
                            "Unit": results.value[i]["[email protected]"],
                            "PricePerUnit": results.value[i]["[email protected]"],
                            "Quantity": results.value[i]["[email protected]"],
                            "Discount": results.value[i]["[email protected]"],
                            "Discount%": results.value[i]["pub_discountpercentage"],
                            "ExtendedAmount": results.value[i]["[email protected]"]
                        };
                        console.log(product);
                        products.push(product);
                    }
                } else {
                    window.parent.Xrm.Utility.alertDialog(this.statusText);
                }
            }
        };
        req.send();

Here's the result:

"value": [
    {
        "producttypecode": 1,
        "priceperunit": 100,
        "extendedamount": 190,
        "sequencenumber": 1,
        "quantity": 2,
        "opportunityproductid": "********-****-****-****-************",
        "manualdiscountamount": 10,
        "productdescription": "Test 1",
        "isproductoverridden": true
    }
]

Shows columns available in View

In attached image, please see the highlighted Discount % column which is custom attribute on Product entity that I'm trying to retrieve.

Can anyone guide me how can I retrieve related entity's(Product) attributes or all columns specified on View?

Solution

  • After putting some efforts this morning, I learned that when Product is added as Existing Product in OpporutnityProduct, then you can retrieve all fields of View on opportunityProduct, regardless of whether they belongs to OpporutnityProduct entity or any of its related entity.

    But when Product is added as Write-in Product, then you can retrieve fields that belongs to OpportunityProduct entity only.

    I was not able to find Discount % column of Product entity because Product was added using Write-in Product option.

    When I added a Product using Existing Product option, I could retrieve it using the same piece of code.

    Hope this will be helpful.