Retrieving Data

Use the HTTP GET method to retrieve data from your workspace via endpoint resources. This includes both lists and individual records. For example, you would use the GET method to retrieve a list of sales orders made in a particular month, or the contact details of a customer.

• Successful responses return with a HTTP 200 status code
• By default all successful responses on the Datapel API are returned as JSON.
• The API requires a security domain or scope to be provided with TOKEN requests and REFRESH, for public api resources use pub.
• Forms and Reports can also be returned in PDF format ensure you apply the correct content type header when required.
• Example GET request for a particular product would look like this → https://api2.datapel.net/api.datapel/v2.0/pub/product?34

JSON responses and date formats

By default JSON formatted responses are returned, be sure to use the http header “application/json” when making a request.

Any Date returned in JSON datasets is UTC format represented as YYYY-MM-DDTHH:mm:SS, for example ‘2009-04-29T10:12:20’.

In order to reduce overall data transfer requirements a record level TIMESTAMP is typically available with format ‘2021-10-01 16:35:03’ indicating the time this record was last modified.

Filtering Data

The Datapel API uses the OData standard as the syntax allowing queries of specific data records. By using OData filter queries, you can obtain specific subsets of data, reducing the amount of unnecessary information and enhancing the performance of your workflows.

When using filters, you can use comparison operators like eq for equals, ne for not equals, and so on. You can also use logical operators such as and, or, and not to combine multiple conditions. Additionally, there are functions to refine string-based searches. For example, startswith(fieldName,’string’) checks if a field starts with a specified string, endswith(fieldName,’string’) checks if it ends with one, and substringof(‘string’,fieldName) verifies if a field contains a particular substring.

To explore the syntax and example queries review the OData Standards Support section of this reference guide.

Pagination

A single HTTP GET request can return at most 500 records. This is reasonable because most HTTP clients have a timeout limit of 2 - 5 minutes. They do not allow transactions to wait more than that. A request running longer than the limit gets an HTTP timeout error. Therefore, it is often required to tune complex queries and lower the data size for them to complete within the timeout restriction. Long running queries can also degrade server response times to other users and tenants so its best practice to do smaller queries more often than large one off data requests

The OData API provides several pagination options for query results.

Client-side pagination uses query options on the client side to create an offset that restricts the amount of data returned from the server.

A client-side pagination query can be made using the following URI parameters:

• The $top parameter indicates the number of records to return in the batch. The default and maximum number is 500 records.
• The $skip parameter indicates the number of records in the full data set to skip before fetching data.

For example, a query with $skip=2000&$top=500 returns the fifth page of data where the page size is 500.

Advantages

• Faster initial page load
• It works well with OData compliant list and table web controls to fetch records on demand. DevExtreme UI controls from DevExpress automatically change the $skip value in order to scroll through the data set.
• It allows reverse scrolling by decreasing $skip values. This allows UI controls to avoid buffering all data as the user scrolls up and down.

Considerations when using OData pagination

• For each page, the query is re-executed and then moves forward through the data set to reach the specified $skip value. Not only is this inefficient but also it can lead to unstable results. The data set could be changing due to simultaneous creating, updating, or deleting of the data. Conversely, most modern databases implement caching and will generally store prior query data so this does not present as a major limitation or issue.
• If a record is inserted on a prior page after it has been read, it shifts the data set to the right, resulting the last record from the previous page to be pushed into the current page. This causes the page to be read again because the $skip value of the current page is one record too small, resulting in duplicates in the total data set. You can avoid this issue using $orderBy=TimeStamp to push newly created records to the end of the data set.
• If a record is deleted from a prior page after it has been read, it shifts the data set to the left. The first record of the current page is lost because the $skip value of the current page is one record too large. There’s no good workaround when dealing with concurrent hard deletes from the data set. Note these potential data loss issues make "last modified since" queries especially unreliable. If a record is lost in a query, it can only be picked up by a later attempt after it's modified.

Most queries will return a total record count which allows your request to select a exact page number, and identify the total number of pages to retrieve. Alternatively continue to iterate over the query using the $skip keyword until no records or less than 500 (page maximum) are returned.

What is OData?

OData is a way to query data sources over the web using a standard syntax. It allows requesting of specific data columns with filter expressions to return required data sets typically in JSON format. Because the OData specification is standardised and ISO/IEC approved, you can theoretically use any OData compliant data source and it will respond in the same way to an OData formatted query.

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

The main OData functions are:

• $select
• $expand
• $filter
• $orderBy
• $top
• $skip
• $apply

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

• Get the country and company name of all customers where country is 'UK' - http://services.odata.org/Northwind/Northwind.svc/Customers/?$select=CompanyName,Country&$filter=Country eq 'UK'

$filter

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

• $filter=[Field Name] [Operator] [Value]
• $filter=([Field Name] [Operator] [Value])

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

Operators for first format:

• eq - Exactly equal to
• ne - Inverse of eq
• lt - Less than but not including
• gt - Greater than but not including
• le - Less than or exactly equal to
• ge - Greater than or exactly equal to

Operators for second format:

• contains - Value is contained somewhere in the field
• not contains - Inverse of contains
• startswith - Field starts with value
• not startswith - Inverse of startswith
• endswith - Field ends with value
• not endswith - Inverse of endswith

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

Filters can be chained together internally with 'and' or 'or' to create more complex expressions:

• $filter=([Field] [Operator] [Value]) or/and ([Field] [Operator] [Value])

The in (v1,v2,v3…vN) keyword is not supported, you need to create an expanded filter as (Field eq v1) or (Field eq v2) or … (Field eq vN) where N should be less than 500.

Field Name references and Array $filter

The convention for field name referencing is based on the JSON response property name. There are some limitations on the scope of filtering by field name and on how the response is returned. Field name paths are only support for first level child nodes where the property name is not unique, in all other cases child node path is not required.

Matching scope $filter restrictions for APIv2:

JSON

Select...
{ “FieldName1” : “value1”,
  “Items” : [ { “ArrayElementFieldName” : “arrayValue1” },
            …
  ],
  "FieldName2" : “value2”,
  …
}

• $filter on fields contained within an Item array need to be handled using string search techniques, for example to return all Items that contain arrayValue1 the filter expression would be defined as $filter=contains(Items, ‘arrayValue1’)

• Note also that while the parent elements are returned only the matching item array lines will be returned, if you require all item Array elements it is recommended that a two pass query approach be applied. Use the $select=Uid&$filter=contains(Items,'…'), then query on the array of Uid response elements as $filter=Uid eq value1 or Uid eq value2 …

When filtering on child nodes path elements are only valid to one level, for example $filter=parentnode/childproperty eq ‘value1’ is valid, where the following is not valid as $filter=parentnode/childnode/childproperty eq ‘value1’

$orderBy

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

• $orderby=[Field Name] [Direction]

Direction is either ascending or descending as shown in these examples:

• Every Product ordered by release date - http://services.odata.org/V4/OData/OData.svc/Products/?$orderby=ReleaseDate asc
• Summary of sales by year, largest subtotal first - http://services.odata.org/Northwind/Northwind.svc/Summary_of_Sales_by_Years/?$orderby=Subtotal desc

$top and $skip

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. In order to create PAGED results use the top keyword to specify the number of records and skip * page * number of records per page to get the a particular page in the result set.

The syntax for these is:

• $top=[number]
• $skip=[number]

Some examples:

• Get the first 50 Contacts, skipping the first Contact - http://services.odata.org/V4/OData/OData.svc//Contacts?$top=50&$skip=1
• First 5 Products ordered by release date - http://services.odata.org/V4/OData/OData.svc/Products/?$orderby=ReleaseDate asc&$top=5
• The 10 biggest sales - http://services.odata.org/Northwind/Northwind.svc/Summary_of_Sales_by_Years/?$orderby=Subtotal desc&$top=10

OData Limitations

Datapel API Supports basic OData syntax for keywords select, filter, skip, top, order by and apply. Currently it does NOT support expand and lambda operators.

$filter limited support

• All logical operators except has and in are supported.

Spec: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_LogicalOperators Examples: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_LogicalOperatorExamples

• Grouping filters using () is supported.

Spec: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_Grouping

• String functions startswith, endswith and contains are supported.

Spec: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_StringandCollectionFunctions.

• DateTime functions date, time, year, month, day, hour and minute are supported.

Spec: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31360996

$apply limited support (aggregations)

Aggregations using Odata apply query option is supported.

Spec: http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/odata-data-aggregation-ext-v4.0.html

• Transformations: filter, groupby and aggregate are supported. expand, concat, search, top, bottom are NOT supported.
• sum, min, max, avg, countdistinct and count are supported.
• $filter field names MUST BE included in the groupby column name selection list, calculated fields can be used in the filter expression, using the example below $filter=Total gt 0

Select...
\orders?$apply=groupby((Country),aggregate(Amount with sum as Total,Amount with average as AvgAmt))

Select...
SELECT [Country], Sum(Amount) AS Total, Avg(Amount) AS AvgAmt FROM [Orders] GROUP BY [Country]

To simplify web application development OData compliant web components can bind directly to the API endpoint services. For example the DevExpress DevExtreme javascript visual components have OData 2.0 and 4.0 compatible controls. DevExtreme supports a multitude of frameworks including KnockOut, Angular, React and others. The primary adapter for the API was selected as OData to align with the Microsoft Web Services Stack and the ability to standardise the filtering and selection syntax that simplifies SQL translation.

For general information on OData compliance https://www.odata.org/

For more information on DevExtreme Web Components please refer to the following https://js.devexpress.com/

On OData refer to https://js.devexpress.com/Documentation/Guide/Data_Binding/Specify_a_Data_Source/OData/

For specific connector declaration visit https://js.devexpress.com/Documentation/ApiReference/Data_Layer/ODataStore/

Read and follow the best practices in this topic for optimal OData API performance and a better user experience.

Select...
&$filter= <key> eq <keyvalue>

Select...
{
 "status" : "error", 
 "message" : "Too many requests", 
 "data" : "Total Requests 12000", 
  "error" : {
  "code":"429",
  "message": { "lang":"en-us", 
       "value": "Request rate must not exceed 1 per second or 10,000 per 24 hours."  
      } 
  } 
}

Select...
"tREMOTETransSaleLines": [
      {
        "LineNumber": "1",
        "Code": "Widget",
        "HostCode": "",
        "LineDescription": "Time & Time Again Tool",
        "LineNote": "Blue>>large",
        …

Bearer Token Authentication

Datapel API version 2.0 uses Bearer Token Authentication, a crucial security mechanism that safeguards our API endpoints and ensures secure communication between clients apps and our API servers. This section describes what Bearer Token Authentication is, how it works, and how to manage secure API access.

Bearer Token Authentication is a widely adopted method for securing APIs, relied upon by countless applications and services across the internet. It offers a robust, standardized approach to verifying the identity of clients attempting to access an API resource. With Bearer Token Authentication, you can establish trust between the API server and its clients, ensuring that only authorized parties gain access while keeping unauthorized users at out.

In this documentation reference, we will cover the following key topics:

1. Understanding Bearer Tokens: Get acquainted with the concept of bearer tokens, their structure, and how they function as credentials for authenticating your API requests.
2. Token Generation and Management: Learn how we generate, issue, and manage bearer tokens securely, with best practice token lifetimes and refresh mechanisms.
3. Authentication Flow: Understand step-by-step the process of how client apps present bearer tokens in API requests, and how our API server validates these tokens to grant or deny access.

The following sections describes our implementation of the Bearer Token Authentication process.

Understanding Bearer Tokens

Bearer Tokens are at the heart of Bearer Token Authentication, serving as the digital keys that grant access to API resources.

What is a Bearer Token?

A Bearer Token is a type of access token used in authentication protocols, such as OAuth 2.0 and OpenID Connect. It is a string of characters, typically represented as a long, random alphanumeric sequence. Bearer Tokens act as credentials that a client (usually a user or application) presents to an API server when making a request for a protected resource.

How Do Bearer Tokens Work?

Bearer Tokens operate on a straightforward principle: possession of the token equals access. When a client wishes to access a protected resource via an API, it includes the Bearer Token in the request, typically within the HTTP Authorization header. The server receives the token, validates it, and if the token is valid and not expired, it grants access to the requested resource.

Stateless Authentication

One of the advantages of Bearer Tokens is their statelessness. Unlike session-based authentication, where server-side state is maintained for each user, Bearer Tokens require no server-side storage of session data. This makes them highly scalable and suitable for stateless API architectures, such as RESTful APIs.

Security Considerations

While Bearer Tokens offer an efficient way to authenticate API requests, they come with security responsibilities. Since anyone in possession of a Bearer Token can access the protected resources, it is crucial to protect these tokens from theft and misuse. Security measures include token encryption, short token lifetimes, and token revocation mechanisms.

Token Payload

Bearer Tokens often contain a payload that carries information about the client or user, such as user roles or permissions. This payload can help your API server make access control decisions based on the token's content.

In summary, Bearer Tokens represent the authorization credentials clients use to access an APIs protected resources. Understanding their structure, how they are transmitted, and their security implications is fundamental to implementing secure and efficient API based Application.

Token Generation and Management

Token generation and management are critical aspects of Bearer Token Authentication. To ensure the security of the Datapel API our tokens are 256 bit encrypted and have a limited life span of 30 minutes. A refresh token is provided to allow trust renewal by following the Refresh Authentication flow.

The Bearer Token Authorization flow involves a series of steps that allow a client (such as a user or application) to authenticate itself and gain access to protected resources on our API. This flow is based on the OAuth 2.0 framework and is widely used for securing APIs. Here's a step-by-step breakdown of the typical Bearer Token Authorization flow:

1. Client Registration (Optional): In most cases, our clients need to be registered with API access to our server. During registration, you will receive an API key and secret, which are used to identify and authenticate your application. This step is commonly seen in OAuth 2.0.
2. Authentication Request: Your app initiates the authentication process by sending a request to our API TOKEN endpoint.
3. Token Issuance: The API server processes the token request, verifies identity, and, if everything checks out, issues an access token. This access token is a bearer token, and its purpose is to prove the client's authorization to access protected resources. Included in the token is a refresh token which allows renewed trust access tokens without needing the initial API key or User credentials.
4. Token Usage: The client includes the access token in the Authorization header of its API requests, using the "Bearer" prefix (e.g., "Bearer [access token]"). This token is what grants the client access to the API resources.
5. Token Validation: When the API server receives a request with an access token, it validates the token to ensure it's genuine, unexpired, and has the necessary permissions to access the requested resource. This step often involves checking the token's digital signature, expiration time, and scope.
6. Access Control: If the token is valid, the API server grants the client access to the requested resource. Access control decisions may also be based on the user's identity and the permissions associated with the token.
7. Token Revocation (Optional): To maintain security, tokens can be revoked by either the client or the authorization server when they are no longer needed or if they are compromised.
8. Token Expiration and Refresh (Optional): Access tokens have a limited lifetime. Your application can request a new access token using a refresh token if the original token expires. This helps maintain the session's longevity while minimizing security risks.
9. Logging and Monitoring: Our API has backend monitoring and logging and if required we can assist with troubleshooting any access issues you may experience by reviewing request history.

Welcome to the Endpoint Resources Listing section of our API reference. This comprehensive guide provides a detailed overview of all available endpoints and resources within our API. Whether you're a developer looking to integrate our services into your application or an experienced user seeking to explore the capabilities of our API, you'll find everything you need to get started and make the most of our platform.

In this section, you will find a structured and organized list of API endpoints, each accompanied by a description of its purpose and usage. We have designed our API with simplicity and flexibility in mind, ensuring that you can efficiently interact with our platform to meet your specific needs.

To streamline your development process, we have categorized the endpoints into logical groups and provided clear explanations of the parameters and data formats associated with each resource. Additionally, you'll find information on authentication methods, response formats, and any specific requirements for using particular endpoints.

Whether you're retrieving data, creating records, or performing complex operations, this Endpoint Resources Listing is your go-to reference for understanding and utilizing our API effectively. Feel free to explore the endpoints that align with your project's goals and requirements, and use this documentation as your trusted companion on your API integration journey.

Please refer to the table of contents or use the search functionality to quickly locate the endpoints and resources that interest you. If you have any questions, encounter issues, or need further assistance, our support team is here to help.

When reviewing the Post or Response Body it is recommended to click the expand all option as shown below to display all nodes and properties.

Let's dive in and explore the wealth of possibilities our API offers. Happy coding!

See all updates and changes to our API listed by Date.