Best Practice | Data Filtering requirements

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:

GET 
?finder=rowfinderName;attr1=value1,attr2=value2,...

Example, request using the finder attribute;

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

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

Pagination

Limits and Offset

When working with ADF Business Components (ADF BC) REST APIs, pagination is essential for building efficient and scalable integrations.

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

totalResults parameter

Another helpful feature in this process is the totalResults query parameter, but it’s important to use it correctly to avoid unnecessary performance impact.

The totalResults parameter tells the API to return the total number of records available for a given query, in addition to the current page of results. This is especially useful when you need to:

• Display total record counts in a UI
• Calculate the number of pages required
• Plan batch or incremental processing

Only use totalResults on the first API call of a query sequence.

The initial request is typically where you need visibility into how many records exist overall. Once that information is known, it can be reused for subsequent pagination calls.

GET /pm-rest-api/rest/1/pmproject?limit=100&offset=0&totalResults=true

This call returns:

• The first page of data
• The total number of matching records

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

Starting with Nexus 1, CMiC APIs also support orderBy=default, which automatically applies the default sort order defined in the underlying database view. This simplifies sorting configuration and helps ensure consistent and stable pagination results across API calls, without requiring integrators to explicitly specify sort columns.

We recommend using orderBy=default in combination with pagination and filtering parameters (such as limit, offset, q, and totalResults) to maintain a well-performing and predictable integration.

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