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

Hi Stefan,

I’m lost. Ann didn’t paste her solution in complete format, as is.

How can you combine the code in your example when one is javascript and one is FormCalc? I read your post above and what you’re saying makes sense. However, I would assume that all the code goes in the change event of the first drop down list. I’ve succeeded in populating my first drop downlist by binding it to a recordset, and then successfully changing the query of the 2nd dataconnection to populate the 2nd drop down list. My first drop down list is DDL_ProjectNo (it works). My second is DDL_BillingGroup. I’ve got the code below in FormCalc using your example:

var projectName = xfa.event.newText
var projectID = $.boundItem(projectName)

var oDC = Ref($sourceSet.DataConnectionBilling.clone(1))

DDL_BillingGroup.clearItems()

oDC.#command.query.commandType = “text”

oDC.#command.query.select.nodes.item(0).value = concat(“SELECT BMPROJ_ID, CBGTITLE FROM BMBGRP WHERE BMPROJ_ID = “, projectID, “”)

oDC.open()

I assume the code outlined in your javascript now has to grab the results of the updated query results and populate the DDL_BillingGroup list with the contents…but that code is in JavaScript. Am I missing something here?

Thanks.

Hi Again Stefan,

Sorry to drag this, but I’m trying to solve it on my own without taking too much of your time. I have downloaded your sample, and actually set up mysql to get it to run, so I think I understand how that code works. However, the difference is that once DDL1 is changed (to a project number), that does not trigger an event in DDL2 so I don’t know how writing code in DDL2’s change event would work. I just assumed all the code needs to be written in DDL1’s change event.

Hi All,

i’m really new in using Adobe Livecycle. I’m experiencing the following problem: i have a form that works perfectly in acrobat 9 but it doesn’t work with the reader. The error is related to the clone method (the error message is in italian… the translation should be “method clone unknown”.
Below a fragment of the code:

var sName = xfa.event.newText
var sNameID = $.boundItem(sName)
xfa.host.messageBox(sName, “Debugging”, 3);
xfa.host.messageBox(sNameID, “Debugging”, 3);

var oDataConn = Ref(xfa.sourceSet.staffing.clone(1))
// get a *reference* to the data connection object (you’ll get an error if you try to get it by value)
// 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 note_spese_data WHERE matricolahp = ‘”, sNameID, “‘;”)
oDataConn.open();

I really appreciate any help.
Many thanks,
Alessandro Benedetti

Hi stefan,

Help to review what’s wrong in the following code.Thanks in advance.When it run.will show a message:
Error:first operation failed.bacause there was no connection specified.

PriceTable.Page1.N4A318AKB::calculate – (FormCalc, client)
var sMN = $.name
var oDataConn = Ref(xfa.sourceSet.DataConnection)
oDataConn.#command.query.commandType = “text”
oDataConn.#command.query.select =
concat(“SELECT PR FROM pricetable WHERE MN = ‘”, sMN, “‘”)

oDataConn.open()
oDataConn.first()
$.rawValue=$record.Dataconnection.PR
oDataConn.close()

You may have already addressed this issue in a previous post…I need to understand how to populate one dropdown box from another. I found an example on [ http://livedocs.adobe.com/livecycle/8.2/acrobat_designer/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Adobe_LiveCycle_Designer_Help&file=000892.html ] however, I am unable to make it work. From my limited experience with this process I can only surmise the xml file is not setup properly, but not sure. I am able to populate the first dropdown, and I can bind to the second, however it does not populate. I included the JS in the change event for first dropdown, but still nothing. Can you provide a working example or instructions for making this work. Greatly appreciated. Jake

Hello,

I’ve been searching everywhere to find the Javascript equivalent of the following FormCalc:

oDataConn.#command.query.commandType = “text”
oDataConn.#command.query.select =
concat(“SELECT * FROM table_name WHERE column = ‘ “, sNameID, ” ‘ “)

The reason I’ve been searching for this is because I would like to have a textfield that can allow the user to search a database, and have the possible duplicate results populate a separate dropdown.

As far as I know, creating a new data connection allows to specify an SQL query, but not at run-time (will always have the same search). So that felt like a dead end.

Your examples use both Javascript and FormCalc, which is making it hard to combine both events into one object’s event.

Thank you for your time.

Luc