Unable to expand customerid in salesoreders object - MS Dynamics 365 CRM

Question

I am trying to get list of orders for the customer whose email is equal to "[email protected]". For this I am executing

GET [Organization URI]/api/data/v9.1/salesorders?$select=name,salesorderid&$expand=customerid($select=fullname,emailaddress1)&$filter=customerid/emailaddress1 eq '[email protected]'

while hitting this endpoint I'm getting bellow error:

{
    "error": {
        "code": "0x0",
        "message": "Could not find a property named 'customerid' on type 'Microsoft.Dynamics.CRM.salesorder'."
    }
}

However _customerid_value is present in salesorders:

{
            "@odata.etag": "W/\"4934903\"",
            "salesorderid": "030474b9-722c-ee11-bdf4-000d3a37ca8a",
            "_ownerid_value": "17bd0c02-d822-ee11-9cbe-00224809d59c",
            "shipto_contactname": "9999999999",
            "name": "test order",
            "emailaddress": "[email protected]",
            "ordernumber": "ORD-01000-F3Y9Y6",
            "_createdby_value": "17bd0c02-d822-ee11-9cbe-00224809d59c",
            "pricingerrorcode": 0,
            "totallineitemdiscountamount_base": 0.0000000000,
            "msdyn_iomreadonly": false,
            "_modifiedonbehalfby_value": "17bd0c02-d822-ee11-9cbe-00224809d59c",
            "statecode": 0,
            "msdyn_isreadytosync": false,
            "_customerid_value": "b258b768-722c-ee11-bdf4-000d3a37ca8a",
            "msdyn_isiomorder": false,
            "billto_line1": null,
            "shipto_telephone": null,
        }

Is there any way to this?

Answer 1

customerid is not a standard lookup (pointing to a single table) but a customer type, it can reference an account or a contact.

In Web API queries the navigation property is used when do some filtering, it can be the same as the lookup name but most of the time is not.

In your query you will need to change customerid to customerid_account (in both expand and filter points)

GET [Organization URI]/api/data/v9.1/salesorders?$select=name,salesorderid&$expand=customerid_account($select=fullname,emailaddress1)&$filter=customerid_account/emailaddress1 eq '[email protected]'

If you need to generate other Web API queries you can use my tool Dataverse REST Builder.