Populate a form based on a selection on drop down list

[Former Community Member]

I’m new to livecycle currently using designer es2 version 9. I do have acrobat 9 standard as well.

I’m trying to create a form that has few fields. For testing I’m only testing with two fields. Emname and Empno.
I have a table named Performview in sql and I have already created a odbc connector in dsn.
I also created a dataconnection in livecycle form with default name “dataconnection”

I have made binding to the two fields empno and emname on the forms to the respective fields in performview table.
I have a dropdownlist  named selectfield.

I want to be able to drop down(on the selectfield list) and select the empno and populate the two fields empno and emname.
I have a button and I want the populating of the fields to happen when that’s pressed.
When I open the form, I see the form filled with the first record from the table so the two fields in the form emname and empno have the first record.

I have two issue, first the dropdown list does not have any data when I view the form.
2nd Since there is no data on the drop down list to test it with no data i hit the refresh button  I get "error accessor selectfield.rawvalue unknown".

I event tried to use a textfield(which I prefer to use instead of dropdown as the form user can just put the empno and hit the button, but I wasn’t sure how to do intialize for textfield. When I tried to use the text field and on the code modified instead of selectfield.rawvalue to the textfieldname.rawvalue I still had the same issue. so I stuck with the dropdown as I saw example for it on how to set up intialize. I even tried an example in adobelivecycle designer 7 that has sample with purchase.mdb I get the same "error accessor selectfield.rawvalue unknown".

so here is the code for both dropdown intialize and button click
I’ll really appreciate if you can tell me why the drop box is not having any data and why I’m getting the error
.I do see the 1st record from the table on the form when I open the form so I know the connector is work and the fields binding is working
Please see code below.

// This is for the Refresh button

form1.#subform[0].Button1::click - (FormCalc, client)

　

if

(len(Ltrim(rtrim(selectfield.rawvalue))) >0) then

$sourceSet.DataConnection.#command.query.commandType

= "text"

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

= Concat("Select * from performview Where empno = ", selectfield.rawvalue ,"")

//Reopen the Dataconnection

$sourceSet.DataConnection.open()

endif

// fOR THE DROP DOWN named selectfield
———————–

form1.#subform[0].Selectfield::initialize – (JavaScript, client)

var sDataConnectionName = “DataConnection”;
var sColHiddenValue = “Empno”;
var sColDisplayText = “Emname”;

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;
}
var oDB = xfa.sourceSet.nodes.item(nIndex);
oDB.open();
oDB.first();
// Search node with the class name “command”
nIndex = 0;
while(oDB.nodes.item(nIndex).className != “command”)
{
nIndex++;
}
// Need to set BOF and EOF to stay
oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayBOF”, “bofAction”);
oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayEOF”, “eofAction”);

// Search for the record node with the matching Data Connection name
nIndex = 0;
while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)
{
nIndex++;
}
var oRecord = xfa.record.nodes.item(nIndex);
// 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);
}
}
while(!oDB.isEOF())
{
this.addItem(oValueNode.value, oValueNode.value);
//IDList.addItem(oValueNode.value, oTextNode.value);
oDB.next();
}
// Close connection
oDB.close();

[Niall_O_Donovan]

Hi,

Two things to check:

(1) the if statement uses .rawvalue with a small 'v'. This should be .rawValue.

(2) It looks like you are using a Data Dropdown (from the custom library). If you go to the Object tabs, you will notice that the list item and specify items are now green small links with a paperclick icon. These allow you to access the dynamic properties and link directly to the database (through the data connection).

Hope that helps,

Niall

[Former Community Member]

Hi

I was finally able to populate the field by hard coding the where to clause

form1.#subform[0].Button1::click - (FormCalc, client)

$sourceSet.DataConnection.#command.query.commandType

= "text"

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

= Concat("Select * from performview Where Empno = ", 1497 ,"")

$sourceSet.DataConnection.open()

I want to use a textfield where the user of the form will enter the empno which will be applied to the query. I have only seen example for drop down list intialize, I don't want to use drop down list as it take a long time when we open the pdf as our table that drop down list uses is big and it times out.

So i want to use the input from a textfield where user's enter the number for Empno field and I use that to do the query how would I do this?

so I want to pass $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat("Select * from performview Where Empno = ", This should be from the textfield selectfield ,"")

So how do I initialze a textfield to do this?

[Former Community Member · Accepted solution]

Just use the fieldname.rawValue. The easiest way is to put the cursor in the script area where you want the reference to appear. Then hold the ctrl key down and move the mouse to the field you want to reference. Now click the mouse and the appropriate reference is placed in the editor. Now back in teh editor add a dot and choose the method/property that you want from the object assist.

The trickiest part of these SQL statements is to get the quotes right. The SQL comand woudl be select * from table where columname = "value". It does not matter if the quotes are single or double but it must be consistent and it must have quotes. The expression that you are using to build the SQL statement is a formcalc string and there are parts that must have quotes as well. So I use single quotes for the SQL and double quotes for the formcalc...so your command woudl look like this:

Concat("Select * from performview Where Empno = '", fieldname.rawValue  ,"'")

The quote parts are hard to see so after th eequal sign would be single quote then double quote and at the end woudl be double quote, single quote, double quote. This wil produce a statement like this:

Select * from perfoemview Where Empno = '1497'

You can add a xfa.host.messageBox($sourceSet.DataConnection.#command.query.select.nodes.item(0).value) after the concatination to validate that the SQL statement is correct. This is a debug step only and shoudl be removed after validating.

Hope that helps

Paul