Data Connections - Referencing Cloned Nodes

[Java_Is_differe]

I am working on a set of forms which pull in data from an ODBC connection. Specifically a MySQL database.

The connection was setup per the Stefan Cameron blog posts

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/

I am also working off the cloned connection per the following tutorials (updated for use with XML 2.5):

http://forms.stefcameron.com/2006/12/07/better-form-design-with-xfa-25/

and the "Data Connections tutorial by Paul Guerette (http://acrobatusers.com/tutorials/database-connected-forms).

I am able to modify the SQL query, and pull in the specific record(s) I want.

However -

Binding the dependent fields to the original connection does not work.. as it returns the first record every time (unless I navigate through them using next, previous, first, last etc.).

Binding the fields to the cloned connection (var oDB in the following code) does not seem possible as it does not exist prior to the script execution, nor does it persist after the script completes -

I have tried to assign the oDB as a form variable - i.e to store the data for other fields/scripts later on.

I also know that I should/could use a second connection to populate the dependent fields

How do you do this dynamically without using a script?.. if it is scripted it requires a cloned connection with the above mentioned limitaions.

I am probably missing something in the syntax used to refer to the data stored in oDB... Any information on referencing the specific data inside a variable/record would be appreciated. 

Thank you,

Jason

The following code is from the exit event (javascript) attached to the dropdown being used to select the specific record: 

//in a dropdown box/text field - java script (client).

var inName = this.rawValue;      //the value of the record you want to locate- in my case this is a pre-populated dropdown list that shows all employees...

                                              //if you want to get a specific text name instead of the record number - use "var inName = this.formattedValue; " instead, and below search the appropriate column.

//iterate through the avalable data connections until you find the one you want (Data Connection).

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != "Data Connection")

{

nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // clone the node pertaining to the data connection specified

//set up sql call to DB to get specifics about record

oDB.nodes.item(1).query.setAttribute("text", "commandType");

oDB.nodes.item(1).query.select.nodes.item(0).value = "SELECT * FROM `empdirectory`.`employee_contacts` where employee_contactid = '" + inName + "'"; 

//now connect to DB and get a record

oDB.open();

var oRecord = xfa.record.nodes.item(0);

//app.alert(oDB.saveXML("pretty")); //shows the full data connection properties

app.alert(oDB.nodes.item(1).saveXML("pretty")); //only shows the specific node properties

// This is where things get confusing - none of the following scripts execute properly after the var declarations. I am attempting to fill certain text fields from the data record values. 

var oField =null;

var oEmail = null;

var oPhNum = null;

var oField = oDB.nodes.item(0).empName.formattedValue;

xfa.resolveNode("form1.#subform.DBName.empName") = oField.value;

app.alert(oField);

xfa.resolveNode("form1.#subform.empName") = oDB.empName.rawValue;  //trying to fill the fields directly - without binding it to any particular connection

xfa.resolveNode("form1.#subform.empPhone") = oDB.empPhone.rawValue;

xfa.resolveNode("form1.#subform.empEmail") = oDB.empEmail.rawValue;

oDB.close();