OData in Dynamics 365: How to Get the Data You Need, Fast

If you’ve worked with Microsoft Dynamics 365 (or Power Platform and Dataverse), you’ve likely encountered OData queries—used in Power Automate, Canvas Apps, JavaScript code, Power BI, and beyond. But what exactly is OData? How does it work? And how can you use it to build powerful, efficient solutions in your business apps?

Jump to section:

• 1. What Is OData?
• 2. Under the Hood
• 3. Where It’s Used
• 4. Common Queries
• 5. Common Mistakes
• 6. Debugging Tips
• 7. JavaScript Example
• 8. Power Automate Example

---

1. What Is OData?

OData (Open Data Protocol) is a standardized REST API protocol developed by Microsoft. It allows for querying and manipulating data using simple URLs and standard HTTP verbs (GET, POST, PATCH, DELETE). Dynamics 365 and Dataverse use OData v4 through their Web API.

https://<yourorg>.crm.dynamics.com/api/data/v9.2/

Key Features of OData:

• RESTful architecture based on HTTP
• Simple URL-based query syntax
• Supports metadata discovery via $metadata endpoint
• Returns data in JSON (or Atom XML)

---

2. Under the Hood

OData is built on:

• HTTP Methods: For CRUD operations
• URI query options: $filter, $select, $expand, $orderby, $top, $skip
• Entity Data Model (EDM) via /api/data/v9.2/$metadata
• JSON payloads for data exchange

Supported HTTP Verbs:

• GET – Retrieve data
• POST – Create new record
• PATCH – Update existing record
• DELETE – Delete record

---

3. How OData is Used in Dynamics 365

• Power Automate – Filter records using “List Rows” with OData filters
• Canvas Apps – Filter galleries and data tables using OData-based functions
• JavaScript on Model-driven Apps – Make client-side API calls using OData Xrm.WebApi & fetch()
• Power BI – Load Dataverse tables via Real-time OData feeds
• Custom Integration – Use OData endpoints from external systems (e.g., Azure Functions, Node.js)

---

4. Common OData Queries

// Filter active accounts
/accounts?$filter=statecode eq 0

// Select specific columns
/accounts?$select=name,accountnumber

// Sort by creation date
/accounts?$orderby=createdon desc

// Top 10 records
/accounts?$top=10

// Date-based filter
/accounts?$filter=createdon ge 2025-05-01T00:00:00Z

// Text search
/contacts?$filter=contains(fullname,'John')

// Lookup filter
/contacts?$filter=_parentcustomerid_value eq 'GUID'

// Expand lookup (parent contact)
/accounts?$expand=primarycontactid($select=fullname,emailaddress1)

---

5. Common Mistakes

• Use eq not ==; and not &&
• Enclose strings in single quotes: name eq 'Contoso'
• Using unsupported functions like substring
• Incorrect lookup schema or missing _value suffix for lookups, such as:_parentcustomerid_value
• Unsupported nested $expand (only one level is supported)

---

6. Debugging Tips

• Inspect /api/data/v9.2/$metadata for correct names
• Test queries in Postman or browser
• Use Dev Tools → Network for fetch/Xrm.WebApi calls
• Read error messages—they often explain the issue

---

7. JavaScript Example: Basic Account + Contact Expand

// Fetch Account with Primary Contact info
var accountId = "00000000-0000-0000-0000-000000000000";

var reqUrl = Xrm.Utility.getGlobalContext().getClientUrl() +
  "/api/data/v9.2/accounts(" + accountId + ")" +
  "?$select=name,accountnumber" +
  "&$expand=primarycontactid($select=fullname,emailaddress1,jobtitle)";

fetch(reqUrl, {
  method: "GET",
  headers: {
    "OData-MaxVersion": "4.0",
    "OData-Version": "4.0",
    "Accept": "application/json",
    "Content-Type": "application/json; charset=utf-8",
    "Prefer": "odata.include-annotations=*"
  }
})
.then(response => response.json())
.then(data => {
  console.log("Account Name:", data.name);
  console.log(data.primarycontactid
    ? "Contact: " + data.primarycontactid.fullname
    : "No Contact");
})
.catch(console.error);

Another example - Retrieve Contacts with their parent Account names using $expand=parentcustomerid_account:

// Retrieve Contacts with Parent Account Name
Xrm.WebApi.retrieveMultipleRecords(
  "contact",
  "$select=fullname,emailaddress1&$expand=parentcustomerid_account($select=name)"
).then(
  function success(result) {
    result.entities.forEach(function (contact) {
      console.log(
        `Contact: ${contact.fullname}, ` +
        `Email: ${contact.emailaddress1}, ` +
        `Account: ${contact.parentcustomerid_account?.name || "No Account"}`
      );
    });
  },
  function (error) {
    console.error("Error retrieving contacts:", error.message);
  }
);

---

8. Power Automate Example: “List Rows” with OData

// Filter = only active contacts from last 30 days
statuscode eq 1 and createdon ge 2025-05-25T00:00:00Z

// Select columns
fullname,emailaddress1

// Order and limit results
Order by: createdon desc
Top Count: 50

Configure the Dataverse “List rows” step with these fields. This approach minimizes data transfer and maximizes performance.

---

Empower your Dynamics 365 solutions with efficient, accurate OData queries—whether you’re building automations, apps, or APIs. Happy querying!