When you create a data connection and bind a field to it, you only get the first record. You then need some script to either list all records or iterate through all records one at a time.
In order to show all records on a form from a data connection to an ODBC data source, you'll need to use what's call the
instanceManager (this will answer Brian's question) which enables you to dynamically generate new instances of a subform within a container (another subform) which has a flowed (as opposed to positioned) layout (so that as you add instances, they flow nicely one after the other).
I was able to achieve this by using the script from the Data List Box in the Library palette's Custom tab and modifying it to be executed when the form is loaded (Form:Ready event on the top-level "form1" node in the Hierarchy palette) and to use the instanceManager on a row in a table.
Here are the steps I followed:
- I created a data connection to my database table via ODBC and named it "DataConnection".
- I placed a new table (named "Table1") on my form with the same number of columns as there are in my database table and with a
single row (named "Row"). Whether you specify a header and/or footer row is up to you. As long as there's one actual row in the table.
- I dragged a text field from the Library into each cell in the table row and named then
differently from the names in the nodes within the data connection (DataConnection). This is important because if you use the same names as in the data connection, you'll be creating implicit bindings and you may end-up modifying the data in your table without wanting to. Let's say there are two columns in the database table so we create two text fields named "Col1Field" and "Col2Field" as the table cells in the table row in Table1.
- I selected the Row object (the one and only row in Table1) and, using the Binding tab in the Object palette, set the
Repeat Row for Each Data Item property (checked), unchecked
Min Count and set
Initial Count to zero (0). This gives you a table with no rows initially.
- I selected the form1 top-level form node in the Hierarchy palette and, in its Form:Ready event, specified the following JavaScript:
var sDataConnectionName = "DataConnection"; // name of the data connection to get the data from. Note the data connection will appear in the Data View.
// Search for sourceSet node which matchs the DataConnection name
var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;
}
var oDB = xfa.sourceSet.nodes.item(nIndex);
oDB.open();
oDB.first();
// Search node with the class name "command"
var nDBIndex = 0;
while(oDB.nodes.item(nDBIndex).className != "command")
{
nDBIndex++;
}
// Backup the original settings before assigning BOF and EOF to stay
var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");
var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");
// Clear the rows in the table
Table1._Row.setInstances(0); // clear all instances/rows
// Search for the record node with the matching Data Connection name
nIndex = 0;
while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;
}
var oRecord = xfa.record.nodes.item(nIndex);
// Find the node representing the columns in the table
var oCol1Node = null;
var oCol2Node = null;
for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)
{
if(oRecord.nodes.item(nColIndex).name == "column 1") // column 1
{
oCol1Node = oRecord.nodes.item(nColIndex);
}
else if(oRecord.nodes.item(nColIndex).name == "column 2") // column 2
{
oCol2Node = oRecord.nodes.item(nColIndex);
}
}
while(!oDB.isEOF())
{
// create new row in Table1
var oNewRow = Table1._Row.addInstance(false);
// set the value of the column fields in the new row
oNewRow.Col1Field.rawValue = oCol1Node.value;
oNewRow.Col2Field.rawValue = oCol2Node.value;
oDB.next();
}
// Restore the original settings
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");
// Close connection
oDB.close();
The bolded portion of the script shows you the point at which a new row is being added to Table1. When a subform is identified by the Scripting Model as being able to have multiple instances (in this case, the Row subform), an instanceManager is created in a special object in the Scripting Model under the subform's parent (Table1) which has the name of the dynamic subform (Row) with a "_" prefix: "_Row".
- Preview the form and the table should be populated with data from all rows in the database table.
Stefan
Adobe Systems
