X

It's All About the Platform.

Querying Records using RESTful Web Services

Richard Bingham
Senior Development Manager

Introduction

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.

 

Querying Records

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:

 

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/accounts/?q=OrganizationName=Sundries Supplies

 

As another example, the following queries the Resource (sales user) object records based on a specific email address:

 

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/resources/?q=EmailAddress=richard.bingham@oracle.com

 

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:

 

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/contacts/?q=OwnerName='Oliver Steinmeier' OR 'Richard Bingham'

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

 

  • https://crm-MyServer.oracleoutsourcing.com/salesApi/resources/latest/opportunities/?q=rb_Reference_c!=null 

The following is a list of the characters and reserve words to use in queries:

  • Supported operators are: >, <, >=, !=, AND, OR, =, LIKE
  • Single quotes can be used to define literal values
  • Escape character is \
  • Wildcard character is * (see below)

Note that it is not possible to submit complex multiple criteria queries using the simple "q=" process. As such look at the alternatives below.

 

Using Wildcards

This is a simple extension of the query feature above. For example, the following queries Contacts object records where the name starts with Grande:

 

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/contacts/?q=ContactName LIKE Grande*

 

In the same way, the following queries all Contacts record by the Account Name using the LIKE operator:

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/contacts/?q=AccountName LIKE Big*
Note that in the response array (after the items[ ]) you'll find some metadata that includes a count value - showing two records returned in this example.

Pre-packaged Queries 

Additionally there are some pre-packaged queries you can use to issue more complex statements. These all return an array of the related object records.


 

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.

 

  • https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/accounts/?q=RecordSet MYACCTS

 

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

 

  • Look up Resource (users) records by primary key: https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/resources/?finder=PrimaryKey;ResourceProfileId=300000002522027
  • Look up Contacts for an account by the Contact PartyId:  https://crm-MyServer.oracleoutsourcing.com/crmCommonApi/resources/latest/contacts/?finder=PrimaryKey;PartyId=300000003709027

 

 

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.

  • Parameter: ParentActivityIdAttr is a string for Activity ID of the past appointment
MyAppointmentsInDateRange - Finds appointments within a data range.
  • Parameter StartDtRFAttr - string of the appointment start date.
  • Parameter EndDtRFAttr - string the appointment end date.

MyActivitiesAsResource - Finds activities where a specified user is a resource.

  • Parameter Bind_UserResourceId an integer of the resource ID of the user.

CompletedActivities - Finds activities in a completed status.

  • Parameter Bind_CurrentDate a string of the date used to locate completed activities.

GetListOfCallReports - Finds call reports.

  • Parameter LeadIdAttr an String of the lead ID related to the call reports.
  • Parameter StartDtRFAttr an String of the start date of the call report.
  • Parameter OpportunityIdAttr an String of the opportunity ID related to the call reports.
  • Parameter EndDtRFAttr an string of the end date of the call report.
  • Parameter AccountIdAttr an integer of the account ID related to the call reports.

MyTasks - Finds tasks owned by a specified user.

  • Parameter Bind_LoggedInUserId_BV an integer of the ID of the user who is logged into the application.
OpenActivities - Finds all open activities.
  • Parameter Bind_CurrentDate a string of the date used to locate current activities.

GetActivitiesByActivityNumber - Finds activities with the specified activity number.

  • Parameter ActivityNumber a string of the activity number use to locate the activity.

Finder Examples

Here are a few examples of the above Finders, to illustrate their usage. First let's look at the OpenActivities finder.

  •  https://crm-MyServer.oracleoutsourcing.com/salesApi/resources/latest/activities/?finder=OpenActivities;Bind_CurrentDate=2010-01-01

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). 


Here is another example, showing the MyAppointments based on start and end dates.




 

 

  • https://crm-MyServer.oracleoutsourcing.com/salesApi/resources/latest/activities/?finder=MyAppointmentsInDateRange;StartDtRFAttr=2013-08-11,EndDtRFAttr=2016-01-01

 

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).

 

Using the Links from a Record

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):

  • Child: https://crm-MyServer.oracleoutsourcing.com:443/crmCommonApi/resources/11.1.10/contacts/35076/child/Address
  • LOV: https://crm-MyServer.oracleoutsourcing.com:443/crmCommonApi/resources/11.1.10/contacts/35076/lov/GenderLookupLOV

Troubleshooting

The following represents a few common mistakes we found when using these features. Whilst not complex, hopefully they will help catch a few issues. 

 

  • If your request returns no data (as below) - and you're sure it should - check that your user session has not simply timed-out.
  • Also ensure there are no typos in the URL.
  • Ensure parameter values are accurate.
  • Ensure the user you are using has Data Security access to the expected rows.
  • Note all finders require you to include ALL the parameters with values.

For reference also look at the whitepaper entitled Oracle Sales Cloud Using RESTful Web Services in MyOracle Support Note 1981941.1.

 

Join the discussion

Comments ( 1 )
  • Jorge Avila Monday, September 17, 2018
    Hello Richard.

    Still can you provide me advice on this article?

    Best Regards
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.