Best Practice | Data Filtering requirements
  • 10 Oct 2024
  • 2 Minutes to read
  • Dark
    Light

Best Practice | Data Filtering requirements

  • Dark
    Light

Article summary

The following is a list of parameters offered by CMiC to help filter, sort and order data queried. It is strongly recommend by CMiC developers to leverage all four parameters when making GET requests to our API endpoints.

CMiC API Oracle ADF BC Framework

CMiC APIs are built using an Oracle ADF BC Restful JSON framework.

A GET method is the action required to retrieve/query data out of the CMiC database.

Finders Parameter

Applying a row finder to a resource collection using a GET method supports filtering the data by specifying in the URL string one or more row finder parameter values.

The row finder query string parameter format is:
?finder=rowfinderName;attr1=value1,attr2=value2,...

Example, GET request using the finder attribute;

https://atlas-api-test.cmiccloud.com/cmictest/pm-rest-api/rest/1/pmproject?finder=selectByCompProj;compCode="10"projCode="1503"

image.png

Fields parameter

The query parameter fields ensure the response payload contains only the specified attributes of the resource being queried. The fields attribute is used in conjunction with a GET request AND supplied row finder parameter.

Example GET request using the finder attribute; note the use of fields attribute by using an & to indicate more than one url attribute is being used,

https://atlas-api-test.cmiccloud.com/cmictest/pm-rest-api/rest/1/pmproject?finder=selectByJobCode;companyCode=001,projectCode=15600&fields=GrpmpCompCode,GrpmpProjCode,GrpmpProjName

image.png

Pagination

Limits and Offset

Retrieving resource collections with row set pagination using a GET method. Paging a resource collection with the GET method is performed using the following URI query parameters:
limit restricts the number of resources returned inside the resource collection. If the limit exceeds the resource count, then the framework will return all available resources. The value is the maximum number of resources to be returned.

For best results, CMiC recommends a maximum limit of 500 be utilized in all cases. Offset defines a zero-based index into the collection (where 0 is the first position). The index identifies the starting position of the resource collection. If offset exceeds the resource count, then no resources are returned.

https://atlas-api-test.cmiccloud.com/cmictest/pm-rest-api/rest/1/pmproject?finder=selectByJobCode;companyCode=001&fields=GrpmpCompCode,GrpmpProjCode,GrpmpProjName&limit=500&offset=0

image.png

orderBy: asc/desc

The orderBy attribute, retrieves rows using a order ensuring the response payload displays data in the order supplied in the URL parameter using the GET method.

The orderBy attribute is recommended to be applied after the finder, fields, and pagination parameters are set on the URL. A orderBy:asc/desc using would produce the best results.
Example of an orderBy,

Note: the OrderBy parameter sorts the data in ascending order by default

https://atlas-api-test.cmiccloud.com/cmictest/pm-rest-api/rest/1/pmproject?finder=selectByJobCode;companyCode=001&fields=GrpmpCompCode,GrpmpProjCode,GrpmpProjName&limit=500&offset=0&orderBy=GrpmpProjCode

image.png

Audit Date Parameter

In some cases, there may be a need to continually query newly or updated rows from CMiC. This is when an Audit Date must be utilized.

An initial query is completed to retrieve all rows using the query parameters above, then on subsequent loads, a Audit Date finder filter is utilized to query the delta between the previous and current data pulls.

There are 4 considerations applied when using the Audit Date finder:

  1. Date supplied will query all data rows created or updated after this date. Using the IuCreateDate and IuUpdateDate attributes in the payload.
  2. The date format must be in YYYY-MM-DD if only date value is entered.
  3. If a date timestamp value is entered, it should be in YYYY-MM-DDT00:00:00-05:00, where the extension -05:00 is for setting the time zone.
  4. Audit Date finders are written specifically for each resource, so each one may be labelled differently. There may be some CMiC resources that support a different date finder. On further details on how to verify which date finder is supported please refer to Endpoint Introspection.
https://atlas-api-test.cmiccloud.com/cmictest/hcm-rest-api/rest/1/pyemployee?finder=findByDate;auditDate=2015-12-20

2dc5087-small-Capture.PNG