[shuqierduo]

I have a simple Dynaform with the following 5 text boxes. A table "customer" on MySQL having the first four fields (in same names) is created.

CIN (string, read only, int and auto increment in MySQL)
cust_name (string, varchar in MySQL)
cust_mobile (string, varchar in MySQL)
cust_email (string, varchar in MySQL)
custQuery (string, SQL = SELECT CIN, concat(CIN, "|", cust_name, "|", mobile, "|", email) as custContact from customer where cin = @@CIN)

On Dynaform, I have the following function, so that when user input a valid CIN, the Name, Mobile and Email will be retrieved; and if customer

Code: Select all

$("#cin").setOnchange(function(){
  var aResult = getFieldById("custQuery").executeQuery();
  if (Array.isArray(aResult) && aResult.length > 0) {
    $aFields = aResult[0].text.split("|");
    $("#cin").setValue($aFields[0]);
    $("#cust_name").setValue($aFields[1]);
    $("#cust_email").setValue($aFields[2]);
    $("#cust_mobile").setValue($aFields[3]);
  }
})

2. After form triggers are created:

Code: Select all

insertNewCustomer (condition = empty(@@CIN))

$db = "812342ba3b7003230y375yyasddsad54";
$cust_name = @@cust_name;
$cust_email = @@cust_email;
$cust_mobile = @@cust_mobile;

$result = executeQuery("INSERT INTO CUSTOMER (cust_name, cust_email, cust_mobile) VALUES ('$cust_name', '$cust_email', '$cust_mobile')", $db) or die ("Error inserting customer");

updateCustomer (condition = !empty(@@CIN))

$db = "812342ba3b7003230y375yyasddsad54";
$cust_name = @@cust_name;
$cust_email = @@cust_email;
$cust_mobile = @@cust_mobile;

$result = executeQuery("UPDATE CUSTOMER SET cust_name = '$cust_name', cust_email = '$cust_email', cust_mobile = '$cust_mobile'", $db) 
    or die ("Error updating customer");

It works fine, no problem in creating new customer records, and the lookup by using CIN is working too. Only that if I retrieved a customer record then click "next step" on the form immediately without changing form data it comes back with "Error updating customer"

If I modify the email field and click "next step", no errors and database is updated accordingly.

What have I missed?

[amosbatto]

To update the record, you need to add a WHERE clause to your query:

Code: Select all

if (isset(@@CIN) and !empty(@@CIN)) {
   $db = "812342ba3b7003230y375yyasddsad54";
   $cust_name = @@cust_name;
   $cust_email = @@cust_email;
   $cust_mobile = @@cust_mobile;
   $cin = @@CIN;
   //if CIN field in database is a string, then enclose in single quotes. If integer, then don't need quotes.
   $sql = "UPDATE CUSTOMER SET cust_name = '$cust_name', cust_email = '$cust_email', cust_mobile = '$cust_mobile' WHERE CIN='$cin' ";
   $result = executeQuery($sql, $db) or die ("Error updating customer");
}

[shuqierduo]

Good catch

But it was a copy and paste error and I have the where clause on my workflow. Any clue on how I can diagnostic?

[amosbatto]

Eliminate the error checking from your trigger:

Code: Select all

$sql = "UPDATE CUSTOMER SET cust_name = '$cust_name', cust_email = '$cust_email', cust_mobile = '$cust_mobile' WHERE CIN='$cin' ";
/*Comment out the die() statement in your trigger here*/

Then, run your case with Debug Mode activated and look at the value of __ERROR__ in the ProcessMaker debugger.
If that doesn't help you, then check the MySQL error_log file (in Debian it is located at: /var/log/mysql/error.log ). If that doesn't help you, then turn on the MySQL query log. See:
http://stackoverflow.com/questions/6479 ... -query-log