I can't get stored procedures to work with the version of Propel used by MySQL, but you can use mysqli in a trigger.
For example, if you have the following stored procedure in MySQL:
Code: Select alldelimiter $$
create procedure getUsers(in search_string varchar(64))
begin
declare user_query varchar(66);
set user_query = concat("'%",search_string,"%'");
select * from USERS where USR_USERNAME = user_query;
end$$
delimiter ;
And you have the following DynaForm:
formFindUsersInDesigner.png (40.66 KiB) Viewed 8643 times
The "Select User" dropdown has its
datasource set to "array variable" and its
data variable set to "@@userOptions". The "Users List" grid is associated with the "userGrid" variable and the IDs of its fields are: userId, userName, firstName and lastName
The DynaForm has the following JavaScript code:
Code: Select all$("#action").setValue(""); //reset action when form loads
//when the "Search" button is clicked, set the action to "search"
//so subsequent trigger knows to search for a user
$("#searchBtn").find("button").click(function() {
console.log("search");
$("#action").setValue("search");
})
And the following trigger is set to fire after the Dynaform:
Code: Select all//set to the ID of the Dynaform:
$dynaformId = '5740412495b4810d1358583096516821';
//set to the ID of the DB connection or 'workflow' if a PM Table:
$dbConnectionId = 'workflow';
if (@@action == 'search') {
//use addslashes() instead of mysql_real_escape_string() if not a MySQL database:
$srch = mysql_real_escape_string(@@userSearch);
//change to your SQL statement:
$sql = "CALL getUsers('$srch')";
//mysqli_connect("domain-or-ip","user", "password", "database", "port-number")
$conn = mysqli_connect("localhost", "root", "vato1234", "wf_workflow321", "3306") or
die("MySQL connection error: " . mysqli_error());
//run the store proc
$result = mysqli_query($conn, $sql) or
die("Query error: " . mysqli_error());
@=userOptions = array();
@=userGrid = array();
$i = 1;
//loop the result set
while ($aRow = mysqli_fetch_assoc($result)) {
//set the list is options for dropdown/suggest/radio/checkgroup:
@=userOptions[] = array(
$aRow['USR_UID'],
$aRow['USR_FIRSTNAME'].' '.$aRow['USR_LASTNAME'].' ('.$aRow['USR_USERNAME'].')'
);
//set the rows in a grid:
@=userGrid[$i++] = array(
'userId' => $aRow['USR_UID'],
'userName' => $aRow['USR_USERNAME'],
'firstName' => $aRow['USR_FIRSTNAME'],
'lastName' => $aRow['USR_LASTNAME']
);
}
PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);
}
When a case is run, the Dynaform will display the searched users in the list of options in the dropdown and in the rows in the grid.
searchUsersInCase.png (21.73 KiB) Viewed 8643 times
Here is the process if you want to test it:
(31.13 KiB) Downloaded 448 times