drop down list from sample xml data

[Former Community Member]

Hello All,

I was wondering if it possible to fill a drop down box with values from
the XML sample file (with a dataconnection). It is no problem to fill standard text fields with a dataconncetion.

For example I have this situation:
I made a xdp form with a dropdown box for the status.
In the xml sample file i got the following values:

open
denied
closed


I tried to connect the dropdown box with the xml data but it does not work. The list items of the drop down box are still empty. Anyone can help me with this problem?

Thanks in advance

Jalan

[Former Community Member]

You're headed in the right direction with data bindings and data connections but the trick here is to use Dynamic Properties. It's a new feature in LiveCycle Designer 7.1 which lets you bind nodes in data files to properties of certain objects.



First, you have to turn on Dynamic Properties: Go to the Tools menu and pick Options. Then go to the Data Binding item and check
Show Dynamic Properties.



Having created a data connection to your data file, drop a list box on the form. The List Items property on the Object palette's Field tab should now be green. Click on this label to edit the dynamic binding properties of the items of the list box. In there, set Items to "$record.value[*]" and Item Text to "$".



Save the form as a Dynamic PDF and load it into Acrobat. Import your data file into your form (File menu, Form Data, Import Data) and the list box should get populated with items which are the values in the data file.



I've attached a sample of this.



Stefan

Adobe Systems

[Former Community Member]

Thanks you very much Stefan. It works exactly how I wanted it to work.

[Former Community Member]

I am having an issue with this particular feature. I have a subform with dynamically populated listboxes in my form. When I call the instanceManager to create a new instance of this subform, the listboxes do not contain any values at all. Any thoughts on how to get this to work?

[Former Community Member]

Once you put the drop down list into dynamic subforms, you need to modify the data file's structure a little bit so that it mimics the subform hierarchy that you get when each list box is in its own subform.



You can still do this with Dynamic Bindings as I described earlier in this thread but the catch is that you can't have each instance of the drop down automatically populated with different data.



Based on the sample data initially posted by jalancrew, consider this data which shows 3 instances of data for a list:

<status>

 <section>

  <value>1-open</value>

  <value>1-closed</value>

  <value>1-denied</value>

 </section>

 <section>

  <value>2-open</value>

  <value>2-closed</value>

  <value>2-denied</value>

 </section>

 <section>

  <value>3-open</value>

  <value>3-closed</value>

  <value>3-denied</value>

 </section>

</status>

Using this data file and placing a list box in a repeating subform named "section", you could automatically generate 3 instances of the section subform, each with a list box populated with some values using the following SOM expression in the list box's Dynamic Binding dialog:

$record.section[*].value[*]

The catch is that each list box will be populated with the combination of all values for all sections because of the "section[*].value[*]" in the expression above. What you really need to do is modify this expression for each instance of the list box to "section[x].value[*]" where x is the instance of the section subform but this unfortunately isn't possible because the Dynamic Binding expression can't be modified "on the fly" at this time.



I've attached a sample form and data which illustrates what I'm trying to explain here. It generates 3 subforms each with a list which includes all data values.



You could, however, cheat the short-fall in the Dynamic Binding feature by writing a script on the list box that would look at all items in the list and eliminate all the ones that don't start with the string "X-" where X is "parent.index + 1" and then would strip-off the "X-" prefix on the remaining items (based on my sample data in this case). It's not a very eloquent solution but it might do the trick until the Dynamic Binding feature is improved.



Stefan

Adobe Systems

[Niall_O_Donovan]

I will chance this topic here....



I have followed the examples with the following script:



var sDataConnectionName = "Green_List";

var sColHiddenValue = "GreenProject";

var sColDisplayText = "GreenProject";



var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}



var oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

oDB.first();



nIndex = 0;

while(oDB.nodes.item(nIndex).className != "command")

{

nIndex++;

}



oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayBOF", "bofAction");

oDB.nodes.item(nIndex).query.recordSet.setAttribute("stayEOF", "eofAction");



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(oValueNode.value, oValueNode.value);

//IDList.addItem(oValueNode.value, oTextNode.value);

oDB.next();

}



// Close connection

oDB.close();



It populates the drop down list with the GreenProject records from the Green_List Table. But the problem is that is populates will all occurances, therefore repeating every record. Is there a way that the drop down list will only given a single population of each record in the access database?



Thanks in advance ;-)



Niall

[Former Community Member]

Niall,



If I understand correctly (and based on the topic of this thread), you script is located on, say, the Initialize event of a list box object which is parented to a dynamic subform. When your form loads, multiple instances of this subform are created. You would like each list box within each instance of the subform to have its respective list of items based on the specific subform instance but the problem is that each list box gets all items from the table as opposed to only the items which pertain to its specific instance. Is that correct?



Assuming I've understood your problem correctly, what you could do is add a column to your table with the number of the subform instance to which each row pertains, starting with zero (0). Let's call it "InstanceNumber".



Say you had 9 items which were to be split amongst 3 instances of a list box, each within its own subform. You would set the InstanceNumber column of the first 3 to zero (0), the next 3 to one (1) and the last 3 to two (2).



You would then add a third variable to your script:

var sDataConnectionName = "Green_List";

var sColHiddenValue = "GreenProject";

var sColDisplayText = "GreenProject";


var sColInstance = "InstanceNumber";

Further down, you would do the same:

var oValueNode = null;

var oTextNode = null;


var oInstanceNode = 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); }



  
else if (oRecord.nodes.item(nColIndex).name == sColInstance)



  { oInstanceNode = oRecord.nodes.item(nColIndex); }

}

Finally, you would use the index of the list box's parent object (the subform) to weed-out the values you don't want:

while(!oDB.isEOF())

{

  
var nInstanceNum = new Number(oInstanceNode.value); // make sure we compare numbers and not a string to a number



  if (this.parent.index == nInstanceNum)

  {

    this.addItem(oValueNode.value, oValueNode.value);

  }



  oDB.next();

}

Does this help you out at all?



Stefan

Adobe Systems

[Niall_O_Donovan]

Thanks Stefan for your efforts, I apologise about my vagueness on my problems.



I inputted the script (which was as an initilisation event), however it still does not work.



What I have is a single page dynamic form which appears blank except for a three group of radio buttons (Red, Amber and Green), when the User selects a button it brings up two invisible subforms (one for the query and the second which contains fields which will be populated with the records from Access, based on the choice of the User).



In the Access Database I have three separate Tables (Red_List, Amber_List and GreenList).



When the User selects the red button it brings up two separate "data drop-down lists", which contains the references to the "red" data connection. The drop-down list is populated correctly from the Access database, in that it contains all of the records in the same order as is in the Table. However What I need to do is have only a single population of a record in the drop-down list, even though there may be several occurances of that record in the Access Table.



The reason is that what I need the form to do is to populate the fields for each occurance of that record in the Table (by the User clicking Next, Previous, First and Last buttons). This is going to grow into a large database.



I have had some success with the "Get Red Data" button if I leave the drop-down list blank (Null) in that it populates the form with all of the records from the Red_List. But what I realy need to do is allow the User select criteria to narrow the search.



I would really appreciate any help you can give, as this is the last of a series of three forms (one emails data, next form stores information to database and this one will allow Users to interograte the records). Important meeting tomorrow with potential user group.



Thanks in advance,



Niall

[Former Community Member]

That's OK. I was going out on a limb anyway.



So let's see if I get it this time: You have a group of 3 radio buttons named something like "red", "amber" and "green". When you click on a radio button, two subforms appear, one of which contains fields which expose the values of rows in the pertaining table (red, amber, green, depending on the radio button that was clicked) and controls to move to the next/previous/first/last/whatever record in the table. One of these fields is a drop down list which needs to be populated with the value of a column in a single row in the pertaining table. The problem is that the drop down list gets the entire list instead of a single row/column value.



Let's start with eliminating the simple possibilities before diving into something more complex: Is it possible that you used the Data Drop Down List from the Library palette's Custom tab simply because you thought that's what was needed to have a drop down list bind to a value in a table via a data connection? If that's the case, the remedee may be simple: The Data Drop Down List is designed to populate a drop down list with the content of an entire column in a table. If you simply use a "regular" drop down list from the Library palette's Standard tab and bind it to the column in the pertaining table, this should load the current record's value into that drop down list.



You may already know this and in which case, it's not doing what you want (and you probably would've used a text edit field instead of a drop down list if that were the case). So what you're probably trying to do is load the value of the column (pertaining to the drop down list) of the current record into the drop down list's item list.



If that's the case, then you might try this: Place a hidden text field bound to the column which your subform exposes as a drop down list in the pertaining subform. On the buttons which control the record currently being viewed (i.e. moving from one record to the next), add script after the call to load the next/previous/first/last record which first removes all items in the drop down list and then takes the new record value in the hidden text edit field and adds it as an item in the drop down list. Then, add code to the drop down list's Change event to take "event.newText" and put it into the hidden text field (so that a change in value of the drop down list gets applied to the data base via the hidden text field).



Does any of this makes sense? Does it help? If not directly, hopefully it triggers some other solution.



Stefan

Adobe Systems

[Niall_O_Donovan]

Hi Stefan,



Yes, your perception that I misunderstood the function of the data drop down list is correct. I tried the normal drop down list, but that would not populate until after I clicked the Get Data button. Whereas I want the drop down list to determine the records to be uploaded from the database. In addition once I have populated the form, it will not allow me to "open" the normal drop down list.



I tried JavaScript in the click event of the Next button:

form1.#subform[0].RedQuery.RedProject.value = Null;

form1.#subform[0].RedQuery.RedProject.rawValue = form1.#subform[0].RedQuery.RedProDum.value;

xfa.sourceSet.Red_List.next();

(I tried it before and after the call for next)



and JavaScript in the change event of the RedProject drop down list:

event.newText;



Unfortunately I am not there yet. If we take the Table 02_Red_Hazard_List: say it contains 200 records (90 relating to hospitals, 20 relating to Retail, 30 relating to Refurbishment and 60 relating to Commercial buildings).



I have the Click event script working on an if statement: If the drop down list is blank (Null) then it populates the second subform (which takes up most of the page) with all 200 records from the Red List. The User would then have to navigate through all 200 individually. This is not particularly useful.



What I am trying to get to work is similar to the "Purchase.pdf" sample where the User selects from the drop down list, say "Retail" and the form is populated with 20 records, which they then navigate using the Next/Previous/etc.



I am using the Concat("Select * from 02_Red_Hazard_List Where RedProject = ", Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue)) ,"" script where RedQuery is the subform and RedProject is the name of the drop down list.



I hope that I am making myself clearer on my intended purpose for the form. The first two forms I have got to work (with your and Chris' help) to feed information into the database. Now I am trying to allow the User to get the information back out based on three criteria:

1 - Colour

2 - Building Type

3 - Location



To date I have focused my questions on the first two, as my belief (probably naively) was that if I get the first two to work, the third would be a cinch.



Time for a long walk on a short pier.....

[Former Community Member]

OK. I think I'm beginning to understand a little better what you're trying to accomplish.



In the RedHazardList table, you have a column named RedProject. This column identifies which project the record belongs to. You want the RedQuery.RedProject combo to be populated with the
unique values found in the RedHazardListTable.RedProject column so that when the user picks an item, you can load a
filtered record set into the second subform (which contains fields that expose the values of the current record) where only the records have a "red project" equal to the selected "red project" in the RedQuery.RedProject drop down list are available.



Hopefully I've got it now... So...



First of all, I would create a new data connection to each table (RedHazardListTable, GreenHazardListTable, AmberHazardListTable) but instead of just picking the table, I would set a special SQL statement for the data connection which is something like:

SELECT RedProject FROM 02_Red_Hazard_List GROUP BY RedProject ASC;

This will set the data connection to have a single node (RedProject) set to the unique values found in the 02_[Color]_Hazard_List table's [Color]Project column in ascending order.



Then I would use Data Drop Down List objects (I know I said not to use them before but now I understand the problem a little better), each one setup with its respective data connection.



At this point, you should be able to load your form, pick a radio button and obtain a drop down list populated with the unique values under the [Color]Project column in the pertaining table.



Now, on the Change event of each drop down list, you should be able to use your code to setup the query for the second subform (in FormCalc here):

Concat("Select * from 02_Red_Hazard_List Where RedProject = ", Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue)) ,"")

Does this get you to where you want to be (away from that short pier...)?



Stefan

Adobe Systems

[Niall_O_Donovan]

........ \

......... \

.......... \

........... >>

_____

... l

_____



^^^^^^^^^^^^^^^^^^^^^^^



Only joking ;-)



The SQL query:

"SELECT RedProject FROM 02_Red_Hazard_List GROUP BY RedProject ASC;"

would not execute in the new Data Connection procedure. I tried setting up a Stored SQL Procedure in the Access Database but I do not have the "Create stored procedure in designer" as a option in the Queries window.



So off to Bill Gates I go and I find that I can download a whooping 90Mb SQL Server Express 2005, but first I would need to download .NET - I did come across a thread where there were issues with .NET & LC Designer, so I would like to make sure that I am on the right path before I make an even bigger mess of it.



On the positive side (yes it is still there) I can see your logic of have the projects in ascending order and hopefully that would work.



Thanks for persevering: Should I plump for SQL Server or not? If it is not that, do you have any ideas why I can see the Access tables in the Data Connection view and test the connection (ok), but not execute the SQL Query?



Thanks,



Niall

[Former Community Member]

I don't think you need to download SQL Server Express 2005 and .NET just yet.



I'm using MySQL locally to test ODBC data connections. I created a table called RedList in which I have a column named RedProject. I then created a new data connection in Designer with the following SQL Query:

SELECT RedProject FROM redlist GROUP BY RedProject ASC;

This was accepted just fine by the Data Connection dialog.



I've worked with Microsoft Access in the past and the fact that your query isn't executing in the Data Connection dialog (when it tests it as your building the connection) reminds me that the SQL syntax used by Access is not necessarily the "standard" SQL syntax. Is it possible that there's a different term for "GROUP BY" in Access?



Otherwise, you talk of creating a stored SQL procedure. It might be a good idea. I know that in Access, you can create stored queries (used to be in the Queries panel -- not sure in the latest and greatest version but I'm sure it's there somewhere) which you can then use as tables in your Access forms. It might be possible to simply create a stored query in Access which does the grouping and then you may either be able to pick that query as a table in the Data Connection dialog or you might have to write a simple SQL statement like

SELECT RedListItems FROM RedListQuery;

in order to get the data connection to work with.



If none of this works, you might consider using MySQL before going the SQL Server Express 2005 route if the latter requires .NET and you've heard bad things about mixing Designer and .NET. MySQL is free to use in certain cases and in others, you may have to purchase it but it hasn't caused me any trouble so far:
http://www.mysql.com



Let me know how it goes.



Stefan

Adobe Systems

[Niall_O_Donovan]

Hi Stefan,



Back again!



I got the SQL Query to work in the Data Connection process by dropping the "ASC" at the end of the query. So I got the new data connection in and attached to the drop down list in the var sDataConnectionName = "Red_Query";. In preview it reported an error and the list would not drop down (beep warning).



The standard code that comes with a Data Drop Down List field has the following JavaScript:



// Find the value node

var oValueNode = null;

var oTextNode = null;



Both "null" are coloured blue and the error states that "Error: null is not an object".



I tried changing null to Null and by changing the "n" to capital the colour changed to black and the error was "Error: Null is undefined" at runtime.



I changed back to null but put it in quotations "null". There were no errors reported (looking good), but when I pull down the list, it was populated with 5 "Empty". This happens to be the number of records in the Access table.



I am trying to upload to our ftp at the moment, but cannot get through.



Regards,



Niall

[Former Community Member]

I'm glad you're back. I hope we can get this resolved before your important meeting...



The problem is that the query you're using (assuming it's similar to the one I suggested) results in a single node in the record set but by default, the script in the Data Drop Down List object expects two nodes in the record set: One for the item text value and the other for the item value itself. In your case, the item text value is the actual item value as well.



In the script for the Data Drop Down List, you have this section:

// Find the value node

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);



  }



}

If you configure the script as follows:

var sDataConnectionName = "DataConnection";

var sColHiddenValue = "RedProject";

var sColDisplayText = "RedProject";

what'll happen is that when you get to the
Find the value node section above, it's going to find sColHiddenValue first, set oValueNode to the recordset node and skip over setting oTextNode because the check for sColDisplayText is in the "
else if" clause.



You can either change the
Find the value node code to this:

// Find the value node

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);



  }





  
if(oRecord.nodes.item(nColIndex).name == sColDisplayText)

  {

    oTextNode = oRecord.nodes.item(nColIndex);

  }

}

or you can change the following block:

while(!oDB.isEOF())

{

  this.addItem(oTextNode.value, oValueNode.value);

  oDB.next();

}

to this:

while(!oDB.isEOF())

{

  this.addItem(
oValueNode.value, oValueNode.value);

  oDB.next();

}

and it should work without the error.



Stefan

Adobe Systems

[Niall_O_Donovan]

Stefan,



Thank you, the drop down list works perfectly now!!!!



The only remaining problem (hopefully) is that when I click the Get Data button for the item in the drop down list the following error comes up "Error: open operation failed. [Microsoft][ODBC Microsoft Access Driver] Too few parameters, Expected 1."



Now I have the Concat in an if statement where if the drop down list is empty (Null) then the Get Data button gets all records from the appropriate list. This works for all tables (red, amber and green). However if the User selects a Project type (from the now working drop down list). Then the error comes up. In all cases, bar one, the number of expected parameters is 1 (for one case it is 2).



Here in formcalc for the Click event:



if (Len(Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue))) == Null) then

$sourceSet.Red_List.#command.query.commandType = "text"

$sourceSet.Red_List.#command.query.select.nodes.item(0).value = Concat("Select * from 02_Red_Hazard_List Where RedProject")

$sourceSet.Red_List.open()

elseif (Len(Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue))) > 0) then

//Change the commandType from TABLE to TEXT. TEXT is the equivalent of SQL Property



$sourceSet.Red_List.#command.query.commandType = "text"



//Set the Select Node. Select in this case will be whatever the SQL Property you want.

$sourceSet.Red_List.#command.query.select.nodes.item(0).value = Concat("Select * from 02_Red_Hazard_List Where RedProject = ", Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue)) ,"")



//Reopen the Dataconnection

$sourceSet.Red_List.open()

endif



Is there anything glaringly obvious?



Thanks,



Niall

[Former Community Member]

I think the problem is that the RedProject column contains text however you aren't encasing your RedProject value in single quotes like this:

$sourceSet.Red_List.#command.query.select.nodes.item(0).value =

Concat("Select * from 02_Red_Hazard_List Where

RedProject = 

'
", Ltrim(Rtrim(form1.#subform[0].RedQuery.RedProject.rawValue)) ,"

'
")

The way you have your script at the moment, it's trying to pass a string into the query but SQL thinks the value of RedProject.rawValue is an identifier of somesort and can't find the definition (maybe that's not quite exact but it makes sense to me ;) ). By encasing the rawValue in single quotes, you're forcing the query to do a text comparison.



Does that fix the problem?



Stefan

Adobe Systems

[Niall_O_Donovan]

Stefan,



Thank you, thank you, thank you:-) :-) :-) :-) :-) :-)



Absolutely genius, I do not know how you do it (sorting out code from snipes of information) and have the patience to keep going in the face of repeated questioning. But I thank you, because without your great help I was completely lost. I did come across a previous thread of yours in relation to the single quotations, but I was placing them in the wrong place.



The form works perfectly, exactly as intended. The relief is immense!!! Putting information into the database is relatively straightforward, but the important thing is to make it easier for the User to abstract RELEVANT information. All I need to do is a little tidying up and it will be complete.



Thank you for ALL of your help I promise to lay off the questioning for a while!!!!.



Were not worthy, were not worthy.. (backing & bowing slowly out of the thread) tee hee ;-)



Have a great weekend.



Thanks again, Niall

[Former Community Member]

You're very welcome! I was glad to help, as always!



Stefan

Adobe Systems

[Former Community Member]

How can I make this dynamic data submit as a value? IE



Value = 1 Text = A

Value = 2 Text = B

Value = 3 Text = C



So the text in the drop down is displayed as A, B, or C



And when it gets submitted as a numeral



A, B, C and coming in from a xml file so when I use the dynamic properties how can i get the Item Value to represent a numerial rather then the text.

[Former Community Member]

I have a problem. I have created a PDF file with livecycle 8 with two dynamic drop down list boxes. These boxes have a data connection to an xml file. It works great in livecycle but when opening it in acrobat the fields do not work.



I checked the properties to allow external files to be allowed and the javascript section for the appropriate settings, and it still does not work.



Any Ideas?



Thanks Rob