2.0/PM Tables
|
|
PM Tables are designed for users who do not wish to go through the hassle of maintaining an external database. Even users who have external databases may find PM Tables handy for quickly creating data which will be used in their DynaForms or reports. The data in PM Tables is stored in the MySQL database wf_<WORKSPACE> (which by default is wf_workflow), so the data could theoretically be accessed by an external program. Nonetheless, it is recommended that you only use PM Tables for data which will be used inside ProcessMaker. It is better to create an external database if you want to access that same data outside of ProcessMaker.
Recommendations while creating PM Tables
From version 2.0.43, validation for reserved words at the time of named fields where added, this will avoid any error or conflict while using PM tables during a case because data might not be saved in the database.
List of MySQL Reserved Words
Do not use the following words as field names for PM Tables:
"ACCESSIBLE", "ACTION", "ADD", "ALL", "ALTER",
"ANALYZE", "AND", "ANY", "AS", "ASC",
"ASENSITIVE", "AUTHORIZATION", "BACKUP", "BEFORE", "BEGIN",
"BETWEEN", "BIGINT", "BINARY", "BIT", "BLOB",
"BOTH", "BREAK", "BROWSE", "BULK", "BY",
"CALL", "CASCADE", "CASE", "CHANGE", "CHAR",
"CHARACTER", "CHECK", "CHECKPOINT", "CLOSE", "CLUSTERED",
"COALESCE", "COLLATE", "COLUMN", "COMMIT", "COMPUTE",
"CONDITION", "CONSTRAINT", "CONTAINS", "CONTAINSTABLE", "CONTINUE",
"CONVERT", "CREATE", "CROSS", "CURRENT", "CURRENT_DATE",
"CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE",
"DATABASES", "DATE", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE",
"DAY_SECOND", "DBCC", "DEALLOCATE", "DEC", "DECIMAL",
"DECLARE", "DEFAULT", "DELAYED", "DELETE", "DENY",
"DESC", "DESCRIBE", "DETERMINISTIC", "DISK", "DISTINCT",
"DISTINCTROW", "DISTRIBUTED", "DIV", "DOUBLE", "DROP",
"DUAL", "DUMMY", "DUMP", "EACH", "ELSE",
"ELSEIF", "ENCLOSED", "END", "ENUM", "ERRLVL",
"ESCAPE", "ESCAPED", "EXCEPT", "EXEC", "EXECUTE",
"EXISTS", "EXIT", "EXPLAIN", "FALSE", "FETCH",
"FILE", "FILLFACTOR", "FLOAT", "FLOAT4", "FLOAT8",
"FOR", "FORCE", "FOREIGN", "FREETEXT", "FREETEXTTABLE",
"FROM", "FULL", "FULLTEXT", "FUNCTION", "GENERAL",
"GOTO", "GRANT", "GROUP", "HAVING", "HIGH_PRIORITY",
"HOLDLOCK", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND", "IDENTITY",
"IDENTITYCOL", "IDENTITY_INSERT", "IF", "IGNORE", "IGNORE_SERVER_IDS",
"IN", "INDEX", "INFILE", "INNER", "INOUT",
"INSENSITIVE", "INSERT", "INT", "INT1", "INT2",
"INT3", "INT4", "INT8", "INTEGER", "INTERSECT",
"INTERVAL", "INTO", "IS", "ITERATE", "JOIN",
"KEY", "KEYS", "KILL", "LEADING", "LEAVE",
"LEFT", "LIKE", "LIMIT", "LINEAR", "LINENO",
"LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP", "LOCK",
"LONG", "LONGBLOB", "LONGTEXT", "LOOP", "LOW_PRIORITY",
"MASTER_HEARTBEAT_PERIOD", "MASTER_SSL_VERIFY_SERVER_CERT", "MATCH", "MAXVALUE", "MEDIUMBLOB",
"MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND", "MINUTE_SECOND",
"MOD", "MODIFIES", "NATIONAL", "NATURAL", "NO",
"NOCHECK", "NONCLUSTERED", "NOT", "NO_WRITE_TO_BINLOG", "NULL",
"NULLIF", "NUMERIC", "OF", "OFF", "OFFSETS",
"ON", "OPEN", "OPENDATASOURCE", "OPENQUERY", "OPENROWSET",
"OPENXML", "OPTIMIZE", "OPTION", "OPTIONALLY", "OR",
"ORDER", "OUT", "OUTER", "OUTFILE", "OVER",
"PERCENT", "PLAN", "PRECISION", "PRIMARY", "PRINT",
"PROC", "PROCEDURE", "PUBLIC", "PURGE", "RAISERROR",
"RANGE", "READ", "READS", "READTEXT", "READ_WRITE",
"REAL", "RECONFIGURE", "REFERENCES", "REGEXP", "RELEASE",
"RENAME", "REPEAT", "REPLACE", "REPLICATION", "REQUIRE",
"RESIGNAL", "RESTORE", "RESTRICT", "RETURN", "REVOKE",
"RIGHT", "RLIKE", "ROLLBACK", "ROWCOUNT", "ROWGUIDCOL",
"RULE", "SAVE", "SCHEMA", "SCHEMAS", "SECOND_MICROSECOND",
"SELECT", "SENSITIVE", "SEPARATOR", "SESSION_USER", "SET",
"SETUSER", "SHOW", "SHUTDOWN", "SIGNAL", "SLOW",
"SMALLINT", "SOME", "SPATIAL", "SPECIFIC", "SQL",
"SQLEXCEPTION", "SQLSTATE", "SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS",
"SQL_SMALL_RESULT", "SSL", "STARTING", "STATISTICS", "STRAIGHT_JOIN",
"SYSTEM_USER", "TABLE", "TERMINATED", "TEXT", "TEXTSIZE",
"THEN", "TIME", "TIMESTAMP", "TINYBLOB", "TINYINT",
"TINYTEXT", "TO", "TOP", "TRAILING", "TRAN",
"TRANSACTION", "TRIGGER", "TRUE", "TRUNCATE", "TSEQUAL",
"UNDO", "UNION", "UNIQUE", "UNLOCK", "UNSIGNED",
"UPDATE", "UPDATETEXT", "USAGE", "USE", "USER",
"USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP", "VALUES",
"VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING", "VIEW",
"WAITFOR", "WHEN", "WHERE", "WHILE", "WITH",
"WRITE", "WRITETEXT", "XOR", "YEAR_MONTH", "ZEROFILL"
For more information about reserved words please check this documentation
List of PHP Reserved Words
Do not use the following words as field names for PM Tables:
'case', 'catch', 'cfunction', 'class', 'clone', 'const', 'continue', 'declare', 'default', 'do', 'else', 'elseif', 'enddeclare', 'endfor', 'endforeach', 'endif', 'endswitch', 'endwhile', 'extends', 'final', 'for', 'foreach', 'function', 'global', 'goto', 'if', 'implements', 'interface', 'instanceof', 'private', 'namespace', 'new', 'old_function', 'or', 'throw', 'protected', 'public', 'static', 'switch', 'xor', 'try', 'use', 'var', 'while'
Others
'ALTER', 'CLOSE', 'COMMIT', 'CREATE', 'DECLARE', 'DELETE', 'DROP', 'FETCH', 'FUNCTION', 'GRANT', 'INDEX', 'INSERT', 'OPEN', 'REVOKE', 'ROLLBACK', 'SELECT', 'SYNONYM', 'TABLE', 'UPDATE', 'VIEW', 'APP_UID', 'ROW', 'PMTABLE'
Creating PM Tables until version 2.0.34
To create PM Tables, go to ADMIN > Settings > PM Tables
Click on New and two options will display, choose New PM Table.
Enter all the information of the new Pm Table:
Table Information:
- Table Name: Enter the name of the new table, which is case insensitive because this is the name which will be used for the MySQL table.
- Description: A description or any additional information about the table.
Creating PM Tables from version 2.0.35
Form Version 2.0.35 PM Tables are created with the prefix PMT, so by going to ADMIN > Setings > PM Tables and next to Table Name the label Auto Prefix "PMT" will be displayed.
For example:
Creating a PM Tables named COUNTRY with this prefix the table will named PMT_COUNTRY as the image below:
Note: from this version If a querying to a PM Table to populate any field of a DynaForm is required, the table must be written with the </tt>PMT</tt> prefix
Adding Fields
To add fields click on 
icon. A new row will add where information of the field must be completed:
Fields:
Field Name:A unique case-insensitive name to identify the field. Names should be 64 or less characters long and should not begin or end with numbers or spaces, nor contain the characters '\', '/' or '.'
Field Label:The displayed label for the field, which the user will see when editing data in the table.
Type:Fields can be one following data types:.
VARCHAR is a string of 255 or fewer characters.
TEXT is a string of characters which is 65,536 or fewer bytes long.
DATE is a date in the format YYYY-MM-DD.
INT is an integer between -2147483648 and 2147483647.
FLOAT is a 4 byte floating point number between -3.402823466E+38 and 3.402823466E+38. Numbers with 9 or more decimals will be rounded to to the nearest integer. For instance, if the following number is entered 0.999999999 it will be rounded to 1
.
DOUBLE float number with double precision. It represents numbers of 64 bits. Numbers with 9 or more decimals will be rounded to to the nearest integer. For instance, if the following number is entered 0.999999999 it will be rounded to 1
.
See the MySQL manual for more information on these data types.
Size: For VARCHAR fields, the maximum number of characters. For INT and FLOAT fields, the maximum number of digits of precision. For DATE and TEXT fields, size is not applicable.
Null: Select if the field may be a NULL value, which means "no data".
Primary key: Select if the field (or a combination of fields) will hold a unique, unalterable value which is used to index the table. All PM tables must contain at least one primary key.
Auto increment:select if the field will be automatically increased.
After defining each field click on Update and when all the fields will be added click on Create to store the structure of the table.
Editing Fields
To edit a field select the field to edit and click on 
or just choose the filed to edit and double click on it:
Removing Fields
To remove a field select the field to remove and click on 
icon:
List of PM Tables
After creating a PM Table, it will be displayed in the list of available tables under ADMIN > Settings > PM Tables.
Editing Pm Table Data
Choose one table and click on Edit to alter the fields in a field or change a table's name or description. All fields can be edited except the name of the table which can be edited by entering Mysql.
Fields can be added, edited and removed. If the structure of the table is edited all data will lost.
Deleting Pm Table Data
One or more tables can be deleted at the same time, check all the tables to delete and click on Delete:
Viewing and Editing PM Table Data
To view the data stored in a PM Table, select the table to view and edit, and click on the Data:
To add a new row to a PM Table, click on the Add Row link at the top of the table. Click on the Edit and Delete links to edit or delete a record in the PM Table. Fields can also be edited or deleted by clicking over the field:
To prevent careless users from accidently editing or deleting a record or seeing a particular column of data, right click on the arrow that is displayed on the PM Table header and select the columns which will be displayed.
Importing PM Tables to CSV
From version 1.2-2552 on, it is possible it import data into PM Tables from a CSV (comma separated values) file.
The CSV file should be a plain text file containing the field values separated by commas or semicolons. List the field values in the same order as they appear in the PM Table, so the first comma separated value will go into the first field, the second value in the second field, and so on. The first line of text in the CSV file will be ignored, because it is assumed to contain the column headers (field names).
To import a CVS file into a PM Table, go to ADMIN > Settings > PM Tables. Find the desired table in the list and click on its Data link. At the top of the table, click on the link CSV Import.
Then select the CSV file and click the Upload button.
If the file import is correct a message will display:
Exporting PM Tables to CSV
To export a CVS file into a PM Table, go to ADMIN > Settings > PM Tables. Find the desired table in the list and click on its Data link. At the top of the table, click on the link CSV Export choose the values separated by commas or semicolons and click on Export:
The file will download on the user's computer.
Exporting PM Tables
To export a PM table from inside the ProcessMaker interface, go to ADMIN > Settings >PM Tables select the desired table and click on the Export link at the top of the list. Select whether to export the table's Schema (structure), the Data, or both.
Finally, click on the Export link at the bottom, to generate a .pmt file, which can be downloaded and saved.
If planning on exporting PM table(s) and all their data to another installation of ProcessMaker, select both the schema and data. If planning on exporting the data to an external spreadsheet or database, then only select a single PM Table and only export its data (not it's schema).
Note: More than one PM table can be exported at the same time.
Importing PM Tables
To import a PM table from inside the ProcessMaker interface, go to ADMIN > Settings >PM Tables click on the Import link at the top of the list and select a .pmt file. Finally click on Upload button
If the PM table will be replaced with another mark the Overwrite if exists? checkbox.
Accessing PM Tables from Triggers
It is possible to use PHP code in triggers to read and manipulate the information in PM Tables. Either use the executeQuery() function or Propel classes to access PM Tables.
For example, a PM Table called "CONTACTS", which contains the fields "ID", "FIRSTNAME", "LASTNAME" and "BIRTHDAY" can be accessed with executeQuery().
To read a single row from the "CONTACTS" table:
$result = executeQuery("SELECT * FROM CONTACTS WHERE ID=23") if (is_array($result) and count($result) > 0) { @@firstname = $result[1]['FIRSTNAME']; @@lastname = $result[1]['LASTNAME']; @@birthday = $result[1]['BIRTHDAY']; }
To insert a new row into the "CONTACTS" table:
$result = executeQuery("INSERT CONTACTS (ID, FIRSTNAME, LASTNAME, BIRTHDAY) VALUES (24, 'Jane', 'Doe', '1969-03-24'"); if ($result == 0) G::SendMessageText("Could not insert new record in CONTACTS PM Table.", "ERROR");
To insert a new row into the "CONTACTS" PM Table, using data from DynaForm fields named "id", "firstName", "lastName", "birthday", create a trigger which fires after the DynaForm with the following code:
//assign case variables to normal PHP variables for insertion in string $id = @%id; $firstName = @@firstName; $lastName = @@firstName; $birthday = @@birthday; $insert = "INSERT INTO CONTACTS (ID, FIRSTNAME, LASTNAME, BIRTHDAY) VALUES ($id, '$firstName', '$lastName', '$birthday')"; if (executeQuery($insert) == 0) G::SendMessageText("Could not insert new record in CONTACTS PM Table.", "ERROR");
To update an existing row in the "CONTACTS" table:
$result = executeQuery("UPDATE CONTACTS SET FIRSTNAME='Buster', LASTNAME='Brown' WHERE ID='0020'"); if ($result == 0) G::SendMessageText("Did not update any record in the CONTACTS PM Table.", "ERROR");
To delete a row from the "CONTACTS" table:
$result = executeQuery("DELETE FROM CONTACTS WHERE ID='0020'"); if ($result == 0) G::SendMessageText("Did not delete any records in the CONTACTS PM Table.", "ERROR");
Note: It is not necessary to specify the database, since executeQuery() uses by default the wf_<WORKSPACE> database where the PM Tables are stored.
PM Tables can also be accessed through Propel classes in triggers. For instance, the same table could be accessed through its PHP class name of "Contacts". (Remember that class names are case sensitive). This example inserts a new record in the contacts PM Table:
$oContacts = new Contacts(); $oContacts->setId('0023'); $oContacts->setFirstname('John'); $oContacts->setLastname('Doe'); $oContacts->setBirthday(@@field1); $oContacts->save();
See the Propel documentation for more information on how to access tables through Propel classes.
Downloading CVS Files generated from PM Tables
Users who have the PM_SETUP permission in their role can easily obtain a CSV (comma-separated-value) file for a PM Table, by going to ADMIN > Settings > PM Tables. Nonetheless, normal production users with a role like PROCESSMAKER_OPERATOR can't access the ADMIN menu.
To workaround this problem, a Trigger can be used to generate a CSV file from a PM Table and store that CSV file in the Process Files Manager's public directory, where it can be accessed through a link in a DynaForm.
Create a Trigger with the following code to query the PM Table and create a CSV file in the public directory:
$tableName = 'PM-TABLE-NAME'; //change for your PM Table $path = PATH_DATA_PUBLIC . @@PROCESS . PATH_SEP; if (!is_dir($path)) { mkdir($path, 0770) or die("Error creating directory '$path'"); } $sFile = tempnam($path, 'PMT_' . $tableName) . '.csv'; $fFile = fopen($sFile, 'w+') or die("Error opening file '$sFile' for writing!"); $query = "SELECT * FROM $tableName"; $table = executeQuery($query); foreach ($table as $record) { $line = ''; foreach ($record as $field=>$value) { $line .= ($line=='' ? '' : ', ') . '"' . $value . '"'; } fwrite($fFile, $line . "\n"); } fclose($fFile); $filename = pathinfo($sFile, PATHINFO_BASENAME); @@linkUrl = '../' . @@PROCESS . '/' . $filename; @@linkLabel = $filename;
Then, create a DynaForm with a link field named "linkFile" and two hidden fields named "linkUrl" and "linkLabel". Add the following JavaScript to the DynaForm to set the URL and label for the link field:
getField("linkFile").href = getField("linkUrl").value; getField("linkFile").innerHTML = getField("linkLabel").value;
Set the Trigger to fire before the DynaForm, so the file will be ready for download and the case variables @@linkUrl and @@linkLabel will already be set when the DynaForm is displayed. Then, the user can click on "linkFile" and download the CSV file.
Accessing PM Tables in DynaForms
PM Tables can be integrated into DynaForms in three ways:
- Using SQL SELECT queries in individual DynaForm fields
- Creating new DynaForms based upon the fields in a PM Table
- Manually adding Read/Write Access to PM Tables in DynaForm Fields
The first method provides read-only access to the PM Table, whereas the latter two allows entered data to be added as a record in the PM Table.
Note: From version 2.0.35 If a querying to a PM Table to populate any field of a DynaForm is required, the table must be written with the </tt>PMT</tt> prefix, otherwise queries won't work.
Querying PM Tables in DynaForm Fields
DynaForm fields can obtain read-only access to PM Tables with a standard SQL SELECT statement. Since PM Tables are stored in the wf_<WORKSPACE> database, the SQL Connection property can be left blank, since it will select that database by default if blank. In the SQL property, put the SELECT statement.
Populating lists of options
If querying a PM Table to populate a list of options in a dropdown box, listbox, suggest box, radiogroup or checkgroup, then the SQL SELECT statement should return two fields. The first is the value field (which should be unique) and the second is the label field (which is what is displayed to the user).
For example, a dropdown box could populate its list of options with the last names found in the CONTACTS table used in the previous example. Its SQL property would be:
SELECT ID, LASTNAME FROM CONTACTS
and its complete XML definition would look something like this:
<SelectContact type="dropdown" required="0" readonly="0" savelabel="0" mode="edit" options="Array"> <![CDATA[SELECT ID, LASTNAME FROM CONTACTS]]> <en>Select Contact <option name=""></option> </en> </SelectContact>
To display more than one field in the label, use the CONCAT() function to combine more than one field. For example, to combine the FIRSTNAME and LASTNAME fields in the label:
SELECT ID, CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS
Populating ordinary fields
To query a PM Table in order to populate the value of a textbox, currency box, percentage box, textarea, or hidden field, use an SQL SELECT statement which only returns one field from one record. If more than one record is returned, only the first record will be used.
For instance, to select the contact whose ID is 907:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS WHERE ID='907'
Using Variables in SQL Queries
There are two ways to use variable(s) in a query to a PM Table. Either use a system or case variable which has been passed to the DynaForm as a hidden field or reference another field in the DynaForm which has a dependent field relationship with the current field.
Using system or case variables in SQL Queries
A system or case variable can be used in the SQL query to a PM Table. System variables are created automatically by ProcessMaker, but case variables either have to be created from a field in a previous DynaForm (which was submitted) in the case or defined in a previous trigger in the case. System and case variables can be passed to the current DynaForm by creating a hidden field with the same name as the case variable.
For example, to use the case variable @@ContactId in an SQL query to a PM Table, first create a hidden field named "ContactId" in the DynaForm. Remember the field names are case sensitive, so the field name has to be spelled exactly like the case variable.
Then, add an SQL query to a field which references the case variable. To enclose the value of the variable inside quotation marks, reference it as @@ContactId.
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS WHERE ID=@@ContactId
If the variable is a number, then reference it as @#ContactId to not enclose it in inside quotation marks:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS WHERE ID=@#ContactId
SQL Queries with Dependent Fields
If a dependent field relationship has been established with another field, then the value of the independent field can be references as a variable in the SQL query. Whenever the value of the independent field changes in the DynaForm, the query will be reexecuted and the dependent field will be repopulated.
In this example, the value in the "ContactId" field will determine which contact's full name is displayed in a textbox:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS WHERE ID=@@ContactId
Or if the ID is a number:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CONTACTS WHERE ID=@#ContactId
Populating DynaForm Grids
In order to display a PM Table in a DynaForm grid, first create a grid form which has the field names which are the same as those found in the PM Table. Then, embed that grid form in a master form. Finally, create a trigger which will use executeQuery("SELECT ...") to return an array of records from the PM Table. Assign that array of records to a case variable which has the same names as the grid object in the master DynaForm. Set the trigger to fire before the master DynaForm is displayed.
For example, the following trigger code would populate a grid object named "ContactsGrid":
@@ContactsGrid = executeQuery("SELECT ID, FIRSTNAME, LASTNAME, BIRTHDAY FROM CONTACTS");
If the field names in the grid form are not named "ID", "FIRSTNAME", "LASTNAME" and "BIRTHDAY", then use AS to rename the returned fields from the PM Table to the names used in the grid form. For example, the fields from the CONTACTS table could be renamed as "ContactID", "ContactFName", "ContactLName" and "ContactBirthday", respectively:
@=ContactsGrid = executeQuery("SELECT ID AS ContactID, FIRSTNAME AS ContactFName, " . "LASTNAME AS ContactLName, BIRTHDAY AS ContactBirthday FROM CONTACTS");
To write the changes in a DynaForm grid to a PM Table, set a trigger to fire after the DynaForm with code to delete all the contents of the old PM Table. Then write each row from the grid into the new PM Table. For example:
executeQuery("DELETE FROM CONTACTS"); //delete all rows in table foreach (@=ContactsGrid as $row) { executeQuery("INSERT INTO CONTACTS (ID, FIRSTNAME, LASTNAME, BIRTHDAY) VALUES " . "('{$row['ContactID']}', '{$row['ContactFName']}', '{$row['ContactLName']}', '{$row['ContactBirthday']}')"); }
When inserting PHP variables into double quoted strings (as in the example above), remember to enclose the variable names inside curly braces {} to access elements inside associative arrays.
Creating DynaForms based on PM Tables
From version 1.6-3862 on, DynaForms can be created based upon the fields in a PM Table. All the fields in the PM Table become fields in the DynaForm. The benefit is that the fields are automatically configured with write access to the PM Table. When the DynaForm is submitted, a new record is automatically added to the PM Table, with the data entered into the DynaForm.
First, create a PM Table with all the fields that will be used in needed in the DynaForm. After creating the PM Table, go to PROCESS > DynaForms to view the list of DynaForms. Click on the New link at the top of the list to create a new Dynaform. Rather than the Blank DynaForm option, select the PM Table DynaForm option:
Then, select which PM Table the DynaForm structure will be based upon:
Then, select the variables to use as indexes to relate the DynaForm to records in the PM Table. Now, click on Save to save the new DynaForm without editing or Save & Open to save it and immediately start editing it.
The Dynaform is created by default with all the fields from the PM Table. DynaForm fields can be removed or added as required, but remember that any added fields in the DynaForm will not be stored in the PM Table.
Read/Write Access to PM Tables in DynaForm Fields
As of version 1.2-2552, it is possible to obtain read and write access to a single record in a PM Table through a DynaForm. A field in a PM Table record can be associated with a DynaForm field by editing its XML definition. The data entered in the DynaForm field will be automatically updated in the PM Table.
In order to associate fields in a DynaForm with the fields from a single record in a PM Table, first go to the PM TABLES tab under ADMIN (formerly called SETUP). Select the desired PM Table from the list and click its Edit link. Then, look in the address of your web browser to find the UID of the PM Table. For instance, in the URL:
http://localhost/sysworkflow/en/green/additionalTables/additionalTablesEdit?sUID=8978302234afeaeff4f2773061370232
Copy the UID of the PM Table. Then, open a DynaForm and go to the XML tab to directly edit the XML code. Add a line near the top to establish a pmconnection to the PM Table. The pmconnection should be given a name which will be used to identify it when used in later elements of the DynaForm. Use the XML attribute pmtable to specify the UID of the PM Table and the XML attribute keys to specify the primary key(s) of the record to be retrieved from the PM Table:
<PM-CONNECTION-NAME type="pmconnection" pmtable="PM-TABLE-UID" keys="PRIMARY-KEY-VALUE" />
In this example a pmconnection called "CONTACTS_CONNECTION" is defined for the record whose primary key is 909.
<CONTACTS_CONNECTION type="pmconnection" pmtable="6119139294a843924e70988028788673" keys="909" />Always enclose the primary key value in quotes, even if its data type is not a string. If the primary key is a date, use the format "YYYY-MM-DD".
If a PM Table contains multiple primary keys, the key values can be separated by pipes (|) or by commas (,). List the values in the same order which the fields appear in the PM Table. For instance, if a PM Table has a primary key which is a combination of the fields "name", "birthday" and "income":
<CONTACTS_CONNECTION type="pmconnection" pmtable="6119139294a843924e70988028788673" keys="John Doe|1973-12-31|35000" />The keys attribute can also be set to a variable referring to another field in the DynaForm, however, the value of the variable will only be read when the DynaForm is first displayed, so it must be the default value of the field or a value set by a trigger beforehand. (Setting the value of the field with JavaScript will not work, since JavaScript code is run after the DynaForm is displayed.) For example, if trigger is fired beforehand with code:
@%SelectID = 3;
and a DynaForm contains a hidden field named "SelectID", then the following pmconnection could refer to @#SelectID:
<CONTACTS_CONNECTION type="pmconnection" pmtable="6119139294a843924e70988028788673" keys="@#SelectID" />After creating a pmconnection, its data can be accessed from a DynaForm field by using the XML attribute pmconnection to specify the name of the pmconnection and the XML attribute pmfield to specify the name of the field in the PM Table which will be associated with the field in the DynaForm:
<DYNAFORM-FIELD-NAME ... pmconnection="PM-CONNECTION-NAME" pmfield="PM-FIELD-NAME"> ... </DYNAFORM-FIELD-NAME>
For example, here the DynaForm field "ContactId" is associated with the "ID" field in the CONTACTS table:
<ContactId type="text" maxlength="64" validate="Any" required="0" readonly="0" size="50" mode="edit" pmconnection="CONTACTS_CONNECTION" pmfield="ID"> <en>ID</en> </ContactId>
It is possible to have any number of different pmconnections to PM Tables in a DynaForm. The pmconnections can be different queries to the same PM Table and/or queries to different PM Tables.
For instance, it this example, DynaForm fields are associated with two different pmconnections:
<CONTACTS_CONNECTION type="pmconnection" pmtable="611a139c94a843924e709eb028788673" keys="@#ContactId" /> <COMPANIES_CONNECTION type="pmconnection" pmtable="347a7b790cd119139294a84392bf709c" keys="ACME" /> <FirstName type="text" maxlength="64" validate="Any" required="0" readonly="0" size="50" mode="edit" pmconnection="CONTACTS_CONNECTION" pmfield="FIRSTNAME"> <en>First Name</en> </ContactId> <LastName type="text" maxlength="64" validate="Any" required="0" readonly="0" size="50" mode="edit" pmconnection="CONTACTS_CONNECTION" pmfield="LASTNAME"> <en>Last Name</en> </LastName> <Company type="text" maxlength="64" validate="Any" required="0" readonly="0" size="50" mode="edit" pmconnection="COMPANIES_CONNECTION" pmfield="CO_NAME"> <en>Company Name</en> </Company>
At this point it is not possible to obtain use a pmconnection to obtain read/write access to grids or query more than one record at a time, but that functionality will come in future versions of ProcessMaker.
It is not possible to automatically create new records in the PM Table using a DynaForm. If a new record in the PM Table needs to be created every time the DynaForm is displayed, fire a trigger before the DynaForm is displayed to create a new blank record, then use the DynaForm to fill the values in that blank record.
In this example, a trigger is used to create a new record in the PM Table before the DynaForm is displayed. A primary key is generated for the new record by adding one to the highest current primary key in the PM Table:
$result = $executeQuery("SELECT MAX(ID) FROM CONTACTS"); if (is_array($result) && count($result) > 0) $nextId = $result[1]['MAX(ID)'] + 1; else // if no table records $nextId = 1; executeQuery("INSERT INTO CONTACTS (ID) VALUES ('$nextId')"); @%NewID = $nextId;
The DynaForm has a hidden field named "NewID" and a pmconnection named "CONTACTS_CONNECT":
<CONTACTS_CONNECT type="pmconnection" pmtable="611a139c94a843924e709eb028788673" keys="@#NewID" /> <NewID type="hidden" mode="edit" btn_cancel="Cancel"/>
The hidden field is needed to pass the case variable to the DynaForm, so its value can be accessed by the pmconnection.
