[shuqierduo]

I have three fields on a Dyanform, I am expecting that when a user input a valid Passport Number, the First Name and Last Name from the database will be retrieved and populated.

FirstName
LastName
PassportNumber

There is a database table "CUSTOMER" (not PMTable but sits on the same MySQL database) created with the fields above

A database connection is created to the database and on the Dynaform, I have put the following SQL on the FIrstName and LastName fields correspondingly

SELECT FIRSTNAME FROM CUSTOMER WHERE PASSPORTNUMBER = @@PassportNumber

And

SELECT LASTNAME FROM CUSTOMER WHERE PASSPORTNUMBER = @@PassportNumber

There is no errors but I could not get the First Name and Last Name fields refreshed even when I update Passport Number on the form.

I tried to create a button with the follow script but it does not work neither. Can someone help please?


$db = "46351562218d923e118a881289223496";


$("#customerLookupButton").find("button").click(function(){
$idDocRef = jQuery("#PassportNumber").getValue();
var dummy = getFieldById("FirstName").executeQuery();
});

[amosbatto]

In your "firstname" and "lastname" fields you need to set the database connection property to your MySQL database.

You need to have a control in your DynaForm with the ID "PassportNumber" and that control needs to be associated with a variable which is also named "PassportNumber" in order for the dependent field to work correctly.

If you can't figure it out, export your your DynaForm and post the JSON file here on the forum.

[shuqierduo]

Thank you! It worked.

I missed the db selection which is pointing to PM tables by default.

Now I would like to add the other customer attributes such as DOB (datetime), Gender (radio buttons), etc.

I read http://wiki.processmaker.com/3.0/Dependent_Fields there are examples only for dropdown and text

[shuqierduo]

To supplement my question, here is what I intend to do.

In the database I have a customer table with the following fields

1. CIN (longint)
2. name (varchar)
3. id_document_number (varchar)
4. id_document_type (int)
5. dob

and a lookup table with the following

1. id_document_type (int)
2. id_document_label (varchar)

On a Dynaform, I have text controls for #1 - 3 above, radio button control for #4 and date/calendar for #5.

The radio is associated with a variable IDDocType and I have the following SQL configured in the variable

"SELECT id_document_type, id_document_label from lookup"

Here it is what I want to do: When a user input an id_document_number, the form will lookup the database and populate CIN, name, id_document_number, dob and select id_document_type radio button.

I have no problem populating #1 to 3 using dependant fields; I have the form showing different document types as radio button options but

1) how do I make the id_document_type selection on the form; and
2) how can I populate a date/calendar control (as there is nowhere to put in a SQL)

[amosbatto]

I have prepared a sample DynaForm and PM Table that shows how to do this:

sys-workflow_2017-03-29_1939.pmt

(2.9 KiB) Downloaded 333 times

datetime and radio as dependent fields.json

(5.31 KiB) Downloaded 372 times

Create a textbox which has an ID of "queryResult2" and a variable of the same name. This textbox will be hidden with JavaScript, but it will be used to execute the database query. In the SQL property of the "queryResult2", place the following query:

Code: Select all

SELECT DUE_DATE, CONTRACT_TYPE FROM PMT_CONTRACTS WHERE CONTRACT_ID = @=contractId

(change to @@contractId for greater security)

Then use this JavaScript code in the DynaForm:

Code: Select all

//hide textbox when form loads
$("#queryResult2").hide()

//for control.executeQuery(), see: https://forum.processmaker.com/viewtopic.php?f=47&t=710110
$("#contractId").setOnchange( function(newVal, oldVal) {
  var aResult = getFieldById("queryResult2").executeQuery();
  if (Array.isArray(aResult) && aResult.length > 0) {
    var dueDate = aResult[0].value;
    var contractType = aResult[0].text
    $("#dueDate").setValue(dueDate);
    $("#contractType").setValue(contractType);
  }
  else {    
    $("#contractType").setValue("");
    //to clear datetime fields, see: https://forum.processmaker.com/viewtopic.php?f=47&t=709978
    $("[id='form[dueDate]']").val('');
    $("[id='form[dueDate_label]']").val('');
    getFieldById("dueDate").model.attributes.data.label='';
    getFieldById("dueDate").model.attributes.data.value=''; 
  }
});

In my tests in Firefox, I noticed that setValue() didn't work after 3 or 4 times of changing the value of the "contractType" radio button. With a dropdown box, I didn't notice this problem, so you may have to change the field type.

[shuqierduo]

Excellent! This is exactly what I am looking for. Thank you very much!