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
- It is recommended to work with the ProcessMaker native ProcessMaker functions rather than create complicated custom functions inside triggers.
- Use the formatDate() function of PHP for English. For other languages, use PHP's strftime() function.
- The userInfo() in ProcessMaker 3.x is an alias for the PMFInformationUser() function. Both functions return the same information about the user.
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:
(read more here)$result = executeQuery('SELECT * USERS WHERE USR_UID=\'$uid\'');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():
(read more here)$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'];
}
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:
Can now use the DEPARTMENT table directly:
Other Examples
To look up all the users in a group named "Accounting":
WHERE U.USR_UID = GU.USR_UID AND G.GRP_TITLE = 'Accounting';
To look up all the groups the user is assigned to:
To look up the name of a group using its group UID:
To get the process UID of a process named 'Expense Report Process':
To get the Dynaform UID of a Dynaform named 'Report Form':.
To get all the files uploaded as input documents in a case: