Microsoft’ data service commonly provide RESTful interfaces that utilize OData syntax.
Filter Rows
Beside the option to retrieve all records beloning to a entity you have the ability to specify additional filter criteria for your query. The filter employs various syntax and operators, including acronyms such as eq for equal and gt for greater than, among others.
| Symbol | Operator | Example |
|---|---|---|
| eq | = | $filter=size eq 100 |
| ne | != | $filter=size ne 200 |
| gt | > | $filter=size gt 50 |
| ge | >= | $filter=size ge 100 |
| lt | < | $filter=size lt 300 |
| le | <= | $filter=size le 125 |
| and | & | $filter=size gt 100 and size lt 200 |
| or | | | $filter=size lt 10 or size gt 400 |
| not | ! | $filter=not size eq 100 |
| () | precedence | $filter=(size eq 100) or (...) |
The query response has the following schema.
{
odata.metadata: "http://.../$metadata",
value:
[
{
name: "Crocodile",
url: "https://en.wikipedia.org/wiki/Crocodile"
},
{
name: "Alligator",
url: "https://en.wikipedia.org/wiki/American_alligator"
}
]
}
Limit rows
The top command lets you limit the number of records your query will return.
/accounts?$top=10
Joining Tables
The way to join tables in Odata is to make use of the expand command which will return a nested document with the related items. For example to looks up all acounts and joins them with their addresses use the follwing query:
crm.dynamics.com/api/data/v9.2/acoutns?$select=name&$expand=Account_CustomerAddrss
The expand command takes a relationship to resolve the link between the tables. To know what name the relationship has you can do the following.
On the address table go to relationshpas and search for the name of the relationship to your source table.
| Display name | Name | Related table | Relationship type |
|---|---|---|---|
| Parent | Account_CustomerAddress | Account | Many-to-one |