Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Sebastiaan Draaisma
    ROQL query on Profile table in report
    Topic posted December 10, 2018 by Sebastiaan DraaismaBlack Diamond: 60,000+ Points, tagged Connect PHP 
    124 Views, 12 Comments
    Title:
    ROQL query on Profile table in report
    Content:

    Hi there.

    I'm trying to get a ROQL query on the Profile table in a report.
    I found the following documentation

    The following example shows a ROQL query on the Profile managed table:
    SELECT * FROM Profile; DESCRIBE Profile;

    I tried the following custom script inside a report (report also attached)

    Initialisation
    require_once(get_cfg_var('doc_root')."/ConnectPHP/Connect_init.php");
    initConnectAPI();
     

    Process
    $profile_id = $rows[0][0]->val;

    //build the ROQL query using the profile id
    $query = "SELECT P.CreatedTime FROM Profile P WHERE P.ID=".$profile_id;

    //run the query
    $query_results = RightNow\Connect\v1_2\ROQL::query($query)->next();

    $row = $query_results->next();

    //put the results in the report
    $rows[0][1]->val = "Created ".$row['CreatedTime'];

     

    I don't get any result...
    (the code does work when performing a query on the incident table)

    I'm not sure if I'm missing something? smiley

    Image:

    Comment

     

    • Suresh Thirukoti

      Sebastiaan - Try putitng v1_4 in RNCPHP Alias ........ also note that Managed tables querying are bit troublesome ....Try the query like this Select CreatedTime from Profile where ID = {}

      ~Surehs

    • Sebastiaan Draaisma

      Hi Suresh

      I changed the code in the process tab without any progress...

      Process
      $profile_id = $rows[0][0]->val;

      //build the ROQL query using the profile id
      $query = "Select CreatedTime from Profile where ID = ".$profile_id;


      //run the query
      $query_results = RightNow\Connect\v1_4\ROQL::query($query)->next();

      $row = $query_results->next();

      //put the results in the report
      $rows[0][1]->val = "Created ".$row['CreatedTime'];

    • Suresh Thirukoti

      Aah....I dont have 18D system to test...Sebastiaan ....1 alternative approach is to have Profiles table which is exposed to Analytics but it doesnt have CreatedTime.....so I will leave to any 18D'ers to pitch in here :-) 

       

      Suresh

      • Sebastiaan Draaisma

        Yeah. that was the same issue I was experiencing... The data in analytics is not sufficient. Especially when creating system documentation for the client. There is so much more available in the data dictionary. I hope we will have access to more audit data in future releases. The ROQL data would have been a welcome addition.

        Have fun in the UK, talk to you soon :-)

    • Vlad

      Hey Sebastiaan,

      This works fine for me:

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT C.CreatedTime from Contact C WHERE ID=1" )->next();
      while($contact =  $roql_result->next())
      {
      $rows[0][1]->val = "Created: " . $contact['CreatedTime'];
      }
      So does this:
      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT P.Name from Profile P WHERE ID=2" )->next();
      while($profile =  $roql_result->next())
      {
      $rows[0][1]->val = "Name: " . $profile['Name'];
      }
      However this does not work:
      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT P.CreatedTime from Profile P WHERE ID=2" )->next();
      while($profile =  $roql_result->next())
      {
      $rows[0][1]->val = "Created: " . $profile['CreatedTime'];
      }
      Can you replicate my results?
    • Sebastiaan Draaisma

      Hi Vlad.

      Yes, strangely enough the last one doesn't work while the first 2 do work.

      This one works for me:

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

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT P.Name from Profile P WHERE ID=".$profile_id )->next();
      while($profile =  $roql_result->next())
      {
      $rows[0][1]->val = $profile['Name'];
      }

      This one also works:

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

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT P.CreatedTime from Contact P WHERE ID=".$profile_id )->next();
      while($profile =  $roql_result->next())
      {
      $rows[0][1]->val = $profile['CreatedTime'];
      }

      But this one doesn't

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

      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT P.CreatedTime from Profile P WHERE ID=".$profile_id )->next();
      while($profile =  $roql_result->next())
      {
      $rows[0][1]->val = $profile['CreatedTime'];
      }

      This looks like a bug (since the documentation mentions CreatedTime and UpdatedTime can be used, I also tried UpdatedTime)

    • Vlad

      Based on the documentation, I agree it should work. You may want to submit a service request with tech support. Make sure to reference this thread if you do please.

      I managed to reproduce this outside the report script, in a standalone custom script:

      <?php
       
      if (!defined('DOCROOT')) {
      $docroot = get_cfg_var('doc_root');
      define('DOCROOT', $docroot);
      }
      require_once (DOCROOT . '/include/services/AgentAuthenticator.phph');
      $account = AgentAuthenticator::authenticateCredentials("username","password");
       
      use RightNow\Connect\v1_4 as RNCPHP;
      RNCPHP\ConnectAPI::getCurrentContext()->ApplicationContext = "ROQL";
       
      try {
       
      $roql_result = RNCPHP\ROQL::query( "SELECT C.CreatedTime from Contact C WHERE ID=1" )->next();
      while($contact =  $roql_result->next())
      {
      echo "Contact CreatedTime: " . $contact['CreatedTime'] . "<br>";
      }
       
      $roql_result = RNCPHP\ROQL::query( "SELECT P.Name from Profile P WHERE ID=2" )->next();
      while($profile =  $roql_result->next())
      {
      echo "Profile Name: " . $profile['Name'] . "<br>";
      }
      $roql_result = RNCPHP\ROQL::query( "SELECT P.CreatedTime from Profile P WHERE ID=2" )->next();
      while($profile =  $roql_result->next())
      {
      echo "Profile CreatedTime: " . $profile['CreatedTime'] . "<br>";
      }
      }
      catch (RNCPHP\ConnectAPIError $err)
      {
          echo $err->getMessage();
      }
      catch(Exception $err) 
      {
          echo $err->getMessage();
      }
    • Rajan Davis

      Hi Sebastiaan,

      I could be mistaken, but I think that Profiles have a createdTime attribute but it's always null or not stored on the system.

      For example, if I run "SELECT id, createdTime from profiles" against the REST API, I get the following results:

      [
        {
          "createdTime": "null",
          "id": 1
        },
        {
          "createdTime": "null",
          "id": 2
        },
        {
          "createdTime": "null",
          "id": 3
        },
        {
          "createdTime": "null",
          "id": 5
        },
        {
          "createdTime": "null",
          "id": 7
        },
        {
          "createdTime": "null",
          "id": 11
        },
        {
          "createdTime": "null",
          "id": 13
        },
        {
          "createdTime": "null",
          "id": 14
        },
        {
          "createdTime": "null",
          "id": 16
        },
        {
          "createdTime": "null",
          "id": 18
        },
        {
          "createdTime": "null",
          "id": 19
        },
        {
          "createdTime": "null",
          "id": 20
        },
        {
          "createdTime": "null",
          "id": 23
        },
        {
          "createdTime": "null",
          "id": 26
        },
        {
          "createdTime": "null",
          "id": 27
        },
        {
          "createdTime": "null",
          "id": 28
        },
        {
          "createdTime": "null",
          "id": 32
        },
        {
          "createdTime": "null",
          "id": 33
        },
        {
          "createdTime": "null",
          "id": 35
        },
        {
          "createdTime": "null",
          "id": 36
        }
      ]
      

      What I have noticed is that if there isn't a Date Created attribute associated with a given object from the reports, querying for a date created time almost always returns null or "No Value".

      What's really interesting is that if you go into an individual profile, you can run an audit log and see when a given Profile is created and edited; however, there are no public reports that expose Profile data for when a profile has been created or updated. If you try to see the available associated tables for transactions, Profiles are not available.

      So I think you might be out of luck... hopefully I am wrong.

      Kind Regards,

      Rajan

    • Sebastiaan Draaisma

      Yeah, I noticed the same thing. Also for Accounts and other fields. I wish we had more audit tables available in analytics (the ones hidden from us). To be honest I have never understood the purpose of limiting access to loggs.

    • Vlad

      If you look at the Accounts.CreatedTime field in ConnectPHP Connect Object Model, it's listed as "Not Available" on Get. So that is working as designed. Profile.CreatedTime is listed as read-only in the documentation, this is being investigated in the service request you submitted Sebastiaan.