OData APIs
Introduction
OData is a service within the HR application website, that allows integration partners to request specific data from any permitted table or view within the HR database.
Confidential clients
Administrators are expected to create their own confidential clients for their integrations partners, and assign permissions specifying exactly what data they can access. See the guide to setting up confidential clients for more information.
The confidential clients are authenticated using the Advanced SSO service. This service validates their credentials when they try to log in, and provides them with a token that they use for any request to the OData service.
Once the user has a valid token and requests data, the OData service uses Microsoft SQL Server Roles to ensure that they only get access to the data that the customer has specified. This is called Authorisation.
Authentication
We have implemented the OAuth 2.0 Client Credentials flow for the authentication of OData subscribers.
An Administrator will create a new confidential client in Advanced HR (as specified in our confidential client guide), and then provide the newly generated Client Id and Secret to the integrations partner.
They also provide the partner with the Authentication Token URL, which is where they will submit their credentials in exchange for a token.
Once the integrations partner has a valid token, they can make requests to the OData service. They add the token to the request’s Authorization header.
Authorisation
Once the integrations partner’s system has made a request to the OData service, their token is inspected to identify the confidential client and pass the Authorisation part of the request process.
If the user has permission to read the data that they have requested, they will receive a response in a JSON format.
Endpoint
HR now supports some basic OData queries through it’s new /odata endpoint.
Instead of using API endpoints which return a fixed list of fields with limited filtering, the OData endpoint allows applications to request any fields from any table or view in HR that they have access to, and filter, sort and paginate that data, in pretty much any way they need to.
To try out the OData endpoint, we recommend using postman. Postman allows you to log in to HR, and receive an access token, which can then be used to query the HR data using the guidelines below.
For details on how the HR data is secured for use with OData, see Advanced HR OData Security.
Requesting resources
The most basic request to /odata is for all the data to be returned from a single resource. A resource can be any HR table or view.
Here’s how to request all the data from the bank name table:
GET /api/v1/workplaces/{workplaceRef}/odata/bank_name_tableThe result will be a JSON object such as this example:
{
"count": 10,
"nextLink": "?top=10&skip=10",
"value": [
{
"Bank_Name": "Abbey National",
"ID": 1,
"TimeStamp": "AAAAAAEqqhY=",
"RecordDescription": null
},
{
"Bank_Name": "Alliance & Leicester",
"ID": 2,
"TimeStamp": "AAAAAAEqqhc=",
"RecordDescription": null
},
{
"Bank_Name": "Allied Irish Bank",
"ID": 3,
"TimeStamp": "AAAAAAEqqhg=",
"RecordDescription": null
},
{
"Bank_Name": "Bank of Ireland",
"ID": 4,
"TimeStamp": "AAAAAAEqqhk=",
"RecordDescription": null
},
{
"Bank_Name": "Bank of Scotland",
"ID": 5,
"TimeStamp": "AAAAAAEqqho=",
"RecordDescription": null
},
{
"Bank_Name": "Barclays Bank",
"ID": 6,
"TimeStamp": "AAAAAAEqqhs=",
"RecordDescription": null
},
{
"Bank_Name": "cahoot",
"ID": 7,
"TimeStamp": "AAAAAAEqqhw=",
"RecordDescription": null
},
{
"Bank_Name": "Citibank",
"ID": 8,
"TimeStamp": "AAAAAAEqqh0=",
"RecordDescription": null
},
{
"Bank_Name": "Clydesdale Bank",
"ID": 9,
"TimeStamp": "AAAAAAEqqh4=",
"RecordDescription": null
},
{
"Bank_Name": "Co-operative Bank",
"ID": 10,
"TimeStamp": "AAAAAAEqqh8=",
"RecordDescription": null
}
]
}As we have not specified any pagination settings, the default values of fetching the top 10 rows of data have been applied.
At the top of the response you can see a count and a nextLink property. The count property specifies the number of records returned in the response, and the nextLink property can be used to fetch the next set of records.
Queries
Applications can query the /odata endpoint with a series of query options.
For example, here’s how to request the 'full name' of the first five employees in the sales department
GET /api/v1/workplaces/{workplaceRef}/odata/current_employees?$top=5&$select=full_name, department&$filter=department eq 'sales'Which might give the following result:
{
"count": 5,
"nextLink": "?select=full_name%2C%20department&filter=department%20eq%20%27sales%27&top=5&skip=5",
"value": [
{
"full_name": "Harry Duley",
"department": "Sales"
},
{
"full_name": "Peter Harris",
"department": "Sales"
},
{
"full_name": "Andy Thomas",
"department": "Sales"
},
{
"full_name": "Mark Craden",
"department": "Sales"
},
{
"full_name": "Christine Peters",
"department": "Sales"
}
]
}The query explained
Query section | Explanation |
| A GET request asks the |
| This is the |
| The resource name. This can be any table or view in HR. Spaces must be replaced with underscores |
| The first query must be prefixed with a question mark |
|
|
| All subsequent queries must be prefixed with the ampersand symbol '&'
In this example, we’ve asked for only full_name and department to be returned. Spaces in field names must be replaced with underscores If you leave out the Separate the field list with commas |
|
In this example we’ve specified that we only want results where the Character values, e.g. Numeric values are not quoted Date values should be quoted and specified in complete date/time format, e.g. Other logical operators are
The OData endpoint also supports some string functions:
|
Some other query options, not included in the previous example, include:
Query option | Explanation |
| Allows users to specify the order of the results e.g.
use |
|
These are used for pagination. If you want results in groups of 10 records, and require page 3, you’d specify |
