Manage Collections

A collection resource represents a list of items, such as accounts, customers, or opportunities. You can sort, filter, and paginate your collection resources to make large result sets easier to use and consume. For instance, you might only want to select a subset of information from a customer collection resource, instead of the entire data set. You can manage data returned from a collection resource using the following actions:

Querying

Querying an API is easy: you simply need to know how to talk to your web server to get back the response you want. You can use the finder parameter supported by a resource to query records, or you can use the q parameter to query and filter a collection resource.

Using the finder Query Parameter

Use the finder parameter when you want to find a record based on specified criteria. The finder parameter is a predefined query associated with a resource. Use the supported finder names to return a record based on the criteria specified in the finder variables. For example, use the finder PrimaryKey, and the finder variable AssignmentId, to get a procurement agent with a specific assignment identifier, such as 20000000000587," which we've used in the following cURL command example:

curl -u username:password \
-X GET https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/procurementAgents?finder=PrimaryKey;AssignmentId=20000000000587\
-H 'Content-Type: application/vnd.oracle.adf.resourceitem+json'

Note:

To learn more about the supported finder names and finder variables for any resource, see the Request > Query Parameter > finder section for the resource's Get All method located in the Tasks section of this guide.

Using the q Query Parameter in REST Framework Version 2 and Later

If you've defined the REST framework as Version 2, the q query parameter uses the expanded expression syntax that supports RowMatch expressions for filtering collection resources. In Version 2 and later, requests that use query-by-example syntax that Version 1 supports return an error.

For example, the query expression separated by a semicolon, q=AssignmentId<=100000019149282;AgentId=100010034208241, returns an error in Version 2 and later. The RowMatch expression, q=AssignmentId<=100000019149282 or AgentId=100010034208241, is supported in Version 2 and later. The following are examples of advanced query syntax supported in Version 2 and later:

  • To test whether a field's value is in a list of possibilities, use the in operator:

    • ProcurementBUId in (458, 459, 460)
  • For relational comparisons, use between and not between:

    • AgentId between 1900 and 1950

    • AgentId not between 1900 and 1950

  • To create complex query expressions, use and and or, along with the matching set of parentheses:

    • (ManageOrdersAllowedFlag=false) or ((ProcurementBUId <= 400) and (AgentId between 1900 and 1950))

If you're using REST Framework Version 1, you can use query-by-example syntax in requests. However, note that query-by-example syntax doesn't work in Version 2 and later. To learn more about versions, see Setting the REST Framework Version.

The following table lists REST data types and the valid operators that you can use in query parameter strings. Note that the operators BETWEEN, NOT BETWEEN, IN, NOT IN, and the wildcard character % are available only in REST framework Version 2 and later.

REST Data Type Supported Operators for q Parameter
string
  • = (Equal to)

    .../procurementAgents?q=ProcurementBU = 'Vision'

  • <> (Not equal to)

    .../procurementAgents?q=ProcurementBU <> 'Vision'

  • LIKE (Like)

    .../procurementAgents?q=ProcurementBU LIKE 'Vi%'

    .../procurementAgents?q=ProcurementBU LIKE '%on'

    .../procurementAgents?q=UPPER(ProcurementBU) LIKE UPPER(''%io%')

  • NOT LIKE (Not like)

    .../procurementAgents?q=UPPER(ProcurementBU) NOT LIKE UPPER('%io%')

  • IN (In)

    .../procurementAgents?q=ProcurementBU IN ('Vision', 'Vision Germany')

  • NOT IN (Not in)

    .../procurementAgents?q=ProcurementBU NOT IN ('Vision', 'Vision Germany')

  • IS NULL (Is null)

    .../procurementAgents?q=ProcurementBU IS NULL

  • IS NOT NULL (Is not null)

    .../procurementAgents?q=ProcurementBU IS NOT NULL

boolean
  • = 'true' (true)

    .../procurementAgents?q=ManageOrdersAllowedFlag = 'true'

  • = 'false' (false)

    .../procurementAgents?q=ManageOrdersAllowedFlag = 'false'

  • <> 'true' (false)

    .../procurementAgents?q=ManageOrdersAllowedFlag <> 'true'

  • <> 'false' (true)

    .../procurementAgents?q=ManageOrdersAllowedFlag <> 'false'

  • = 'Y' (true)

    .../procurementAgents?q=StatusCode = 'Y'

  • = 'N' (false)

    .../procurementAgents?q=StatusCode = 'N'

  • = true (true)

    .../procurementAgents?q=ManageOrdersAllowedFlag = true

  • = false (false)

    .../procurementAgents?q=ManageOrdersAllowedFlag = false

number
  • = (Equal to)

    .../procurementAgents?q=ProcurementBUId= 458

  • <> (Not equal to)

    .../procurementAgents?q=ProcurementBUId <> 458

  • < (Less than)

    .../procurementAgents?q=ProcurementBUId < 911

  • <= (Less than or equal to)

    .../procurementAgents?q=ProcurementBUId <= 911

  • > (Greater than)

    .../procurementAgents?q=ProcurementBUId > 458

  • >= (Greater than or equal to)

    .../procurementAgents?q=ProcurementBUId >= 458

  • BETWEEN (Between)

    .../procurementAgents?q=ProcurementBUId BETWEEN 400 AND 900

  • NOT BETWEEN (Not between)

    .../procurementAgents?q=ProcurementBUId NOT BETWEEN 400 and 900

  • IN (In)

    .../procurementAgents?q=ProcurementBUId IN (458, 911)

  • NOT IN (Not in)

    .../procurementAgents?q=ProcurementBUId NOT IN (458, 911)

  • IS NULL (Is null)

    .../procurementAgents?q=ProcurementBUId IS NULL

  • NOT NULL (Not null)

    .../procurementAgents?q=ProcurementBUId IS NOT NULL

integer
  • = (Equal to)

    .../procurementAgents?q=ProcurementBUId = 458

  • <> (Not equal to)

    .../procurementAgents?q=ProcurementBUId <> 458

  • < (Less than)

    .../procurementAgents?q=ProcurementBUId < 911

  • <= (Less than or equal to)

    .../procurementAgents?q=ProcurementBUId <= 911

  • > (Greater than)

    .../procurementAgents?q=ProcurementBUId > 400

  • >= (Greater than or equal to)

    .../procurementAgents?q=ProcurementBUId >= 400

  • BETWEEN (Between)

    .../procurementAgents?q=ProcurementBUId BETWEEN 400 AND 900

  • NOT BETWEEN (Not between)

    .../procurementAgents?q=ProcurementBUId NOT BETWEEN 400 and 900

  • IN (In)

    .../procurementAgents?q=ProcurementBUId IN (458, 911)

  • NOT IN (Not in)

    .../procurementAgents?q=ProcurementBUId NOT IN (458, 911)

  • IS NULL (Is null)

    .../procurementAgents?q=ProcurementBUId IS NULL

  • NOT NULL (Not null)

    .../procurementAgents?q=ProcurementBUId IS NOT NULL

Paginating

Most resource collections need some kind of pagination. Without it, a simple search could return millions of records, bringing your network to a crawl. So, instead of receiving all the records of a collection resource, you can limit the number of records that are displayed on a page in the REST client response. To do so, set the following parameters in the request payload:

  • limit - to specify the paging size

  • offset - to specify the starting point from which the resources are returned

  • totalResults - set to true to include the total number of search records that match the query

Set the following fields in the response payload:

  • hasMore - set to true when there more records to be returned from the collection; set to false when the last set of records are retrieved from the collection

  • totalResults - set to the total number of search records

For example, the client runs a GET command on an opportunities resource. The server stores 100 opportunities and the current request returns only 25. To indicate that there are more records to retrieve, the server sets the read-only hasMore field to true.

Example Response Body

{
  "items": [
   .
   .
   .
  ],
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0,
  "links": [
    {
      .
				.
				.
    }
  ]
}

If you set the totalResults parameter to true in the request, the response includes the totalResults field. After retrieving all the records as indicated by the totalResults value, the server sets hasMore to false.

{
  "items": [
   .
   .
   .
  ],
  "totalResults": 100,
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0,
  "links": [
    {
	.
    .
	.
    }
  ]
}

When you specify the limit and offset parameters, the paginated result isn't ordered. To ensure consistent results, use the orderBy parameter to order the results. If you update the collection resource between paging requests, the records displayed in each page may vary.

Example Requests Records Returned
GET /procurementAgents?offset=10&limit=20 11 through 30
GET /procurementAgents/?q=AgentEmail='john.brown@example.com'*&limit=10 1 through 10
GET /procurementAgents/?q=AgentEmail='john.brown@example.com'*&offset=25 26 through 50
GET /procurementAgents?totalResults=true&limit=20 1 through 20

Response includes the record count that match the query.

Sorting

Sorting is another feature that makes it easier to work with data-heavy resources. You can sort items returned from a collection resource using the orderBy query parameter. To set the sorting sequence, use asc for ascending order and desc for descending order. The default sequencing order is asc. Items returned in the response payload are sorted in a case-sensitive order.

For example, to sort items according to AssignmentId in descending order, enter the command:

?orderBy=AssignmentId:desc

If you include multiple fields in the query parameter, the order in which you specify the fields determines the sorting order. For example, to sort items in ascending order of AssignmentId, and then sort according to ProcurementBUId in descending order, enter the command:

?orderBy=AssignmentId,ProcurementBUId:desc

You now know how to manage your collection resources!