I am building a form that uses 3 dropdown lists, each one filters the results for populating the next one, but also need to filter the results for populating 6 text fields.
I have the dropdowns working following different online tutorials but am at a loss for figuring out how to get the textfields to populate from the selections in the dropdowns. On my form I have 3 data connections, when I select a customer from the customer dropdown, the units dropdown is populated with units specific for that customer, then when I select a unit from the units dropdown, the assets dropdown is populated with assets specific for that customer and unit. This is all working, but now I need to populate 3 text fields specific for the unit selected, and 3 text fields specific for the asset selected.
Here is the code I have so far for the form:
form1.#subform[0].ddlCustomers::initialize - (JavaScript, client)
// name of the data connection from which to retrieve data
var sDataConnectionName = "DataConnectionCustomers";
// name of the column within a record from the specified data connection which will provide the hidden values for list items
var sColHiddenValue = "idCustomers";
// name of the column within a record from the specified data connection which will provide the display text for list items
var sColDisplayText = "CompanyName";
// Search for node
var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name!= sDataConnectionName)
{
nIndex++;
}
var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified in sDataConnectionName is our "database"
oDB.open(); // load the data from the connection into the form, resolving any bindings
oDB.first(); // move to the first record in the connection
// Search node with the class name "command" (this is )
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");
// Remove all items currently in the list (if any).
this.clearItems();
// Search for the record node with the matching Data Connection name (looking for node within the current record data)
nIndex = 0;
while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;
}
var oRecord = xfa.record.nodes.item(nIndex);
var oValueNode = null;
var oTextNode = null;
for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)
{
if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)
{
oValueNode = oRecord.nodes.item(nColIndex);
}
else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)
{
oTextNode = oRecord.nodes.item(nColIndex);
}
}
while(!oDB.isEOF())
{
this.addItem(oTextNode.value, oValueNode.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();
...................................................................... ...................................
form1.#subform[0].ddlCustomers::change - (FormCalc, client)
// Modify the SQL Statement on the DataConnectionUnits data connection such that it filters the records on the selected category.
// Get the id for the category the user just selected (which is the id of the category to filter on):
var sCategoryName = xfa.event.newText
var sCategoryId = $.boundItem(sCategoryName)
var oDataConn = Ref(xfa.sourceSet.DataConnectionUnits.clone(1)) // get a *reference* to the data connection object (you'll get an error if you try to get it by value)
// First, we need to set its query command type to "text" so that we can specify an SQL statement for it.
// Otherwise, we would have to specify a table or stored procedure.
// Set the //sourceSet/MoviesInCat/command/query@commandType attribute to "text".
oDataConn.#command.query.commandType = "text"
// Next, we need to specify the SQL statement for the data connection. This is done by setting the contents of
// the //query/select node.
oDataConn.#command.query.select =
concat("SELECT UnitNumber, EquipmentMake, EquipmentModel, VinNumber, idUnits FROM units WHERE Customers_idCustomers = ", sCategoryId, " ORDER BY UnitNumber;")
// Finally, we can open the data connection and look at the data which should get automatically populated
// into the EquipmentMake, EquipmentModel, and VinNumber fields of the form because of the explicit bindings
// that have been specified on those fields.
// This is not working but would not be correct if it was because I have not yet selected a specific Unit
// from the ddlUnits dropdown ???
oDataConn.open()
oDataConn.first()
// Backup the original settings before assigning BOF and EOF to stay
// Not sure this is needed since DataConnectionUnits data connection has this set to stay in the connection string
var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute("bofAction")
var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute("eofAction")
oDataConn.#command.query.recordSet.setAttribute("stayBOF", "bofAction")
oDataConn.#command.query.recordSet.setAttribute("stayEOF", "eofAction")
// Remove all items currently in the list (if any).
ddlUnits.clearItems()
// Add items to ddlUnits dropdown
while(not oDataConn.isEOF()) do
ddlUnits.addItem($record.DataConnectionUnits.UnitNumber,$record.DataCo nnectionUnits.idUnits)
oDataConn.next()
endwhile
//Close connection
oDataConn.close()
...................................................................... .......................
form1.#subform[0].ddlUnits::change - (FormCalc, client)
// Modify the SQL Statement on the DataConnectionAssets data connection such that it filters the records on the selected category.
// Get the id for the Unit the user just selected (which is the id of the Unit to filter on):
var sUnitName = xfa.event.newText
var sUnitId = $.boundItem(sUnitName)
var oDataConn = Ref(xfa.sourceSet.DataConnectionAssets.clone(1)) // get a *reference* to the data connection object (you'll get an error if you try to get it by value)
// First, we need to set its query command type to "text" so that we can specify an SQL statement for it.
// Otherwise, we would have to specify a table or stored procedure.
// Set the //sourceSet/MoviesInCat/command/query@commandType attribute to "text".
oDataConn.#command.query.commandType = "text"
// Next, we need to specify the SQL statement for the data connection. This is done by setting the contents of
// the //query/select node.
oDataConn.#command.query.select =
concat("SELECT AssetName, EngineMake, EngineModel, SerialNumber, idAssets FROM assets WHERE Units_idUnits = ", sUnitId, " ORDER BY AssetName;")
// Finally, we can open the data connection and look at the data which should get automatically populated
// into the EngineMake, EngineModel, and SerialNumber fields of the form because of the explicit bindings
// that have been specified on those fields.
// This is not working, but would not be correct if it was because I have not yet selected a specific Asset
// from the ddlAssets dropdown ???
oDataConn.open()
oDataConn.first()
// Backup the original settings before assigning BOF and EOF to stay
// Not sure this is needed since DataConnectionAssets data connection has this set to stay in the connection string
var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute("bofAction")
var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute("eofAction")
oDataConn.#command.query.recordSet.setAttribute("stayBOF", "bofAction")
oDataConn.#command.query.recordSet.setAttribute("stayEOF", "eofAction")
// Remove all items currently in the list (if any).
ddlAssets.clearItems()
// Add items to ddlAssets dropdown
while(not oDataConn.isEOF()) do
ddlAssets.addItem($record.DataConnectionAssets.AssetName,$record.DataC onnectionAssets.idAssets)
oDataConn.next()
endwhile
//Close connection
oDataConn.close()
...................................................................... ...................................................................... .....
Any help, hints, links to usefull information on what to do next would be gratly appreciated.
