How to execute OData queries on data?

What is OData?

OData (Open Data Protocol) is a standard protocol that allows data creation and consumption using a Web API.

It is a standard built on HTTP and REST protocols, and that supports CRUD operations on the entities available on the Web API.

Given its characteristics, this protocol is particularly useful to execute ad-hoc queries on that data.

More info: http://www.odata.org/

OData

The OData services available in Jasmin allow viewing Jasmin's entities from external apps, for example, its attributes, the corresponding values, and the related entities.

OData allows to perform a more agile and customized consultation, considering that it allows using the parameters to get the desired results.

Parameters for OData consultations

OData consultations are simply the execution of GET actions on the Web API, with certain parameters that condition the results.

Next, we present the parameters that can be used in OData consultations, as well as examples of its application on Jasmin's Web API.

Tip: Every time properties are referenced in OData expressions, the Pascal Case convention must be used (first letter if each word in uppercase)

$TOP

Allows to define the number of desired results.

Example:

To get the first 5 results from the sales item list:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$top=5

$SKIP

Allows to ignore the N first results of a query.

Example:

To get 5 records from the sales item list, ignoring the first 5:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$skip=5

$INLINECOUNT

Allows to include a record totalizer on the query results.

Example:

To get the total of records existent in the sales item list:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$inlinecount=allpages

$ORDERBY

Allows to order, in ascending or descending order, the results for one or more attributes.

Example:

To get the existent records in the sales item list, by ordering by attribute "ItemKey" in descending order:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$orderby=ItemKey desc

$SELECT

Allows to select the attributes to be presented on the results returned by the query.

Example:

To get the attributes "ItemKey" and "Description" from the existent records on the sales item list:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$select=ItemKey,Description

$EXPAND

Allows to expand the consultation to entities related with the search entity, for example, when you wish to select a detail's attribute. This parameter can only be used associated to the $SELECT parameter.

Example:

To get the attributes "ItemKey" and "Description" from the existent records on the sales item list and the attribute "PriceAmountAmount" from the details:

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$select=ItemKey,Description,PriceListLines/PriceAmountAmount&$expand=PriceListLines

$FILTER

Allows to filter the results based in certain conditions, namely, the followings:

EQ (Equal)

To get the records in which the attribute "ItemKey" is equal to "PORTES":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey eq 'PORTES'

AND

To get the records in which the attribute "ItemKey" is equal to "PORTES" and the attribute "Unit" is equal to "UN":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey eq 'PORTES' and Unit eq 'UN'

OR

To get the records in which the attribute "Unit"is equal to "UN" or "KG":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= Unit eq 'UN' or Unit eq 'KG'

LT (LESS THAN)

To get the records in which the attribute "ItemKey" is less than "0010":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey lt '0010'

GT (GREATER THAN)

To get the records in which the attribute "ItemKey" is greater than "0010":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey gt '0010'

GE (GREATER THAN OR EQUAL TO)

To get the records in which the attribute "ItemKey" is greater than or equal to "0010":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey ge '0010'

LE (LESS THAN OR EQUAL TO)

To get the records in which the attribute "ItemKey" is less than or equal to "0010":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= ItemKey le '0010'

STARTWITH

To get the records in which the attribute "ItemKey" starts with "POR":

http://my.jasminsoftware.com/api/{account}/{subscription}/salescore/salesitems/odata?$filter= startswith(ItemKey , 'POR')

SUBSTRINGOF

To get the records in which the value ‘cadeira’ is part of the string for attribute "Description":

http://my.jasminsoftware.com/api/{account}/{subscr

Parameters combination

The parameters mentioned previously can be combined to streamline searches. Next, we present a few practical examples.

Example:

To get the attributes "CustomerPartyName" and "SeriesNumber" for the records on the orders list, in descending order by the attribute "SeriesNumber", as well as the counter of records that match the search criteria:

http://my.jasminsoftware.com/api/{account}/{subscription}/sales/orders/odata?$inlinecount=allpages&$select=BuyerCustomerPartyName,SeriesNumber&$orderby=SeriesNumber desc

To get the attributes "BuyerCustomerPartyName" and "PayableAmountAmount" for the 10 orders with the highest value, presented from the highest value to the lowest value, as well as the counter of records that match the search criteria:

http://my.jasminsoftware.com/api/{account}/{subscription}/sales/orders/odata?$inlinecount=allpages&$select=BuyerCustomerPartyName,PayableAmountAmount&$top=10&$orderby=PayableAmountAmount desc