Lookup data row to populate form.

[Former Community Member]

I have successfully created a SQL data connection. I am able to setup the basic buttons (like First, Next, Last, Add, etc) for moving through records. I can see the SQL data records in the fields I have bound to the data in the form.



What I want to be able to do is have 2 fields (username and password for example) in the form that can be used to find and select a specific row of data from the SQL database. I don't want users to be able to see and step through all these records from everyone who has ever submitted a row of data via the pdf form. I want them to only be able to see and modify their row of data. I figure the easiest way to do this is have the username/password on that same row of data and have a select statement based on those username and password fields that the user can enter. So that if username and password entered = username and password in a specific row then display that row of data in the form. This should be simple enough to do but I'm not finding the correct way to script a search/lookup button for this task.

[Former Community Member]

I have done this in an older version and I think the technique will still work in the current version. Firstly when you create your DataConnection you must set it so that it has a delayed open. There is a check box on the last screen of the data connection wizard that allows you to set this. Once this is done it will allow you to control how and when you want the data connection to happen.



Now we have to programmatically change the data base connection. Everything his held in the sourceSet object. So follow these steps to change the connection parameters:



1. First lets change the connection to accept a sql command instead of connecting and getting all records:



xfa.sourceSet.DataConnection.nodes.item(1).query.setAttribute("text", "commandType");



2. Now lets set the SQL command we want to execute:



xfa.sourceSet.DataConnection.nodes.item(1).query.select.nodes.item(0).value="Select * from Parts Where PART_NO = " + TextField1.rawValue;



3. Now we are ready to open the connection:



xfa.sourceSet.DataConnection.open()



Note that I named my connection DataConnection. This will be dependant upon what you called your data connection. Also you would put this code on the exit of the field where you enter in your userid. I woudl suggest putting on a button for testing and when you are happy with how ot works move it to the exit event of the field (then delete the button)



Hope that helps

[Former Community Member]

Thank you for the response. I tried something very similar to this syntax earlier (and your suggested syntax most recently), but I then get "Error: This operation violates your permissions configuration" in either case.



Stef Cameron has a blog that suggests these errors are caused by data connection restrictions introduced between XFA 2.4 vs. XFA 2.5. I'm using the included scripting reference from LiveCycle Designer 8.05.2073.1.374024. You'd think Adobe would do a better job of keeping up with their docs to reflect changes like this.



Any other ideas?

[Former Community Member]

I believe you can certify the doc to allow the changes to the sourceset to happen.



Or you may try changing the XFA version flag in the xml. This is a little dangerous in that if you use a feature that is specific to XFA 2.5 then you can cause unpredictable results. I do not recommend this approach but if it is a quick test to validate fuctionality then it may do the trick.

[Former Community Member]

"I believe you can certify the doc to allow the changes to the sourceset to happen."



How do I certify the pdf to allow changes to the sourceset to happen?

[Former Community Member]

There are two ways:



There is a LCES service that does this and if you do not have that you can open youf form in Acrobat and under the file menu there is a save as a certified doc.

[Former Community Member]

Saved as a certified doc after generating a certificate. Still get the same error..



>> Script failed (language is formcalc; context is xfa[0].form[0].topmostSubForm[0].page1[0].Button4[1]) script=xfa.sourceSet.MySQLDataConnection.nodes.item(1).query.setAttribute("text","commandType"); xfa.sourceSet.MySQLDataConnection.nodes.item(1).query.select.nodes.item(0).value = "Select * From commercial_electrical Where test=" +test2.rawValue; xfa.sourceSet.MySQLDataConnection.open; Error: This operation violates your permissions configuration. <<

[Former Community Member]

Did you trust the certificate used to do the certify. This certificate must be imported into the trusted store of Acrobat/Reader.

[Former Community Member]

Yes. I exported the certificate (.cer) file. Imported it into the pdf and gave it full permissions including Embedded High Privilage Javascript. Have the blue ribbon in the upper left which I'm guessing is an indicator that it is trusted. Still get the same error.

[Former Community Member]

Which version are you using ....I want to try it myself (that is supposed to work). I have done this in previous releases and it has worked. You may have stumbled across a bug.

[Former Community Member]

I'm using the following versions for this pdf...

Adobe Acrobat 8.1.2

Adobe Livecycle Designer 8.05.2073.1.374024

Thank you for checking.

[Former Community Member]

I just tried it and got the same problem .... can you open a bug.



I have a work around ....if you save the xdp out you can open it in notepad and change the xfa-template version number (4 lines down) to 2.4 the save it back out again. This is dangerous because if you are using features that are specific to xfa version 2.5 then they will not work. If you go this route then when you reopen the template in Designer it will change that number back to 2.5 or 2.6.



This will only work with Form Server as it will dynamically create the PDF at runtime.



Hope that helps

[Former Community Member]

It's funny you can't report a bug from the forum. I'll look for the bug report tool and report it.



I don't have Form Server so I guess I'm stuck for now.

[Former Community Member]

Submitted bug 3/11/2008 @ 11:01am Eastern DST.

[_Bill_Kotsifas_]

Hi Guys,



Has anyone resolved this issue, I've been looking everywhere?



Then I notice something very interesting that I also was using the following versions:

Adobe Acrobat Pro 8.1.2

Adobe LiveCycle Designer 8.05.2073.1.374024



The interesting thing is that their are 2 LiveCycle Designers....

One that comes with Adobe Acrobat Professional 8.1.2 and the other comes with LiveCycle ES Workbench, LiveCycle Designer ES 8.1.1.3021.1.1.438934 (after Service Pack 2).



I tried using LiveCycle Designer ES and things started working, may need to recreate Data Connections.



Also Paul is right about the xfa version. Acrobat LiveCycle Designer uses version 2.5 and LiveCycle Designer ES uses version 2.4



I hope this helps.



Now I have a new problem, if I certify the pdf it won't let me change the query using



xfa.sourceSet.DataConnection.resolveNode("#command.query").select.value = "SELECT ....";



It doesn't error, just executes the query that the Data Connection was originally setup with.



Thanks,

Bill Kotsifas

[Mark_Lindberg]

Have you tried cloning the connection?



This might well allow you to replace the SQL. I use this all the time, but I have not tried it with certified forms.

Below is an example where I clone my "DataConnectionContact" data connection, replacing the SQL with some that returns the selected individual.



// SET THE QUERY

var qry = "Select ......";

// PROCESS FOR GETTING DATA

var sbDC = xfa.sourceSet.DataConnectionContact.clone(1);

sbDC.resolveNode("#command").query.commandType = "text";

sbDC.resolveNode("#command").query.select.nodes.item(0).value = qry;

try {

sbDC.open();

} catch(e) {

sbDC.alert(e);

}

[Former Community Member]

I did submit the bug report but I doubt I'll ever hear anything from Adobe. It'll just start working some day after a patch .. hopefully. Bill .. how does one get LiveCycle ES Workbench? And a better question .. why would v.8.1.1.3021.1.1.438934 use xfa 2.4 and v.8.05.2073.1.374024 use xfa 2.5? One of those great mysteries ;-)

[_Bill_Kotsifas_]

Hi Mark,

The cloning technique worked fine even in a certified form, thanks.



Hi NashuaWebmaster, I got my LiveCycle Designer ES from the LiveCycle ES Trail 8.1. You can just go to http://www.adobe.com/devnet/livecycle/ and go to the "Downloads" tab you will need to login to download. The complete LiveCycle ES trial is about 3GB.



You can get lots of help from Adobe Developer Connection, tutorials, downloads, etc. Hope this helps.



As for the reason why a newer version of designer is running a older version xfa......I don't know, maybe some of the more experienced uses can answer that question.

[_Bill_Kotsifas_]

Hi Guys,



Does anyone know how to stop the prompt that pops up when you open a form in Acrobat, saying "This document is trying to access the following database:" then there is info about your Data Connection. It ask you to "Do you want to allow this access?" yes or no.



I could just click yes and tick remember this choice. But that will work for me and annoy other users.



If tried certifying the document still the same.



Is there something I have to set in form to not show warnings or this warning in particular?



Thanks,

Bill Kotsifas