Search for Groups and their Users in a Dynaform

From pmusers
Jump to: navigation, search

The following example shows how to select users in groups in a DynaForm. It shows how to enter a search string to find groups and also display the users in those groups.

Some process designers might want to select a particular group in a dropdown box and then select a user from that selected group in a second dropdown box. This example shows how to do that with two dropdown boxes which are dependent fields.

Other process designers might want to display all the found groups and their users in a grid. This example also shows how to do that using a text field which uses a dependent field query. JavaScript is used to execute the REST endpoint to execute the SQL query. It then populates the grid with the results from the database SQL query.


Dynaform: Search for groups and users.json (right click and select Save Link As)
Author: Amos Batto (amos[at]processmaker[dot]com)
Version: 1.0 (2012-03-07)
Tested in: PM 3.3.4 Enterprise in Debian 9.5 and Firefox 60.5


Create the following Dynaform:

SearchGroupsAndUsersInDesigner.png

This Dynaform contains the following fields:

  • Text box with the ID "searchGroup" where the user searches for group names
  • Dropdown box with the ID "selectGroup" which is a dependent field that displays the results from the search. When the user selects a group in this dropdown, then it will display the list of available users in the "selectUser" dropdown.
  • Dropdown box with the ID "selectUser" which is a dependent field that displays the list of users in a selected group.
  • Grid with the ID "usersList" which displays the groups and their users from the search. It contains the following fields:
    • Checkbox with the ID "select",
    • Text box with the ID "group",
    • Text box with the ID "user"
  • Submit button
  • Text box with the ID "searchResults" which is used for the SQL query to populate the "usersList" grid. This field is hidden with JavaScript.

The "selectGroup" uses the following SQL query to populate the list of groups which were found that contain the text entered in the "searchGroups" text field:

SELECT DISTINCT CON_ID AS GRP_UID, CON_VALUE AS GRP_NAME FROM CONTENT 
WHERE CON_CATEGORY='GRP_TITLE' AND CON_VALUE LIKE "%@#searchGroup%"

The query uses % wildcards so the string can appear in the middle of the group's title. The query is case sensitive.

The "selectUser" uses the following SQL query to populate the list of users in the selected group in the "selectGroup" dropdown box:

SELECT DISTINCT U.USR_UID, CONCAT(U.USR_FIRSTNAME, ' ', U.USR_LASTNAME) FROM GROUP_USER AS GU, USERS AS U 
WHERE GU.GRP_UID=@@selectGroup AND GU.USR_UID=U.USR_UID ORDER BY CONCAT(U.USR_FIRSTNAME, ' ', U.USR_LASTNAME)

This is all that is required to display the search results in the two dependent dropdown boxes. Displaying the search results in a grid is significantly more complicated. It requires creating not only the grid, but also the additional "searchResults" text field to execute the SQL query.

In the "searchResults" text field, add the following SQL query to obtain the group name and the full name of the users in the groups:

SELECT C.CON_VALUE AS GRP_NAME, CONCAT(U.USR_FIRSTNAME, ' ', U.USR_LASTNAME) AS USER_FULL_NAME 
FROM CONTENT AS C, GROUP_USER AS GU, USERS AS U 
WHERE C.CON_CATEGORY='GRP_TITLE' AND C.CON_VALUE LIKE "%@#searchGroup%" AND C.CON_ID=GU.GRP_UID AND GU.USR_UID=U.USR_UID 
ORDER BY C.CON_VALUE, CONCAT(U.USR_FIRSTNAME, ' ', U.USR_LASTNAME)

Then, add the following JavaScript to the form:

$("#searchResults").hide();

var host      = PMDynaform.getHostName();             // like "http://example.com:8080"
var workspace = PMDynaform.getWorkspaceName();        // get the current workspace
var token     = PMDynaform.getAccessToken();          // get the access token for REST
var caseId    = PMDynaform.getProjectKeys().caseUID;  
var projectId = PMDynaform.getProjectKeys().processUID;
var formId    = PMDynaform.getProjectKeys().formUID ? PMDynaform.getProjectKeys().formUID : $("form").prop("id");

$("#searchGroup").setOnchange( function(newVal, oldVal) {
    var oParams = {
      // REST endpoint:
      url: host+"/api/1.0/"+workspace+"/project/"+projectId+"/process-variable/queryResults/execute-query", 
      data: JSON.stringify( {
        "app_uid":   caseId, 
        "del_index": null,
        "dyn_uid":   formId,
        "field_id":  "searchResults", 
        "searchGroup": newVal
      } ),                        
      type: "POST",
      contentType: "application/json",
      beforeSend: function(xhr) {
        xhr.setRequestHeader('Authorization', 'Bearer '+token); // Header with access token
      },
      success: function(aRecords, status, error) {
        if (status == "success") {
          //delete the existing grid rows:
          var oGrid = $("#usersList"); //set to the ID of the grid
          var nRows = oGrid.getNumberRows();
          if (nRows) {
            for (var i = nRows; i > 0; i--) {
              oGrid.deleteRow(i);
            }
          }
          //repopulate the grid:
          for (i in aRecords) {
            var aRow = [
              { value: 0                 }, //"select" checkbox
              { value: aRecords[i].value }, //"group" text field
              { value: aRecords[i].text  }  //"user" text field
            ];
            oGrid.addRow(aRow);
          }
        }
        else {
          alert(status+": "+error);
        }
      },
      error: function(xhr, status, error) {
        alert(status+": "+error);
      }
    }
    
    $.ajax(oParams);   
});

This code uses the PMDynaform.getProcessKeys() function to obtain the ID of the process, which is needed to execute the following REST endpoint:

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

The code sets an event handler to execute when the user enters a new search string in the "searchGroups" text box. The control.setOnchange() event handler uses jQuery's $.ajax() function to call the execute-query REST endpoint to execute the SQL query in the "searchResults" field.

If the query was successful, then the grid.deleteRow() function is used to delete the existing rows in the "usersList" grid and then grid.addRow() is used to add rows to the grid in order to display the results of the SQL query.

Note: Unfortunately, the execute-query REST endpoint only returns two fields from an SQL query. If needing to return more fields, concatenate multiple fields separated by a character such as "|". Then, break the fields apart on the "|" with JavaScript's String.split() function.


When the user enters a search string in the "searchGroups" text field, both the two dropdown boxes and the grid will display the results. For example, if the user enters "er", then the database query in the group titles will find the groups "Line Workers" and "Managers".

SearchGroupsAndUsersResultsInDropdowns.png

The results will also be displayed in the grid:

SearchGroupsAndUsersResultsInGrid.png