Expand my Community achievements bar.

Don’t miss the AEM Skill Exchange in SF on Nov 14—hear from industry leaders, learn best practices, and enhance your AEM strategy with practical tips.
SOLVED

Drop-down list to pull in Access record

Avatar

Former Community Member

Hi -

I am a newby at this so please bear with me.  I know this have been addressed a hundred times but I can't seem to find anyone else who is having this specific issue.

I have v8.2.

Since my scripting wasn't working, I downloaded the "Adobe LiveCycle Designer 7.0, providing interactive database lookup from forms" document to try and see if I can figure out what is going on.

I built exactly as in document with exception of adding "clone(1)" as needed based on what I found in other postings about v8.2 and premission errors.

Dropdown box:

/* This listbox 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 listbox.

- Specify the table column name used for populating.

sColDisplayText

- this is the display text column of the listbox.

- Specify the table column name used for populating.

These variables must be assigned for this script to run correctly.

*/

var sDataConnectionName = “DataConnection”;

var sColHiddenValue = “ID”;

var sColDisplayText = “PART_NO”;

// Search for sourceSet node which matchs the DataConnection name

var nIndex = 0;

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

{

nIndex++;

}

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

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)

{

o

ValueNode = oRecord.nodes.item(nColIndex);

}

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

{

o

TextNode = 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();

Button:

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

$sourceSet.DataConnection.clone(1).#command.query.commandType = “text”

$sourceSet.DataConnection.clone(1).#command.query.select.nodes.item(0).value = Concat(“Select * from OfficeSupplies Where ID = “, Ltrim(Rtrim(SelectField.rawValue)) ,””)

//Reopen the Dataconnection

$sourceSet.DataConnection.clone(1).open()

endif

Preview PDF and it connects correct, drop down box populates correctly, but when I click the button, it ALWAYS populates data from first record (no matter what is selected in drop down box).  This is the same issue I had with my own build.

Anyone have any ideas what I have wrong?

Thanks!

Misty

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

The other thing I just noticed is that I generally clone the whole connection and not individual nodes in the connection like you have. Try changing your code to this:

 

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2") do

nIndex = nIndex + 1
endwhile

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//set up sql call to DB to get specifics about MRN

if (HasValue(SelectID)) then

oDB.nodes.item(1).query.setAttribute("text", "commandType");

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = '", Ltrim(Rtrim(SelectID.rawValue)) ,"'")

oDB.nodes.item(1).query.select.nodes.item(0).value = sqlString;

//now connect to DB and get a record
oDB.open()
oDB.close();

endif

Also if you copy and paste the code the quotes always get messed up ...you will have to type those in.

Paul

View solution in original post

10 Replies

Avatar

Former Community Member

To do this you will need two DataConnections .....I see that you are only using one. The 1st one is used to connect to and bind the Dropdown list. Whenit is configured it is setup to connect to the DB when the form is opened (delayed open checkbox is off by default). The second connection is the one you reference in the button and it shoudl be setup with Delayed open on  (delayed open is on the last panel of the setup wizard for the connection). This mens that the connection to the db will not happen until you tell it :

$sourceSet.DataConnection.clone(1).open()

Hope that helps

Paul

Avatar

Former Community Member

Hi Paul,

Wow, I am so not getting this.

Updated script on button to following:

if (HasValue(SelectField)) then

$sourceSet.DataConnection2.clone(1).#command.query.commandType = "text"

var sqlString = Concat("Select * FROM OfficeSupplies Where ID = '", Ltrim(Rtrim(SelectField.rawValue)) ,"'")

$sourceSet.DataConnection2.clone(1).#command.query.select = sqlString

$sourceSet.DataConnection2.clone(1).open()

endif

Built second connection with "delayed open" checked and still not getting anything but first record.  If not value in drop down, it does not return record as expected so it is looking for result, just not grabbing correct one.

Suggestions or correction very welcome.

Misty

Avatar

Former Community Member

Are you getting any errors on the java console?

Put a message box out that shows the sqlString and ensure that your sql command is correct.

Add a line after the var sqlString line like this:

xfa.host.messageBox(sqlString)

Paul

Avatar

Former Community Member

I have switched back to using this in my personal file and not test file so button script is as follows with your edit:

if (HasValue(SelectID)) then

$sourceSet.DataConnection2.clone(1).#command.query.commandType = "text"

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = '", Ltrim(Rtrim(SelectID.rawValue)) ,"'")

xfa.host.messageBox(sqlString)

$sourceSet.DataConnection2.clone(1).#command.query.select = sqlString

$sourceSet.DataConnection2.clone(1).open()

endif

And got this...

Warning: JavaScript Window -

SELECT * FROM Medication WHERE MRN = '9999'

Misty

Avatar

Former Community Member

So I assume that there is a corresponding record with an MRN of 9999 right?

Paul

Avatar

Correct answer by
Former Community Member

The other thing I just noticed is that I generally clone the whole connection and not individual nodes in the connection like you have. Try changing your code to this:

 

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2") do

nIndex = nIndex + 1
endwhile

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//set up sql call to DB to get specifics about MRN

if (HasValue(SelectID)) then

oDB.nodes.item(1).query.setAttribute("text", "commandType");

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = '", Ltrim(Rtrim(SelectID.rawValue)) ,"'")

oDB.nodes.item(1).query.select.nodes.item(0).value = sqlString;

//now connect to DB and get a record
oDB.open()
oDB.close();

endif

Also if you copy and paste the code the quotes always get messed up ...you will have to type those in.

Paul

Avatar

Former Community Member

Paul,

Thanks for being so patient.  I know you guys hate us newbies. 

This is in FormCalc, not JavaScript, correct?  I had to change != to <> or got syntax error.

After that, got the followign error message:

Accessor oDB.nodes.item("1").query.setAttribute("text","commandType") is unknown

Button know looks like this:

var nIndex = 0

while(xfa.sourceSet.nodes.item(nIndex).name <> "DataConnection2") do

nIndex = nIndex + 1

endwhile

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1)

if (HasValue(SelectID)) then

oDB.nodes.item(1).query.setAttribute("text","commandType")

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = ' ", Ltrim(Rtrim(SelectID.rawValue)) ," ' ")

oDB.nodes.item(1).query.select.nodes.item(0).value = sqlString

oDB.open()

oDB.close()

endif

Misty

Avatar

Former Community Member

Can you send me the form and the access DB and I will try it here . You will have to rename the mdb file to another extension to make it through the mail filters. Send the files to LiveCycle8@gmail.com<mailto:LiveCycle8@gmail.com>

Paul

Avatar

Former Community Member

Paul,

Files have been sent. Happy Hunting.

Misty

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----