Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Joseph Mastriano
    API Field Names For REST Service Using ROQLAnswered
    Topic posted May 9, 2019 by Joseph MastrianoGreen Ribbon: 100+ Points, tagged REST 
    114 Views, 7 Comments
    Title:
    API Field Names For REST Service Using ROQL
    Summary:
    Need to find out API Field names to use in REST calls using ROQL
    Content:

    Hello, I am looking to find out where I find the fields names to using in REST API service using ROQL.  In the example below which was posted in another post where would I find a emails.address?  Basically if I'm going to use ROQL I am looking to find out where I get those field names to use in my query.

    https://site/services/rest/connect/latest/contacts/?q=emails.address='user@company.com'

    Thanks!

    Joe

    Best Comment

    Sebastiaan Draaisma

    Yes you can.
    Depending on which field you want to use you could create an alias. See an example of this below:

    $incident_id = $rows[0][0]->val;

    $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT Incident.ReferenceNumber, Incident.StatusWithType.Status.LookupName as Status FROM Incident WHERE Incident.ID =".$incident_id )->next();
    while($data =  $roql_result->next())
    {
    $rows[0][1]->val = $data['ReferenceNumber'];
    $rows[0][2]->val = $data['Status'];
    }

    In this example: Incident.StatusWithType.Status.LookupName
    Will be converted to an alias (a variable if you would like)

    Incident.StatusWithType.Status.LookupName as Status

    Status is the new name and equals Incident.StatusWithType.Status.LookupName

    This can then be used in the result set
    $rows[0][2]->val = $data['Status'];

    Comment

     

    • Lakshay Bhalla

      Hi Joseph,

      Look at this Documentation, It might help:

      https://docs.oracle.com/en/cloud/saas/service/18a/cxsvc/api-contacts.html

      Thanks

    • Joseph Mastriano

      Hi Lakshay,

      That link to the documentation is api documentation that accesses Contact, however most of it is by id.  We will not know the id so using the ?q=emails.address='user@company.com' will allow a search using the email.address. 

      However, I have no idea as to where to find the actual fields in the query I can use to search contact.  So for instance if I want to search by a contact phone, or address how do I know what the target fields are?

      Where do I get that level of information?

      Thanks!

      Joe

       

    • Sebastiaan Draaisma

      Hi Joe.

      You can use the CCOM explorer which has most fields (not all of them).
      https://YOURSITE.custhelp.com/ci/admin/explorer/

      All fields can be found in the Common Object section inside the user doc for Connect PHP
      http://documentation.custhelp.com/euf/assets/devdocs/cloud19a/Connect_PHP/Default.htm

      As for the ROQL query, try the LIKE operator with the % wildcard
      SELECT Contact.Name.First, Contact.Name.Last, Emails.EmailList.Address FROM Contact WHERE Emails.EmailList.Address LIKE '%cgi.com'

       

      • Joseph Mastriano

        Thanks Sebastiaan!  This looks to be what I was looking for.

        With the ROQL query you posted, do you know if this can this be appended to the end of a REST call to return results? 

        Joe

    • Sebastiaan Draaisma

      Yes you can.
      Depending on which field you want to use you could create an alias. See an example of this below:

      $incident_id = $rows[0][0]->val;

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT Incident.ReferenceNumber, Incident.StatusWithType.Status.LookupName as Status FROM Incident WHERE Incident.ID =".$incident_id )->next();
      while($data =  $roql_result->next())
      {
      $rows[0][1]->val = $data['ReferenceNumber'];
      $rows[0][2]->val = $data['Status'];
      }

      In this example: Incident.StatusWithType.Status.LookupName
      Will be converted to an alias (a variable if you would like)

      Incident.StatusWithType.Status.LookupName as Status

      Status is the new name and equals Incident.StatusWithType.Status.LookupName

      This can then be used in the result set
      $rows[0][2]->val = $data['Status'];