This guide is a getting started document, starting with an introduction to OData and goes onto explain how to get a token and gives examples of the many ways to query OData. If you already know that stuff, and are just looking for details of the endpoints and the OData data models, then please refer to the API documentation.

A REST-based protocol for querying and updating data built on standard technologies such as HTTP, Atom/XML and JSON.

OData is standardised by OASIS and is ISO/IEC approved.

OData is a way to query databases over the web in a uniform way, requesting the data in a variety of formats (mainly JSON and XML).

Because the OData specification is standardised and ISO/IEC approved, we can theoretically use any OData feed, and it'll respond in the same way to the defined OData functions.

Some OData services are open (available to anyone without authentication). You'll need a Personal Access Token (PAT) to access our OData services. Visit this article for information on generating a PAT.

Queries are constructed from 3 parts:

A complete query will look something like this:

https://crm-odata-v1.prospect365.com/Contacts
    ?$select=Title,Forename,Surname,Email,StatusFlag
    &$filter=contains(email,'@prospectsoft.com') and StatusFlag eq 'A'
    &$expand=Leads(
        $select=Description,Created,StatusFlag;
        $filter=StatusFlag eq 'A' and Status/DeadFlag eq 0;
        $expand=Status(
            $select=Description
        )
    )

If you want to try that query later on, when you're connected and authenticated, you'll need to copy and paste it without the white space. Here's the same code as above, but this time without the formatting:

The base URL explains where you want to get your data from. Some examples are:

The services.odata example URLs are unsecured, so you can use these to test queries without needing to worry about authentication.

The Prospect OData service is secured with bearer authentication, which means you need to add a bearer authorisation header to your request, using a valid access token. If you don't have a token, you can create one following the steps in this article.

These access tokens allow reading and writing to the database as you, with your user ID. It allows the same access to the data as you have when you log in. You should treat this token as if it's your password. Don't hand out your token or share it with anyone. If a colleague, another developer or someone else needs a token, they should generate one themselves - with their own identity and permissions.

If you ever need to disable a token, you can revoke them from here.

The entity is the type of CRM record you want to access. These are defined in our database and generally mirror our tables.

At the time of writing this, we don't have a full list of entities available on our service, but for the most part they are the plural of the table name.

This entity name is appended after the base URL like so:

Please note: Running these queries without filtering will retrieve all data for those entities. If you try to retrieve 50,000 Contacts, it'll take a long time to complete.

Wherever possible, you should aim to only retrieve the data you need. Smaller chunks of data, over a larger number of queries, is almost always better.

To retrieve a specific record by ID, you can append it to the entity name in parentheses:

Parameters are made up of the various OData functions for selecting and filtering entities.

The main functions are:

These parameters are chained together after the entity using '&':

Please note: For most services, the entity has a '/' before the parameters. On our service we opted to omit this.

OData functions are used in the parameters section to manipulate the data before it's returned to the caller. These functions can be used alone, or chained together to make more complex queries.

This allows you to reduce the data returned by only picking the fields you want to see. This is synonymous to SQL's select, where the OData default is all (select * from...).

The select looks like this: $Select=[Field 1],[Field 2]...where Field 1, Field 2 and so on are replaced with field names.

Each field you want to select is separated by a comma:

Expand allows you to move from the base entity out to other levels of the hierarchy.

The syntax for expand is: $Expand=[Navigation Property]

Navigation properties are the links between entities, and therefore tend to match foreign keys.

These links can be used in both directions, E.g. Contact to Company is Contact. Company. Company to Contact is Company. Contacts.

Compared to SQL, these are most similar to the Join syntax.

Expand is the most complex function, as it allows you to chain other functions inside of the expand e.g. selecting Contact forename and Company name:

Once inside an expand, you can chain together functions by using ';'. For example, to select every field on Contact, the Company name and everything from Company:

Filters allow you to reduce the data set by specifying a field and a requirement. There are 2 formats for filter:

The correct format to use depends on the operator you are using.

Operators for first format:

Operators for second format:

There are other more specific expressions which you can find at the Microsoft Documentation on filters.

Finally, filters can be chained together internally with 'and' or 'or' to create more complex expressions:

Either style of filter can be chained together this way.

Some examples:

The orderBy function allows you to specify a sort for your data. The syntax for this is:

Direction is either ascending or descending. Some examples:

Top and Skip tend to go hand in hand. Top can be used to limit the number of records returned, while skip allows you to start from further down your list of results.

The syntax for these is:

Some examples:

For details of the endpoints and the OData data models, please refer to the API documentation.