[narasikrishna]

We are using the following REST API to populate the drop down box which runs perfectly and gives output.

api/1.0/{workspace}/project/{prj_uid}/process-variable/{var_name}/execute-query-suggest

But when we run the same in the text box variable it gives the following the error.

Bad Request: It is not possible to execute the query. Please contact your system administrator.

The following query is used in the option SQL
select ifnull(max(reqid),0)+1 as ReqID from tablename;

If anyone knows about the issues please suggest us how to run the sql inside the variable

[amosbatto]

That endpoint only works for populating a list of options. I have requested an endpoint to execute SQL queries but it has never been implemented. For now, the only solution is to create a trigger in your process which calls executeQuery() and stores the result in a variable, then call another endpoint to get the value stored in the variable. Your trigger code would be something like this:

Code: Select all

$sql = "SELECT X FROM Y WHERE Z='something' ";
$aResult = executeQuery($sql);
if (isset($aResult) and is_array($aResult)) {
   @@result = $aResult[1]['X'];
}

Then your code to call the endpoints would be something like this:

Code: Select all

$caseId = '55519601555ba4b4bd5a3c9097861254';
$triggerId = '37823572555ba5891041136049273220';
$url = "/api/1.0/workflow/cases/$caseId/execute-trigger/$triggerId";
$method = "PUT";
$oRet = pmRestRequest($method, $url);
if ($oRet->status == 200) {
   $url = "/api/1.0/workflow/cases/$caseId/variables";
   $varRet = pmRestRequest("GET", $url);
   
   if ($varRet == 200) {
      $sqlResult = $varRet->response->result;
   }
} 

[nateshnp]

Hi amosbatto,

We have done what you have suggested and it works perfectly fine for text box. What can we do for dropdown?

Best Regards,
Natesh

[amosbatto]

Do you want to populate a dropdown box?

Here is an example trigger:

Code: Select all

$sql = "SELECT ID, NAME FROM CLIENTS WHERE TYPE='CONTRACTED' ";
@@Clients = executeQuery($sql);

Then your code to call the endpoints would be something like this:

Code: Select all

$caseId = '55519601555ba4b4bd5a3c9097861254';
$triggerId = '37823572555ba5891041136049273220';
$url = "/api/1.0/workflow/cases/$caseId/execute-trigger/$triggerId";
$method = "PUT";
$oRet = pmRestRequest($method, $url);
if ($oRet->status == 200) {
   $url = "/api/1.0/workflow/cases/$caseId/variables";
   $varRet = pmRestRequest("GET", $url);
   
   if ($varRet == 200 and !empty($varRet->response)) {
         //convert from stdClass object to array:
         $aClients = get_object_vars($varRet->response->Clients);
         foreach($aClients as $aClient) {
           var opt = document.createElement("OPTION");
           opt.text = $aClient['NAME'];
           opt.value = $aClient['ID'];
           document.getElementById("form[MyDropdown]").options.add(opt);
         }
   }
} 

If inside a DynaForm, then "MyDropdown" is the ID of the dropdown field. If inside an external web page, then don't include form[...] in the ID:
document.getElementById("MyDropdown").options.add(opt);