This article is a continuation of those related to use the RESTful web services introduced in Release 10. Here we're looking at the features for querying records, with examples based on Oracle Sales Cloud.
Note that a browser will HTTP encode the request URL, including characters (and spaces) with the parameters, whereas your client program will need to do this itself. We've left them as non-encoded browser requests for clarity in this article.
Use the list of "Resource Attributes" from the documentation to query by attribute. To use simple queries the request URL needs to have the pattern https://[Endpoint][Resource]/q=[AttributeName]=[Value]. For example the following queries Account records by their name:
As another example, the following queries the Resource (sales user) object records based on a specific email address:
For multiple values in your queries you can just comma separate them. Additionally you can use AND / OR in the value list as with this example for contact owners:
If you've got custom fields on your objects, and you've checked the box to include them in the service payloads, then you can query them. In this example it will return all Opportunity records which have something (i.e. not null) in a custom field
The following is a list of the characters and reserve words to use in queries:
Note that it is not possible to submit complex multiple criteria queries using the simple "q=" process. As such look at the alternatives below.
This is a simple extension of the query feature above. For example, the following queries Contacts object records where the name starts with Grande:
In the same way, the following queries all Contacts record by the Account Name using the LIKE operator:
1) The Account Recordset
Only available for the Accounts object at this time, this predefined query allows the return of values that equates to the most commonly used group of records.
In our system this returns an array of 5 Account items (records) with detail on each.
And to confirm the same business user has the same in their My Accounts page:
2) Key-based Finders
All REST resources have Finders, most of which use Primary Key ID values as quick ways to look up records. These are used internally, and it's subjective how useful this might be when building your own integrations. Here are a couple of examples
3) Finders on the Activities object
To help integration with task and appointment records, there is an extensive set of finders available for querying these records. Here is a list with the parameters they accept.
GetListOfCallReportsForPastAppt - Finds call reports related to a past appointment.
MyActivitiesAsResource - Finds activities where a specified user is a resource.
CompletedActivities - Finds activities in a completed status.
GetListOfCallReports - Finds call reports.
MyTasks - Finds tasks owned by a specified user.
GetActivitiesByActivityNumber - Finds activities with the specified activity number.
Here are a few examples of the above Finders, to illustrate their usage. First let's look at the OpenActivities finder.
Notice the format here. The finder name followed by a semi-colon (encoded is %3b) with the parameter and value. Ensure you use the right format for values (e.g. YYYY-MM-DD for date format).
Notice there are two parameters with the comma separator (%2C). Notice this is the appointments for the current user (not all) and the values might be null else the rows will not be returned (i.e. if no Close Date set).
All REST resource record items have links to related LOVs and their child records. These links can be useful to get data for an integrated application to ensure the same values are used and displayed at all times between apps.
Here is an example, and you'll notice whilst these are seeded values you need to get them in the context of a specific record (contact number 35076 in this case):
The following represents a few common mistakes we found when using these features. Whilst not complex, hopefully they will help catch a few issues.
For reference also look at the whitepaper entitled Oracle Sales Cloud Using RESTful Web Services in MyOracle Support Note 1981941.1.