Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    James B
    Pull Another Contact ID Based on Unique Custom FieldAnswered
    Topic posted February 12, 2019 by James BBlue Ribbon: 750+ Points 
    57 Views, 11 Comments
    Title:
    Pull Another Contact ID Based on Unique Custom Field
    Summary:
    VLookup within the same table
    Content:

    Hi all,

    I am looking to automate a regular upload that we do which grants a contact additional access to the customer portal if they have direct reports. We get a daily contact update file from our HR system into OSC and as part of this file it tells us the employee number of each contacts supervisor.

    I built a report that counts the amount of people reporting to the same person. However, in order to do updates via a custom script I need to get the contact ID of the supervisor. Basically a VLOOKUP from within the same table that takes the Supervisor_Worker_No field from the employee, matches it to the Employee_Num field and then gets the C_ID. From there I can work my expressions and write my update code.

    I have attached the definition (PDF and XML) so you can see my attempt at nabbing somebody else's code and trying to work it for my own needs. From what I understand I need to build an SQL query within the process tab, as I don't think there is a way to do this as standard.

    Cheers!

    Version:
    Oracle Service Cloud 17D
    Document:

    Best Comment

    Sebastiaan Draaisma

    The id for contact in ROQL is called ID so the code will probably have to be changed to:

    $s_emp_num = $rows[0][1]->val;

    //Run ROQL query
    $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT Contact.ID from Contact WHERE Contact.CustomFields.c.employee_num =".$s_emp_num )->next();

    //put the results in the report
    while($row =  $roql_result->next())
    {
    $rows[0][2]->val = $row['ID'];
    }

    Comment

     

    • James B

      I am revisiting this after a bit of time away from it and have come up with another solution that I need to learn how to code. I will see how it goes but for now, the plan is to use another report for the lookup instead of trying to query it directly in the PHP.

      So for now all I have is
      Initialization:

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

      //Fetch Lookup Report. Employee Worker Number is column 0, Contact ID is column 1
      $report = RNCPHP_my_report\AnalyticsReport::fetch(114872);

      $result = $report->run()

      Process:

      global $result;

      //Set Supervisor ID variable
      $s_id;

      //Get the supervisor Worker Number column
      $s_wn = $rows[0][1];

      //Get the lookup worker number column
      $slookup = $result -> $rows[0][0];

      //Somehow vlookup the $s_wn with $slookup, then return that rows column 1 as the $s_id

      I am still figuring this out so it may already be horribly wrong, but I think using a second report to lookup is the way to go. I shall keep this updated as I make progress.

    • Sebastiaan Draaisma

      Hi James.

      I don't have the same fields but try the following code in your Process tab

      $s_emp_num = $rows[0][1]->val;

      //Run ROQL query
      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT Contact.c_id from Contact WHERE Contact.CustomFields.c.employee_num =".$s_emp_num )->next();

      //put the results in the report
      while($row =  $roql_result->next())
      {
      $rows[0][2]->val = $row['c_id'];
      }

    • Sebastiaan Draaisma

      The id for contact in ROQL is called ID so the code will probably have to be changed to:

      $s_emp_num = $rows[0][1]->val;

      //Run ROQL query
      $roql_result = RightNow\Connect\v1_3\ROQL::query( "SELECT Contact.ID from Contact WHERE Contact.CustomFields.c.employee_num =".$s_emp_num )->next();

      //put the results in the report
      while($row =  $roql_result->next())
      {
      $rows[0][2]->val = $row['ID'];
      }

      • James B

        This looks promising. I've inserted the code with some slight amendments on the columns to use but its drawing a blank.

        I am not sure if it's due to the fact that the supervisor worker number is stored as a string, where the contact ID is an integer. Don't ask me why it was set up this way. It must be to do with the CRON export. When I wrap the column in a "to_number()" expression it turns this into something a bit funky, but it definitely seems to be close.

    • Sebastiaan Draaisma

      For the Initialisation you only need

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

    • Sebastiaan Draaisma

      Good question James.

      You convert your datatype to an integer first before assigning it to the variable
      $s_emp_num = (int)$rows[0][1]->val; //forces PHP to convert to integer

      I can do some more testing later tonight or tomorrow :-)

      • James B

        Thanks, this is a big help. I think the only reason why it's not matching is that the employee number it's trying to find also needs to be converted to an integer. I took the above example and tried it at the start of the ROQL query. It runs, doesn't seem to be right as the fourth column remains no value. Our data set seems to be quite awkward.

        $s_emp_num = (int)$rows[0][2]->val; 

        //Run ROQL query
        $roql_result = RightNow\Connect\v1_3\ROQL::query( (int)"SELECT Contact.ID from Contact WHERE Contact.CustomFields.c.employee_num =".$s_emp_num )->next();

        //put the results in the report
        while($row =  $roql_result->next())
        {
        $rows[0][3]->val = $row['ID'];
        }

    • Sebastiaan Draaisma

      Meant to write "You could convert your datatype to an integer first"
      (some issues with the forum curently which prevents users from editing their posts)

    • Sebastiaan Draaisma

      If both contacts.c$supervisor_worker_no and contacts.c$s_emp_num are strings then you don't need to convert. Then the equal sign should work.

      You could do some trouble shooting by using the Connect Object Explorer to see if your query gives you any results
      YOUR_SITE.custhelp.com/ci/admin/explorer/

      I did a quick test and created a custom (text) field for contacts with a default vaule of 1234 (string in custom field) and used the following ROQL query in the COE

      SELECT Contact.ID FROM Contact WHERE Contact.CustomFields.c.employee_num = 1234

      • James B

        I was wondering where the ROQL builder was. I found documentation that mentions the tool but never told me where to find it.

        I have been messing around for a while trying to figure this out without having my hand held, and finally managed to get it to run using the below, which basically breaks down your code into more steps.

        $s_emp_num = $rows[0][2]->val; 

        //Build ROQL query
        $query = "SELECT Contact.ID FROM Contact WHERE Contact.CustomFields.c.employee_num =".$s_emp_num;

        //Run Query
        $results = RightNow\Connect\v1_2\ROQL::query($query)->next();

        $row = $results->next();

        //Put results in report
        $rows[0][3]->val = $row['ID'];

        Initially this also did not work, until I randomly tried changing the version from v1_3 to v1_2 and BOOM! results :D

        I assume when we upgrade to version 19A or B (from 17D) this year, that we will start using V1_3.

        Thank you for helping with this. It's been a valuable learning experience and something I can keep building upon. I will select your initial answer as the best one since that code would have worked if I figured out the version just needed amending.