Livecycle Designer 8.0 - Dropdown lists
I’m very new to forms generally and suffering big time trying to get ddl’s working. The aim is to filter SQL data (OLEDB) via an SQL string in DDL1 to give another set of data in DDL2 which can then be used to select an individual record. The SQL table contains records of information assets belonging to several different teams: the first DDL uses SELECT … GROUP BY Team to give a list of teams to choose - this works fine. I hoped to the populate the DDL2 string with concat(”SELECT assetname FROM table WHERE Team = ‘”, outputfromDDL1, “‘;”) and then to pull the appropriate asset record into the remainder of the form - probably again amending the relevant data connection. I went through the process of getting the permissions error and incorporated the clone method and I also found the note advising that the target data connection must be set for delayed open. Everything runs fine and no errors are thrown in the debug window but no items arrive in the second drop down list. The new selection string is being created OK - I can see this by posting it into a text field on the form.
I’ve also tried setting a form variable and using that in the data connection SELECT string but without success.
Many thanks … Andy

I’m trying to create a form which has two drop down lists (as Andy describes above), populated from SQL data (OLEDB). I’ve used Stefan’s Movie Categories sample to get my first drop-down box working correctly. But I’m not sure how to feed/manipulate that info into my second drop down list. I’m not an experienced programmer (mostly Access, and a tiny bit of SQL) so am clueless on things like “cloning” “delayed open setting”. If you have pointers that might help me, or if possibly you have an example form I could look at the code, wow, I would be so grateful for any help!!

Hi Stefan! Yes, I used your above-referenced sample as The Holy Grail when creating my form, a hearty thanks to you for that!
I’ve searched the web at length for help on this type of “cascading DDLs”, and you’re the only source I’ve seen.

I think I’m getting fairly close to the finish line. My 1stDDL works correctly, and it also populates the 2ndDDL correctly (except that I evidently need
a this.clearItems somewhere on the Change event because subsequent selections of the 1stDDL pile up accumulated records in the 2ndDDL).

It’s mainly the 2ndDDL that is still giving me fits. When testing/selecting from the 2ndDDL, the selection doesn’t “stick” but insteads insists on
returning to the first record of that category subset. And therefore (of course) the bound text fields do not populate. Most likely (uh, “obviously”), I need
code on that 2ndDDL Change event. I tried inserting combinations of text from the 1stDDL Initialize&Change events, no success yet, possibly the
dual languages (JavaScript & FormCalc) are contributing to my failure. Could you give me some pointers?

Here’s what I have for code so far on the 1stDDL for Initialize and Change events. Again, thanks in advance for any & everything you can share with me!

—– form1.#subform[0].Addressbook.1stDDL::initialize: - (JavaScript, client) ——-

/* This dropdown list object will populate two columns with data from a data connection.

sDataConnectionName - name of the data connection to get the data from. Note the data connection will appear in the Data View.
sColHiddenValue - this is the hidden value column of the dropdown. Specify the table column name used for populating.
sColDisplayText - this is the display text column of the dropdown. Specify the table column name used for populating.

These variables must be assigned for this script to run correctly. Replace with the correct value.
*/

var sDataConnectionName = “AddressbookCategories”; // name of the data connection from which to retrieve data
var sColHiddenValue = “catTableID”; // name of the column within a record from the specified data connection which will provide the hidden values for list items
var sColDisplayText = “catTableName”; // name of the column within a record from the specified data connection which will provide the display text for list items

// 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].Addressbook.1stDDL::change: - (FormCalc, client) ————-

//
// Modify the SQL Statement on the AddressesInCategories 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.AddressesInCategories.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/AddressesInCategories/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 * FROM 1COMBINED WHERE TableID = “, sCategoryId, ” ORDER BY Organization;”)

// Finally, we can open the data connection and look at the data which should get automatically populated
// into the txtabName and txtabStreet fields in the Addressbook subform because of the explicit bindings
// that have been specified on those fields.

oDataConn.open()
oDataConn.first()

// Set the category name in the Addressbook subform.
Addressbook.abCategory = sCategoryName

// Backup the original settings before assigning BOF and EOF to stay
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”);

while(not oDataConn.isEOF()) do
cboSelectName.addItem($record.AddressesInCategories.Organization, $record.AddressesInCategories.TableID)
oDataConn.next()
endwhile
oDataConn.close()

Ann Meeker,

That’s great news! I’m glad you were able to get it to work. Thanks for posting your solution so that others can benefit from it.

Cancel the last post. I pretty much got it working with the following code:

—– form1.P1.SubSTandET.ddlMonET::change: - (JavaScript, client) ———————————

//makes the border visible and sets the color to white
this.border.edge.presence = “visible”;
this.borderColor = “255,255,255″;

//checks the values and compares them
if(this.boundItem(xfa.event.newText) SubSTandET.ddlMonST.rawValue){
this.fillColor = “255,255,255″;
}

However, the data from the drop down lists is still not being read correctly. From the starting time list..if I choose say 9:00 AM and if I choose the end time as 2:30 PM. With my code, there is still an error. I have a feeling that it has to do with this line: SubSTandET.ddlMonST.rawValue. Is there another way I can read the values from this previous list without using the rawValue object?

Hi Stefan,
im caught up with this DDl. i have 2 DDLs on my form. the first DDL has category and the second one supposed to get populated based on the category of the first one.i dont have any database connection.
i used switch case infirst ddl achange event and i was able to fill the second based on addItem. now i included the code such a way that if user selects 1 from DDL1 then a,b has to appear inDDL2.
if he selects 2 then i delete the previoius items in DDL2 using DeleteItems and add c,d to the second DDL.but DeleteItems doesnt work as supposed to and all the items remain making it to the lose the functionality of dynamic populating. please help me out with this
thanks
sri