Hawkeye
You are here: Extend the platform > Nintex Hawkeye OData API > Guide > OData Query Cheat Sheet

OData query cheat sheet

This topic contains a reference for construction filters and queries for OData URIs. You can use filter and query expressions in OData URIs to limit the results that are returned.

This topic contains references for:

Query string parameters

System query options are query string parameters that control the amount and order of the data returned for the resource identified by the URL. The names of all system query options are prefixed with a dollar ($) character.

For GET requests the following rules apply:

Reference table of query string parameters

You can use the following string parameters to work with your lens return data in Hawkeye.

Query String Parameter Explanation

$count

The $count system query option allows clients to request a count of the matching resources included with the resources in the response.

For example:

https://wus-odata.hawkeyecloud.com/v4/usage/v1/233ceacf-87f0-4d7c-80ec-17d118626916/3f149a6e-78ff-e511-80c8-000d3a32faa6/InteractionSummary/$count

Returns a count:

113

$expand

The $expand system query option specifies the related resources to be included in line with retrieved resources. You might think of this is an SQL join operation. Only navigation properties can be expanded; you can identify navigation propertes in the Data Model entity relationship diagram.

For example:

https://wus-odata.hawkeyecloud.com/v4/usage/v1/233ceacf-87f0-4d7c-80ec-17d118626916/3f149a6e-78ff-e511-80c8-000d3a32faa6/WorkflowActionSummary?$expand='WorkflowId'

$filter

The $filter system query option allows clients to filter a collection of resources that are addressed by a request URL.

See Filter expressions below for a list of common expressions.

$orderby

The $orderby system query option allows clients to request resources in a particular order.

For example:

https://wus-odata.hawkeyecloud.com/v4/usage/v1/233ceacf-87f0-4d7c-80ec-17d118626916/37df5e9d-5734-e611-80c3-000d3a320ed2/WorkflowInstanceActionByYear?$orderby=MaxDurationInsSeconds.

$search

The $search system query option allows clients to request entities matching a free-text search expression.

For example:

http://host/service/Products?$search=blue OR green

$select

The $select system query option allows clients to requests a specific set of properties for each entity or complex type.

For example:

http://host/service/Products?$select=Rating,ReleaseDate

$skip

The $top system query option requests the number of items in the queried collection to be included in the result. The $skip query option requests the number of items in the queried collection that are to be skipped and not included in the result. A client can request a particular page of items by combining $top and $skip.

For example:

https://wus-odata.hawkeyecloud.com/v4/usage/v1/233ceacf-87f0-4d7c-80ec-17d118626916/3f149a6e-78ff-e511-80c8-000d3a32faa6/WorkflowActionSummary?$skip=10

$top

The $top system query option requests the number of items in the queried collection to be included in the result. The $skip query option requests the number of items in the queried collection that are to be skipped and not included in the result. A client can request a particular page of items by combining $top and $skip.

For example:

https://wus-odata.hawkeyecloud.com/v4/usage/v1/233ceacf-87f0-4d7c-80ec-17d118626916/3f149a6e-78ff-e511-80c8-000d3a32faa6/WorkflowActionSummary?$top=10

Referencing different data types in filter expressions

You must use the appropriate notation for different data types with filter expressions.

Filter expressions

To add a filter to an OData URI, add $filter= to the end of the name of the published web service. For example, the following URI filters the City field in the Customer page to return all customers who are located in Miami:

http://localhost:7048/DynamicsNAV/OData/Company('CRONUS International Ltd.')/Customer?$filter=City eq 'Miami'

Reference table of filter expressions

You can use the following expressions to construct a filter in Hawkeye.

Filter Operation Example Explanation

Equal

filter=ActionCount+eq+10

Query on ActionCount to find ActionCount that equals 10.

Select a range of values filter=Entry_No gt 610 and Entry_No lt 615

Query on Entry service. Returns entry numbers 611 through 614.

And filter=Country_Region_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS'

Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.

Or filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US'

Query on Customer service. Returns customers in Spain and the United States. You can use OR operators to apply different filters on the same field. However, you cannot use OR operators to apply filters on two different fields.

Less than filter=Entry_No lt 610

Query on GLEntry service. Returns entry numbers that are less than 610.

Greater than filter= Entry_No gt 610

Query on GLEntry service. Returns entry numbers 611 and higher.

Greater than or equal to filter=Entry_No ge 610

Query on GLEntry service. Returns entry numbers 610 and higher.

Less than or equal to filter=Entry_No le 610

Query on GLEntry service. Returns entry numbers up to and including 610.

Different from (not equal)

filter=VAT_Bus_Posting_Group ne 'EXPORT'

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.

endswith

filter=endswith(VAT_Bus_Posting_Group,'RT')

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.

startswith

filter=startswith(Name, 'S')

Query on Customer service. Returns all customers names beginning with “S”.

substringof

filter=substringof(Name, ‘urn’)

Query on Customer service. Returns customer records for customers with names containing the string “urn”.

length

filter=length(Name) gt 20

Query on Customer service. Returns customer records for customers with names longer than 20 characters.

indexof

filter=indexof(Location_Code, ‘BLUE’) eq 0

Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.

replace

filter=replace(City, 'Miami', 'Tampa') eq 'CODERED'

 

substring

filter=substring(Location_Code, 5) eq 'RED'

Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.

tolower

filter=tolower(Location_Code) eq 'code red'

 

toupper

filter=toupper(FText) eq '2ND ROW'

 

trim

filter=trim(FCode) eq 'CODE RED'

 

concat

filter=concat(concat(FText, ', '), FCode) eq '2nd row, CODE RED'

 

day

filter=day(FDateTime) eq 12

 

month

filter=month(FDateTime) eq 12

 

year

filter=year(FDateTime) eq 2010

 

hour

filter=hour(FDateTime) eq 1

 

minute

filter=minute(FDateTime) eq 32

 

second

filter=second(FDateTime) eq 0

 

round

filter=round(FDecimal) eq 1

 

floor

filter=floor(FDecimal) eq 0

 

ceiling

filter=ceiling(FDecimal) eq 1

 

 

Related informationRelated information

Help for Nintex Hawkeye OData guide

Authorization

Pagination

URL format