Integrations and APIs for Service

Get Involved. Join the Conversation.


    Iain McKay
    SOAP and ROQL: SELECT by Contact Email Address - How ?
    Topic posted September 14, 2010 by Iain McKaySilver Medal: 2,000+ Points, last edited October 29, 2011 
    8240 Views, 37 Comments
    SOAP and ROQL: SELECT by Contact Email Address - How ?



    While the following ROQL Query syntax in my C# app works:

    String queryString = "SELECT O.Contacts FROM Contact C WHERE Contact.Name.First = 'Iain' AND Contact.Name.Last='Mckay' ;";

    BUT, the following does not:

    String queryString = " SELECT Contact FROM Contact WHERE Contact.Emails[0].Address = '' ;";

    Any thoughts ? Am I not understanding the Contact Object model correctly ? As when I do a Writeline of the contacts, as returned in Query 1 above, then contact.Emails[0].Address works fine.



    Code Snippet:


    • Walter Kamp

      Not sure if this works the same, but when I query my ODBC connection I can use something like this:

      SELECT * FROM database.contacts c WHERE = '';


    • Iain McKay
      Thanks, I can do all that in plain SQL, fine. But it's ROQL I'm trying to use here and the SOAP API. Thanks for taking the time to respond.
    • Walter Kamp

      Ah, we'll have to upgrade first to get this functionality.

      Hopefully RightNow knows the answer.

    • Iain McKay
      update: I can of course work around by querying against Contact.Login, i.e.: String queryString = "SELECT Contact FROM Contact WHERE Contact.Login = '' ;"; This works fine. Any ideas how to query against an email address ? - nothing to do with RN appending a ".invalid" to email addresses internally ? Iain.
    • Walter Kamp

      Can you try Contact.Emails[0].addr instead of Contact.Emails[0].Address?
      I see that in some of the default RN php files...

    • Chris Omland

      You use the name of fields to traverse down, so it should look something like:

      SELECT Contact FROM Contact C Where C.Emails.EmailList.Address = ‘’

    • Iain McKay

      Thanks Chris, but that still returns 0 rows.

      Does their not need to be a '0' in there to give the ordinal position in the list ?


      As I said above, I can use Contact.Login

    • Iain McKay

      Sorry, that was a bit prematurely abrupt. Finger trouble.

      Thanks for your assistance anyway.

    • Chris Omland

      So the query is working or is not?

    • Iain McKay

      Hi Chris, as above, no it's not, still returning zero rows, even when searching for [testing against an --upgrade site which has had most emails appended with '.invalid'].

    • Ryan McCullough

      Walter, just to clarify, Iain is asking a question about ROQL which is our new query language for the Connect Web Services API.

      It looks like your examples are direct SQL or ODBC SQL which is not compatible with ROQL.

    • Chris Omland

      The specified query should work, are you sure a contact with that email exists in the system? I just tried the query on my test site and it returned data as expected. Can you find that contact by email in the agent desktop?

    • Iain McKay

      Hi Chris,

      Thanks for testing on your test site.

      I can confirm that the CX desktop DOES find that contact by email under the Contact Quick Search.

      this works:

      String queryString = " SELECT Contact FROM Contact WHERE Contact.Login = '' ;";



      this (with no semi colons around the email address string) gives 0 rows back, but no exception:

      String queryString = "SELECT Contact FROM Contact C Where C.Emails.EmailList.Address = ;";


      this (with semicolons around the address string) gives a FaultException with 'RightNow unexpected internal problem':

      String queryString = "SELECT Contact FROM Contact C Where C.Emails.EmailList.Address = '' ;";


      I have attached a screenshot of the console progra moutput, showing that the email does exist in the returned object Contact.Emails[0].Address.

      I will attach the source in a following entry.



    • Chris Omland

      Iain, I deleted your last post, the source code contained credentials which you should not post here. I have scrubbed the source file and reposted here.

    • Iain McKay

      Oops, good spot. Will put that in an included file one day!




      Update: Issue 1: Still ongoing. can't seahc by email address as suggested syntax SHOULD work.


      Update: Issue 2: Got incidents per contact OK, fro mthe following ROQL in a QueryCSV query:


      "SELECT Contact.IncidentsWithThreads.* FROM Contact C WHERE C.ID=" + longContactID ;