Simple

[Former Community Member]

This is a simple scenario.

What will be the simplest way to implement this. We have a form which users will fill up. Based on what they check in one of the checkboxes, a drop down will appear, and based on what they select in that drop down, another drop down will appear(only checking a particular checkbox will invoke this action).

Does this scenario require javascripts, or can it be done with the built in functionalities.

[gkaiseril]

At the point you you want to process the results of the all the selections or possibly depending on how you design your forms, yes. So, I would use JavaScript throughout. What JavaScript objects and methods you can use will depend whether you are using Acrobat forms or LiveCycle Designer. And since you might be populating the drop down boxes with JavaScript, I would use Acrobat forms and not LiveCycle Designer.

[Former Community Member]

Geo,

Could you please expound on your comment "And since you might be populating the drop down boxes with JavaScript, I would use Acrobat forms and not LiveCycle Designer."?



I've created a library of forms using LiveCycle Designer. They are basic in nature with Submit buttons and Print buttons. Some of the forms I have a DataConnection to an Access 2003 database.

The database table tblAgentsPDF has AGENTNO (text field), NAME (text field), ADDRESS1, ADDRESS2, and ADDRESS3 bound to my form.



My form has a datadropdown box named SelectField that lists the contents of my tblAgentsPDF. I can drop down the box and select an Agent Name.



I have textfields I want to populate AgentNo, Name, Address1, Address2 and Address3 with the current value of the agent in the SelectField.



I've been following the code from the purchase.pdf document but the javascript sourceSet is written for the ID field, which is numeric and mine field AGENTNO is text. I can't seem to get the form to obtain the focus of the SelectField and display the correct record. I see the first record of the table.



Any thoughts?

Thanks in advance.

Pam

[gkaiseril]

Have you looked at the samples available in the LiveCycle Developer's Center?



http://www.adobe.com/devnet/livecycle/designer_scripting_samples.html



Scroll down to the setion "Working with data sources and schemas" and there is the "Sample: Displaying specific database records on your form" (ZIP, 174K) Learn how to use LiveCycle Designer to filter records in a database and populate a form with the resulting data. Developed by Stefan Cameron (Jan. 22, 2007).



You also might try Stefan Cameron's Blog, http://forms.stefcameron.com/, and his post on "Databases: Inserting, Updating and Deleting Records", http://forms.stefcameron.com/2006/12/18/databases-inserting-updating-and-deleting-records/ .

[Former Community Member]

Hi Geo,



Yes I downloaded the ZIP file. The database script is for SQL of which I do not have access to an SQL server to create the database.



I followed the script and created an Access Database but can't seem to get the Data Connection going for the drop down list. When clicking the drop down box in the Preview mode I hear a beep but do not see the list.



I was further on my original form. At least my drop down worked and I could see the first record of data in the text fields I just couldn't get the focus to the record selected from the drop down.



My deadline is growing ever closer... mumble mumble mumble.



Any chance I could email you my form and Access database for my project and you could look at it?



Thanks in advance.

Pam

[gkaiseril]

Access 97 on can use SQL.



Have you looked at "LiveCycle Designer Providing Interactive DataBase Lookup From Forms"?



It has an Access database and sample form attached to the PDF.



http://www.adobe.com/devnet/livecycle/articles/lc_designer_db_lookup_tip.pdf

[Former Community Member]

Geo,



Thanks for your input. I've used the link you supplied and reconstructed my form. I have a data drop down list (named SelectField, bound normal) plus (5 text fields that are bound to my dataconnection).



On open my data drop down list is blank and my text fields show the first sorted record in my dataconnection.



I drop down the list and select a record. I then click on the Refresh button. I'd like the data in the text fields to change to that specific record.



I'm receiving the following message when clicking the Refresh button:

Script failed (language is formcalc; context is

xfa[0].form[0].form1[0].#subform[0].Button1[0])

script=if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then

//Change the commandType from TABLE to TEXT. TEXT is the

equivalent of SQL Property

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

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

$sourceSet.tblAgentsPDF.#command.query.select.nodes.item(0).value = Concat("Select * from tblAgentsPDF Where agentName = ", Ltrim(Rtrim(SelectField.rawValue)),"")



//Reopen the Dataconnection

$sourceSet.tblAgentsPDF.open()

endif

Error: This operation violates your permissions configuration.



The example I used has a numeric field as ID that populates the SelectField. My SelectField is text.



Any help would be greatly appreciated?



Thanks in advance for your help.

Pam

[Former Community Member]

Geo,



I have my form successfully working like the sample you pointed me to ("LiveCycle Designer Providing Interactive DataBase Lookup From Forms").

b var sDataConnectionName = "DataConnection"; // example - var sDataConnectionName = "MyDataConnection";

b var sColHiddenValue = "ID"; // example - var sColHiddenValue = "MyIndexValue";

b var sColDisplayText = "NAME"; // example - var sColDisplayText = "MyDescription"



b $sourceSet.DataConnection.#command.query.commandType = "text"

b $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat("Select * from tblAgentsPDF Where tblAgentsPDF.ID = ", Ltrim(Rtrim(SelectField)) ,"")

b $sourceSet.DataConnection.open()



However, I would like to modify it so the SelectField has "text data" rather than "numeric data" like the sample.



I modified the script to:

b var sDataConnectionName = "DataConnection"; // example - var sDataConnectionName = "MyDataConnection";

b var sColHiddenValue = "AGENTNO"; // example - var sColHiddenValue = "MyIndexValue";

b var sColDisplayText = "NAME"; // example - var sColDisplayText = "MyDescription"



b $sourceSet.DataConnection.#command.query.commandType = "text"

b $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat("Select * from tblAgentsPDF Where tblAgentsPDF.NAME = ", Ltrim(Rtrim(SelectField)) ,"")

b $sourceSet.DataConnection.open()



After the modification I receive the following error message on Preview:

b Error: open operation failed. [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.



Can you tell me how to modify the Formcalc script for the text field or a Javascript solution?



Thanks in advance!

Pam

[Former Community Member]

I am trying to do something similar with a simple patient database. I would like a dropdown or textbox to select a patient's name, and then jump to that patient in the form, rather than having to click "Next" or "Prev" a hundred times ;)



Any suggestions?

[Former Community Member]

Hi!



I would be very greatful if someone could answer this question since we get the same problem as Pam. Retrieving a record in an access database based on selected id in drop down list works fine until we want to test against a text attribute in the database instead of the ID (counter).