Please rate how useful you found this document: 
Average: 2.8 (5 votes)

Triggers

  • It is recommended to write a descriptive title for the trigger so its function is immediately obvious, as well as a brief description of what it does.
  • It is recommended to add comments to the code source that describe what each section of code does.
  • UID stands for "unique identification", which is a string of 32 hexadecimal characters that identifies objects in ProcessMaker (read more here).
  • Avoid using PHP global variables, such as ($_GET, $_POST, $_SESSION, etc). It is recommended to use case variables instead.
  • Triggers are not executed after Dynaform steps if the Dynaform's data isn't saved (i.e., a submit button was not clicked). If the Next Step link is clicked, then the trigger after the Dynaform will not be fired. To work around this problem, set the trigger to fire before the next step (or before assignment if the last step in the task) (read more here).
  • It is recommended to do not change the structure of the trigger while there are still cases running (read more here).
  • It is recommended to review the PHP trigger so as to avoid syntax that causes any type of fatal error. A fatal error stops case routing by interrupting the Laravel Jobs queue.

ProcessMaker Functions

ProcessMaker Database

  • It is helpful to be able to recognize the most common fields in the tables in the wf_workspace database. Currently, there is not a page in our wiki with the complete description of a wf_workspace database for ProcessMaker version 3. Nevertheless, refer to this page to learn about some of the most common and useful fields. Also, take into account that most of the descriptions of fields and tables are on different pages of the wiki, mostly when explaining code examples, describing the fields used, etc.

The executeQuery() Function

If there are triggers that use executeQuery() to retrieve several registries from the database, it is recommended to optimize the tables with the correct indexes. To learn how to work with indexes, see this useful resource.

Take the following considerations when using the executeQuery() function:

  • ProcessMaker system and case variables can NOT be inserted into double quoted strings in the SQL statement, like this:

    $result = executeQuery("SELECT * FROM USERS WHERE USR_UID='@@USER_LOGGED'");

    Instead, ProcessMaker system and case variables need to first be assigned to normal PHP variables to be inserted inside a double quoted string, like this:

    $uid = @@USER_LOGGED;   #assign to normal PHP variable to insert in string
    $result = executeQuery("SELECT * FROM USERS WHERE USR_UID='$uid'");

    Also note that single quoted strings are treated as literal strings, so PHP variables can NOT be inserted into a single quoted string like this:

    $result = executeQuery('SELECT * USERS WHERE USR_UID=\'$uid\'');
    (read more here)

  • String, date and datetime values in SQL queries should be enclosed within single quoted strings, and integer and floating-point values should NOT be enclosed within single quotes. To insert an apostrophe into a string, escape it with a backslash. For example:

    $result = executeQuery("SELECT FIRSTNAME, LASTNAME FROM CLIENTS WHERE ID=8734 AND STATUS='Don\'t wait'");

  • Some databases, like MySQL, will accept double quotation marks in place of single quotation marks around strings and dates, but it is not recommended because it is not part of the SQL standard.

    When using text input by a user in a database query, it is strongly recommended to pass that text through mysql_real_escape_string() if using a MySQL database, pg_escape_string() if using a PostgreSQL database, or addslashes() for other types of databases to avoid code injection attacks.

    For example, the following code uses text entered in a Dynaform field named "clientName" in the SQL query. Problematic characters are escaped with backslashes by using mysql_real_escape_string():

    $db = '89445967454f5d18dd694f4084525230'; //unique ID of database connection
    $client = mysql_real_escape_string(@@clientName);
    $result = executeQuery("SELECT * FROM CLIENTS WHERE CLIENT_NAME='$client'", $db);
    if (is_array($result) and count($result) > 0) {
       @@contractAmount = $result[1]['CONTRACT_AMOUNT'];
    }
    (read more here)

Query Performance

As of ProcessMaker 3.1, a new database schema was created to improve the performance of SQL queries that used the Content table to obtain element titles, UIDs, etc, and allow the user to retrieve this type of information directly from the element table. For more information about the new database schema, read more here.

For example, the Department table now includes a DEP_TITLE column, so is not required to do a join with the content table to get the department title that a user ($userID) is assigned to. Therefore, a query that used the Content table in ProcessMaker 2.x to retrieve this information, like the one below:

SELECT C.CON_VALUE AS DEPT FROM CONTENT C, USERS U WHERE U.USR_UID='813656499595cec1550fbd1046224187' AND U.DEP_UID = C.CON_ID AND C.CON_CATEGORY='DEPO_TITLE'

Can now use the DEPARTMENT table directly:

SELECT D.DEP_TITLE AS DEPT FROM DEPARTMENT D, USERS U WHERE U.USR_UID='$userID' AND U.DEP_UID = D.DEP_UID

Other Examples

To look up all the users in a group named "Accounting":

SELECT U.USR_FIRSTNAME AS NAME, U.USR_LASTNAME AS LASTNAME FROM USERS U, GROUPWF G, GROUP_USER GU
WHERE U.USR_UID = GU.USR_UID AND G.GRP_TITLE = 'Accounting';

To look up all the groups the user is assigned to:

SELECT  G.GRP_TITLE FROM GROUPWF G, GROUP_USER GU WHERE G.GRP_UID = GU.GRP_UID AND GU.USR_UID='$userID'

To look up the name of a group using its group UID:

SELECT G.GRP_TITLE FROM GROUPWF G WHERE G.GRP_UID = '$gprUID'

To get the process UID of a process named 'Expense Report Process':

SELECT PRO_UID FROM PROCESS WHERE PRO_TITLE = 'Expense Report Process'

To get the Dynaform UID of a Dynaform named 'Report Form':.

SELECT DYN_UID FROM DYNAFORM WHERE DYN_TITLE = 'Report Form'

To get all the files uploaded as input documents in a case:

SELECT APP_DOC_UID, DOC_VERSION, APP_DOC_FILENAME AS FILENAME FROM APP_DOCUMENT WHERE APP_UID='$caseUID' AND APP_DOC_TYPE='INPUT' AND APP_DOC_STATUS='ACTIVE';