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

  • 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'];

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

  • 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


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

    $result = $report->run()


    global $result;

    //Set Supervisor ID variable

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

  • James B

    After 2 days of messing around in the bits of downtime I get, I seem to have a PHP code that can update a custom field in a contact record from a report. As others have said, the next logical step would be to implement this into a CPM. You can see it below with my breakdown:

    // Define variables
    $contact_id = $rows[0][0]->val;
    $contact = RightNow\Connect\v1_2\Contact::fetch($contact_id);
    $cert_flag = $rows[0][2]->val;
    // Update the contact with the value calculated within the Certificate field.
    if($cert_flag == "Yes") {
    $contact->CustomFields->c->cert_received = 1;
    For people that may come across this thread years down the road and are as clueless as me, I'll break this down a bit.
    The first half is where I define my variables. A variable in PHP is denoted by the use of a $ before a word and is not case sensitive. You also do not need to define the variable type as far as I know, as PHP treats everything as a variant.
    First I created the variable $contact_id and assigned it to the first column in my report. When dealing with two dimensional arrays within reporting, it is important to make sure that the row (the first value within the []) is always set to 0 unless you know what you're doing.
    Next I assign the contact record for that row to the $contact variable. I never would have figured this bit out on my own, but basically this line fetches the contact record we define in $contact_id, so when we want to update the contact we can just use this variable instead of the whole line of code.
    Finally, out of my own habits I created the variable $cert_flag for the certificate flag value found in the third column of the report. This makes it easier to reference later and is generally good practice in code.
    Moving onto the actual update part, I start by using an IF statement to check that the $cert_flag value in the third column is set to yes (in PHP using a == means you are checking. A single = means you are setting the value). This saves on processing time as the update code will not run if it doesn't need to.
    I define the contact field i want to update by following the chain using -> to keep digging to the area I want. It's almost like how file directories work. I did have to steal this bit from another forum post so I knew where I was going since I am unfamiliar with it.
    I tell the code to set this value to 1. Why? Because my field is a Yes/No type and needs a boolean input. 0 = No and 1 = Yes. I have no idea what No Value is though.
    Finally, we save the record and close the IF statement.
    For a PHP pro this is all painfully obvious, but I hope I have helped someone who is on the same level as me achieve something.
    Cheers to all those who have helped me in this thread.
  • James B

    I started looking into this soon after because updating from reports is something I've wanted to do for a long time. I managed to yoink some code and trying to get it to work but to no avail. I'm pretty much just stabbing in the dark here:


    // Define variables

    $contact_id = $rows[0][0] -> val;
    $contact = RightNow\Connect\v1_2\Contact::fetch($contact_id);

    // Update the contact with the value calculated within the Certificate field.

    $contact->CustomFields->c->cert_received = $rows[0][2]->val;

    I'll keep digging into this as I would much rather update a field with this on a schedule every night, and have the rules reference that, than use a report on the workspace.

  • James B

    My initial plan was to insert a non functioning icon into the workspace and control it with rules. But, with the field not accessible by either ruleset I couldn't do that so I went with this reporting option. It took a bit of fiddling but we managed to get it to work without the search function in there with the steps below (thanks to my TAM to helping out with this):

    I initially just had the filter on the contact ID. If this filter was hidden the report would force us to insert a value that would overwrite the workspace value.
    We tried fixing it by adding the incidents table to the report as the root table, joining it to contacts. This didn’t work.
    We then tried fixing it by adding incidents.i_id as a hidden field. This didn’t work.
    We then fixed it by changing the filter to look at incidents.i_id AND having the incident.i_id as a hidden field.

    The only thing that's annoying me now is the dead space below the result that comes with inserting reports into workspaces (see image). If anybody knows of how to get rid of that, please be my guest to help out. But functionally speaking, this is now working. I may revisit this with a CPM you mention in paragraph 3 though as that sounds wicked handy! If you know of any resources I could look at to learn CPMs I would very much appreciate it.


  • James B

    Hi Sebastiaan,

    The report pulls the contact ID automatically using the report behaviour settings so there is no need for making a CPM to do this. Which is good, since I have no idea how to even write a CPM! I've been meaning to learn but never had the time. I just don't want the filter section to be viewable at all and make everything as compact as possible.

    My original plan was to unhide a padlock icon if the certificate is present, but since the API is not exposed to that field I can't do so :(

  • James B

    I thought that would be the case, which is a real shame.

    I took a look at the report you mentioned and it does the job with a bit of tinkering. Not the most ideal way of displaying the data but probably the best that OSC can do without any BI add-ons.

    Thanks for your help :)

  • James B

    Advanced routing is not something I have looked into before but it looks like it will route incidents to teams, but it wont route to workspaces or allow me to manage what is displayed in a workspace.

    It's looking as if I need to go down the CPM route, which means a save has to happen on open in order to apply these rules anyway.

    It's either that, or an add-in.

    I'm really not seeing much point in custom fields on staff accounts at the moment laugh

  • James B

    The Windows 10 method seems to work for now so I will continue to use that method until it become inconvenient. Something is better than nothing at this point!

    Thanks all for the help.

  • James B

    I ran the Fiddler Cap, and the only thing that stood out was the following line that was repeated every so often:

    Unrecognized cipher - See

    At this point I'm not sure what it's trying to tell me.

  • James B

    Hi all,

    thanks for your responses.

    I checked the Config settings and the MOD_CP_DEVELOPMENT_ENABLED is indeed set to 'Yes'. I also tried changing the remote directory to see if that helped but to no avail.

    The error message I get is:
    Could not connect to server: No connection could be made because the target machine actively refused it. Connection failed.

    It may be our IT blocking it at a firewall level which is SUCH a pain and would pretty much be impossible to let through (I'm sure you're all familiar with the pain of getting things through all the red tape). I will try out the Windows way of doing things and see if that yields any results for me.

  • James B

    I believe you should be able to do this using standard queries and filters.

    Set your tasks.created filter to be relative to 0 days rounded (or whatever you like really. Just don't go bonkers). Make sure you put the tasks.created as a column in the report as well and set it to "Sort Descending" (highlight column > Design > Sort). After that all you need to do is limit the report to the first 5 rows. You can do this in the "Level" tab > Parameters ribbon > "Record Limit" and then Limit to 5 rows, and untick Per Page.

    You should be good to go. Attached is an example using incidents.created.

  • James B

    I can see the logic in your thinking so I tried to apply this and see what happened. Unfortunately setting up the filters that way, and then trying to count the transactions that are status change has no results for anyone. Zeros across the board, which is unusual!

    Using these filters I tried the following expressions:

    sum(if( 1, 1,0))
    sum(if(transactions.trans_type = 6, 1,0)) - as well as returning the incident ID and counting the distinct.

    But to no avail. If I am missing something let me know!

    In the meantime I will keep trying but I may have to resort to the dashboard option which is not what I wanted to do as I find them painful and sluggish.