Dynamics 365 & Office Integration Series - PART 1 - Odata Queries Through Dynamics 365

This article series is about integration practices between Dynamics 365 and office apps.  Integrations have been explained in 4 posts:

PART 1: Odata Queries Through Dynamics 365

PART 2: Connect to an OData feed through Get & Transform in Excel 2016

PART 3: Excel Integration

PART 4: Data Import/Export by Considering Your Business Purpose

Let's get started with PART 1

CONTENT Introduction Some of Odata Query Structures Examples

INTRODUCTION

In Office Integration Series, articles will be providing information about how Dynamics 365 is integrated with the office app.

Dynamics 365 is giving an environment to work as productively as possible by reinventing productivity and business processes.  One of the ways is using Microsoft Office. Microsoft made office one of the pieces of the user interface for Dynamics 365. Some platforms Dynamics 365 is using are Odata and Azure blob storage.

Odata (Open Data Protocol): Data entities are table groups of top of the one or more tables. In other term, entities are a bit of views. They are used for import & export via Odata with the excel app and word app and document generation capabilities. Third-party applications can also use Odata. Odata represents the data in JSON formats. You can open JSON files by using notepad.

Azure blob storage: Not only something that is used for storing document attachments for document management, but also is used as a kind of transition between the system and the user. So when files get generated they get put into the azure blub storage and then the user gets navigated to those and browsers (chrome, internet explorer) deliver those to them.

SOME OF ODATA QUERY STRUCTURES

Odata queries: Type the following queries on your browser's address bar. Prepare your query in accordance with your environment's data.

Metadata - https://<environment>/data/$metadata

Data - https://<environment>/data/<EntitySet> e.g. Customers

Functions - https://<environment>/data/<EntitySet>/$count

In the next part of the article, we will query the following customers in different ways.

EXAMPLES

Let's see how queries work

Query - Metadata - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/$metadata

This metadata feed is publicly accessible. But the following queries are not publicly accessible.

Query - Customers - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers

Note that a JSON file has been prompted by the Odata service.

Save and open it with notepad for each private query.

This is the raw Odata feed for customers.

Since appearance is complicated, we can simplify  it in order to see only the customer names.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/?$select=Name

We can also add another column to simplify customer name query. We need to put %2C before for each additional field. 

In order to add customer number, we put %2CCustomerAccount statement at the end of the query.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/?$select=Name%2CCustomerAccount

We can do some interesting things as well. Let's count how many customers we have.

Query - Functions - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/$count

So system is able to represent the total number of customer account here as well.