Michael,

Unfortunately, I don’t think FormCalc has error handling capabilities (I can’t find any documentation on it).

You can, however, convert the script to JavaScript and use its “try..catch” statement for error handling purposes when using the “open”, “first”, “addNew”, etc., data connection commands.

I think I’ve got it worked out using javascript, thanks for you help Stefan.

The above info has been very informative and useful… Thanks

Can I, or how can I open a PDF filtered to one specific record? What I would like to do is, use an asp page to search for a specific record, then pass by URL or Post, the RecordID to the PDF. Populate a hidden field with the RecordID, which would then be used in the WHERE clause of the sql query.

Thanks in advance for any info.

Thanks for the response. I tried it in its simplest form and does the trick. Now I will try a real world scenario.

Robert,

LiveCycle can most definitely get you there. That’s just some of the stuff we’ve designed it to do.

You say you have two use cases: The first is showing a blank form which your staff can use to enter new records into an existing Access database. The second is showing a form that lets your staff pick from a list of IDs or names and show the record(s) that match the query.

The first case can be achieved by tweaking the sample I posted in my instructions on how to connect a form to a database. The trick there would be first to remove all buttons except the “Update” one and change its caption to “Submit”. Then, you would simply place the following FormCalc script in the root subform (named “form1”) node’s Form:Ready event:

xfa.sourceSet.DataConnection.addNew()

Note that you must use the Hierarchy palette in order to select the root subform.

This script will cause a new record to created — but not added — for entry into the database. Filling the fields and clicking on the “Submit” button (renamed from “Update” and still using the original script) would then commit the new record to the database.

The second use case can be addressed by the sample I provided in this post. By following the sample, you should be able to design a form that uses multiple data connections: One for picking an SS#, another for picking a patient’s first/last name and another one for loading the record(s) that match the query (either the single record that matches the SS# or the one or more records that match the first/last name that was selected).

Please let me know if you have any further questions.

Dave,

You may find this post to be helpful in your situation: Using URL Requests in PDF Forms.

While it’s not directly related to ASP.NET, it shows you how to use the URL Request in a form (which means you could then easily use the concept to format a custom SQL Statement for a data connection).

Stefan

I downloaded your sample and examined the code. Now I am able to pass a value to a field. Soooo I thought, if I make that a hidden field, make the SELECT statement just
Select * FROM Incidents;
set the connection to ‘delayed open’ then use your code and add a bit to it:-

this.ui.oneOfChild.border.fill.presence = “hidden”;
this.rawValue = null;

var sURL = event.target.URL; // URL used to access this PDF form
var nRequestStart = sURL.indexOf(“?”);

// show the URL
URL.rawValue = sURL;

if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
{
var sRequest = sURL.substr(nRequestStart + 1);

// show the request
Request.rawValue = sRequest;

var aRequests = sRequest.split(“&”); // 1 request per array element

for (var i = 0; i Stefan

I downloaded your sample and examined the code. Now I am able to pass a value to a field. Soooo I thought, if I make that a hidden field, make the SELECT statement just
Select * FROM Incidents;
set the connection to ‘delayed open’ then use your code and add a bit to it:-

this.ui.oneOfChild.border.fill.presence = “hidden”;
this.rawValue = null;

var sURL = event.target.URL; // URL used to access this PDF form
var nRequestStart = sURL.indexOf(“?”);

// show the URL
URL.rawValue = sURL;

if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
{
var sRequest = sURL.substr(nRequestStart + 1);

// show the request
Request.rawValue = sRequest;

var aRequests = sRequest.split(“&”); // 1 request per array element

for (var i = 0; i < aRequests.length; i++) { var aNameValue = aRequests[i].split("="); // name=value var sName = aNameValue[0]; var sValue = ""; try { // attempt to decode the various URI encodings (regular and component) sValue = decodeURI(decodeURIComponent(aNameValue[1])); } catch (e) { // invalid URI encoding found -- skip continue; } if (sName == "Sessionfield") { // show message value in text field this.rawValue = sValue; } else if (sName == "color") { // expecting RGB value: "R,G,B" this.ui.oneOfChild.border.fill.color.value = sValue; this.ui.oneOfChild.border.fill.presence = "visible"; } } } xfa.sourceSet.DataConnection.resolveNode("#command").query.select.value = "Select * FROM Incidents WHERE Claim_Ref=" + sValue + ";" ; try { xfa.sourceSet.DataConnection.close(); } catch (e) { } xfa.sourceSet.DataConnection.open(); Then in theory it should work and populate the fields, right? Wrong! The reference ends up in the (what will be) hidden field, but the first record is displayed regardless of the number entered in the variable! Sigh Any ideas? Oh - my JavaScript like C# is non-existant - VB is more my poison and certainly more forgiving!

Dave,

You’re too fast for me! There are a number of changes in Designer 8.0 and Acrobat 8.0 combined with respect to the new version of XFA (2.5, up from 2.4 in Designer 7.x and Acrobat 7.x days) — one of which is a security feature that basically prevents you from scripting against anything but the template model (that is, the <template> node, if you were to look at the XML Source). I just haven’t had time to blog about this yet.

Therefore, you can’t do this in an XFA 2.5 form:

xfa.sourceSet.DataConnection.resolveNode(“#command”).query.select.value = “Select * FROM Incidents WHERE Claim_Ref=” + sValue + “;” ;

because this is scripting against the <sourceSet> model.

The reason why your form works when you use an old 7.x form as a template is most likely because you’re getting a form that’s already defined as an XFA 2.4 form which isn’t restricted by the security policies of XFA 2.5.

Bottom line: Keep using your 7.x forms as templates for this project until I post information about “legacy mode”.

With respect to your other question, the key thing here is that only one data connection can be open at any given time but you can open, navigate and close them in sequence. Depending on what you need to do (like the tutorial from this post), you may need to store information from those data connections in a data model that you design in JavaScript (sorry — I know you’re a VBScript kind a guy 😉 within the form (e.g. a series of nested arrays) so that you can retrieve the information after the data connection is closed.

This is basically what’s happening in my tutorial with the drop down list because I’m storing record IDs as the values of the items in the object, closing the first data connection, setting the query on the second one based on the selected record ID and the opening the second data connection in order to display the results.

I should also mention that data connections to ODBC Data Sources don’t work like data connections to XML Data files in that they don’t display all records in one shot. They only load one record at a time and it’s up to you to write the code to iterate through them and populate subforms, typically one subform per record.

Dave,

It’s a little difficult to tell exactly what’s going here because of a lack of context. Some reasons why this may not be working as you expect it to are:

• Implicit data binding where data nodes in the data connection you open have a name that matches the name of a field on your form. Data from those data nodes will automatically be pushed into those fields when the connection is opened, based on the first record.
• In your script, you’re using the same data connection to connect to two different tables. I don’t think this is a good idea, again for binding reasons, whether implicit or explicit, simply because the structure of the two tables is likely completely different.

I would recommend you try using two separate data connections (like in the tutorial in this post) and keep in mind that it’s (unfortunately) your responsibility, using script, to carry along the various values from one connection to the other.

Here is another interesting one! The form we discussed now runs fine and I deployed it on an internal W2K server inside a .NET service. Once again the pdf form ran fine from my workstation! Then I had an issue with OLEDB – the backend Sybase database runs on a Netware server under SPX – I was unable to connect after a Windows security update, but I noticed that the pdf file running on the server also failed to retrieve data (although the connection settings on the server are the same as locally and they worked fine).

So after the rambling, the question is – does something need to be installed on the server for it to run and make direct connections? Or am I missing something more obvious?

Stefan

The time thing is a real puzzler! The field is Date/Time the Validation Pattern is HH:MM:SS, the data pattern is HH:MM:SS and the data format is ‘Time.’

Bit of a mystery! Brings in the databound time, but it also has the current date.

As for the DSN, the server is running .NET on the same credentials without any problem. It’s very odd!

Stefan

Sorry – forgot to mention that the display pattern was set to ‘HH:MM:SS’ as well! The data from the back end is purely time – and outputs as that in every other front end!

Stefan

This is the odd thing – if I open Sybase Central and check the details the column is defined as ‘Time’ – in the ‘data’ tab it shows nothing but time as HH:MM:SS. If I open the table with another desktop application, like ‘DataEase’ it shows the same, as does any .NET control (Like DataGrid of DetailsView). The interesting bit is if I change the pdf field to text – and it STILL drags in the current date! Major heavy sigh!

Stefan

I have found a workaround – make the time field hidden, drag another textfield onto the form and write the following JavaScript under inialize:-

var newtime = Time_Reported.rawValue;
slicer = newtime.slice(10,16);
TextField1.rawValue = Slicer

This now returns 15:00 instead of 23/11/2006 15:00:00

I’m not sure if I might run into problems later, but…

To add to my previous post, I suppose it might be useful to know what version of livecycle designer I am using:

8.0.1291

Willie,

• There’s a difference between “testing” the connection to the ODBC data source and opening the data connection when running the form in Acrobat. When you “test” the data source connection, you’re just making sure that you can communicate with the data source using the ODBC drive and settings you’ve specified. When you execute script like “MyDataConnection.open()”, you’re now attempting to execute a connection string to get access to data within the data source. If the data connection you’re using is an SQL Query type and you’ve attempted to generate the SQL Query syntax programmatically, it’s possible you might have a syntax error in the string you’re specifying as the SQL Query for the data connection — which would explain the “invalid character” error you’re getting when you attempt to open the data connection.
• I appologize if an inconsistency between the sample and my post led to confusion. I’ll try to keep things synchronized as much as possible.
• Unfortunately, “intellisense” is something Designer and Acrobat lack right now — not to mention any sort of debugging environment. We know about these issues and are presently working on some things for an up-coming version.
• I guess you’re referring to my post on URL Requests. This information is available immediately when the form is being loaded. Since you’re wanting to use information from it to affect the query used by the main data connection for the form, I would use the Initialize event on the root subform (usually “form1”, located at the very top of the tree in the Hierarchy palette). I realize you say you’ve tried it here already and got some “untrappable” errors but without knowing more about your script, this is still where I would recommend you put it. As far as XFA objects go, they all exist the moment the form begins execution so you can get and set properties on any object in the root subform’s Initialize event. Of course, there are exceptions like attempting to get the width of a text field with a minimum width set as zero before the text field’s “Layout:Ready” event has fired. There are timing issues involved if you’re using AcroForm object properties in your script since some properties cannot be read and/or set before certain events occur on the form.
• It sounds like your data connection’s BOFAction and EOFAction properties have been set to “StayBOF” and “StayEOF”, which has the effect of moving to a blank BOF or EOF record before the first or after the last record in the set, respectively. What you want to do is specify “StayBOF” and “StayEOF” values for the BOFAction and EOFAction properties of the data connection. You can do this either by editing the properties of the data connection using the Data View palette or by script like in the Initialize event of the Data Drop Down List object found in the Library’s Custom tab.

Hopefully this answers all your questions.

Alex,

Thank you very much! I’m glad you find the information posted here to be useful.

Before we investigate this any further, are you previewing/saving your form as an Acrobat 8.0 Dynamic PDF? If so, then you’re saving your form as an XFA 2.5 form and the problem is likely the fact that you’re running into security issues when you attempt to modify DataConnection3’s SQL statement on-the-fly.

Please refer to my article on designing forms for XFA 2.5 (XFA 2.5 is new in Designer/Acrobat 8.0) and try using the cloning technique it describes.

Refer to your sample of the Selecting Specific Database Records, I couldn’t manage to resolve and some of the issues especially for Data (Select from sample xml data / field from asp.net) it’s not functioning at all. how im suppose to set the variable and to do the query for this sources. Kindly brief me as detail as u can.

ashaari,

This sample is meant specifically for data connections to ODBC data sources. If you’re attempting to import data from an XML data file or some source other than ODBC, then you’ll need to use other methods.

If you’re importing XML data, then it would either be imported via data merge on the server when the PDF is generated from your XDP form or imported manually via the Import Data dialog (from Acrobat 8.0’s “Form | Manage Form Data | Import Data” menu).

At that point, any bindings you’ve setup should function properly.

Note that importing XML data into a form implies that you have a data connection based on a schema and you may only have one of that kind of data connection per form (whereas you can have multiple data connections to ODBC or web service data sources in a single form).

Stefan,

Thanks for that.I have it working now and as you suggested it was just a syntax problem.

Now I can get cracking with the rest of the application.

Thanks again for your help.

Colin

Chris,

Am I correct in thinking that you re-created your form using Designer 8.0 in conjunction with Acrobat 8.0?

If that’s the case, then your problem is most likely that you aren’t cloning your data connection prior to modifying its properties.

New forms created in Designer 8.0 are based on XFA 2.5 by default and that subjects your form to stricter rules in Acrobat 8.0. One of these rules is that you can’t modify the form directly when it’s running in Acrobat in case someone were to decide to sign it. Because XFA 2.5 supports what I call “on-the-fly certification”, this means that your form could get signed at any point in its workflow, even without having a signature field on it to sign with. Once a form is signed, any direct modification would inadvertently invalidate the signature. Therefore, Acrobat 8.0 restricts such inadvertent modifications right from the start (even if the form never gets certified) so that you don’t risk running into problems later.

If you use the cloning method I described in my article on Better Form Design with XFA 2.5, you’ll end-up modifying an in-memory copy (clone) of the data connection instead of modifying the original one directly in the document. Your form will therefore keep functioning properly — even if someone were to decide to sign it somewhere along the way — and that “permissions configuration” error will go away.

Colin,

What you’re asking for is one of the reasons why we offer a Form Server solution.

By itself, Designer doesn’t have the capability of doing what you’re asking for.

The only alternative I can think of is to build a web service as the interface between the form (external) and your database (internal). It’s not necessarily the easiest thing to do but it would get you around having to purchase LiveCycle Forms and, depending on your requirements, would probably be easier on the budget as well.

A little more info:

The oQueryNode.commandType I was referring to is located inside the script code for the Database object variable.

I have now since gone back to redefining my connSQL database connection (previously set up for SQL Query (aka text)). It is in here that I found radio buttons for the Record Source, and I have tried to select Stored Procedure. Unfortunately, I am getting a “could not execute stored procedure ” error message. I am not sure what to do to fix this error. When I test the connection string I built, it connects ok.

I also checked the assigned database premissions to guest and to dbo for the stored procedure that I am trying to use…they have full privs. (My connection string is using user sa.)

Any thoughts?
Thanks,

John,

If the data source that your form must access is password-protected, the password must be specified in the connection string that you build using the Data Connection Wizard (the dialog that you get when you create a new or modify an existing data connection).

In your case, you would create an ODBC data connection and then select the DSN which is configured to connect to the database in question. The password is usually already specified in the DSN but you can also specify it in the data connection settings as well (on the last step there are “User” and “Password” fields you can optionally fill-in).

I am having problems getting this to work. I have the following code in the initialize of the JobAppID field:

var sURL = event.target.URL; // URL used to access this PDF form
var nRequestStart = sURL.indexOf(“?”);

if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
{
var sRequest = sURL.substr(nRequestStart + 1);
var aRequests = sRequest.split(“&”); // 1 request per array element

for (var i = 0; i I am having problems getting this to work. I have the following code in the initialize of the JobAppID field:

var sURL = event.target.URL; // URL used to access this PDF form
var nRequestStart = sURL.indexOf(“?”);

if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
{
var sRequest = sURL.substr(nRequestStart + 1);
var aRequests = sRequest.split(“&”); // 1 request per array element

for (var i = 0; i < aRequests.length; i++) { var aNameValue = aRequests[i].split("="); // name=value var sName = aNameValue[0]; var sValue = ""; try { // attempt to decode the various URI encodings (regular and component) sValue = decodeURI(decodeURIComponent(aNameValue[1])); } catch (e) { // invalid URI encoding found -- skip continue; } } } this.rawValue = sValue; And the following code in the JobAppID MouseUp: xfa.sourceSet.DataConnection.#command.query.commandType = "text"; xfa.sourceSet.DataConnection.#command.query.select = concat("SELECT JobAppDate, JobAppLName, JobAppFName, JobAppMName, JobAppAddress, JobAppCity, JobAppState, JobAppZip, JobAppHPhone, JobAppBPhone, JobAppSSN, JobAppEighteen, JobAppWkgPapers, JobAppLegal, JobAppFelony, JobAppFelonyDesc, JobAppNameDesc FROM JobApplicants WHERE JobAppID = ", 6, ";"); xfa.sourceSet.DataConnection.open(); xfa.sourceSet.DataConnection.first(); I have hardcoded the JobAppID in the SELECT statement for simplicity. Once I get this to work, I will be passing the ID in a URL. I have tried putting all of this code in the Page Initialize, all of the code in the JobAppID Initialize, with no luck. Can you tell me what I am doing wrong? I only have 1 DataConnection, because I am only working with 1 table.

Kristin,

Thanks for posting your solution.

From what I can see, it looks like your first attempt was using an invalid JavaScript function named “concat“. It’s valid in FormCalc but not in JavaScript.

The use of “contact” in your script would’ve been causing a JavaScript error which would’ve been stopping script execution prior to opening the data connection.

Your second attempt removes the use of “contact” and simply uses the plus operation to concatenate the query string with the value you got from the URL Request which is the right way to do it.

Ernest,

When you say, “the above script”, do you mean the script that Kristin posted or some other script?

As for pulling-in data from two or more tables, you can do that simply by using an SQL “join” query.

I have tried the example above, and had success to some degree. I have been able to retrieve the “RecordID” from the URL. The database connection works when I hard code it, but fails when I try to use the “RecordID” parsed from the URL. I get error: open operation failed. [Microsoft][ODBC SQL ServerDriver][SQL Server] Conversion failed when the varchar value ‘sValue’ to data type int.

I’m using Acrobat 7.0 Pro & Designer – SQL Server 2005

The code for both the URL parsing and database connection is below:

Thanks in advance for suggestions on how to correct this issue.

Ernest

—————URL—————–

// Requests:
// RecordID >> The RecordID to display, URI-encoded

// start by clearing the values first
this.ui.oneOfChild.border.fill.presence = “hidden”;
this.rawValue = null;

var sURL = event.target.URL; // URL used to access this PDF form
var nRequestStart = sURL.indexOf(“?”);

// show the URL
URL.rawValue = sURL;

if (nRequestStart > 0) // must be larger than zero or else this is a weird URL!
{
var sRequest = sURL.substr(nRequestStart + 1);

// show the request
Request.rawValue = sRequest;

var aRequests = sRequest.split(“&”); // 1 request per array element

for (var i = 0; i < aRequests.length; i++)
{
var aNameValue = aRequests[i].split(“=”); // name=value
var sName = aNameValue[0];
var sValue = “”;

try
{
// attempt to decode the various URI encodings (regular and component)
sValue = decodeURI(decodeURIComponent(aNameValue[1]));
}
catch (e)
{
// invalid URI encoding found — skip
continue;
}

if (sName == “RecordID”)
{
// show RecordID value in text field
this.rawValue = sValue;
}

}
}

———————————–Database Connection—————-

Provider=MSDASQL.1;Persist Security Info=True;User ID=XXXXXXXX;Data Source=CRMS;Extended Properties=”DSN=CRMS;Description=CRMS;UID=XXXXXXXX;APP=Adobe Designer;WSID=XXXXXXXX-NB;DATABASE=CRMS;LANGUAGE=us_english;Network=DBMSSOCN”
XXXXXXXX
XXXXXXXX

SELECT dbo.Juvenile.JuvenileID, dbo.Juvenile.TroopID, dbo.Juvenile.PostID, dbo.Juvenile.OffenseType, dbo.Juvenile.Violation, dbo.Juvenile.ViolationDate, &#xD;
dbo.Juvenile.FName + ‘ ‘ + ISNULL(dbo.Juvenile.Middle, ”) + ‘ ‘ + dbo.Juvenile.LName AS Juvenile, ISNULL(dbo.Juvenile.Title, ”) &#xD;
+ ‘ ‘ + dbo.Juvenile.ParentFName + ‘ ‘ + ISNULL(dbo.Juvenile.ParentMiddle, ”) + ‘ ‘ + dbo.Juvenile.ParentLName AS Parent, dbo.Juvenile.Address, &#xD;
dbo.Juvenile.City + ‘ ‘ + dbo.States.StateAbbr + ‘ ‘ + dbo.Juvenile.Zip AS CSZ, dbo.Juvenile.NCO, dbo.Juvenile.DateNotified, &#xD;
dbo.States.StateAbbr&#xD;
FROM dbo.Juvenile LEFT OUTER JOIN&#xD;
dbo.States ON dbo.Juvenile.State = dbo.States.StateID&#xD;
WHERE (dbo.Juvenile.JuvenileID = ‘sValue’)

Not sure where to post this on this site so figured this looked best. I am trying to follow a tutorial you had posted a while back and cannot get it to work.
Basically I need someone to choose a value from a drop down list (which is pulling from an oracle db). Then based on that drop down I want it to populate other fields on the screen. Right now I have 2 fields on the screen ZZ_CODE and ZZ_DESC. When they choose ZZ_CODE I want it to populate ZZ_DESC.
Here is my script:

—– form1.#subform[0].Body.ZZ_CODE::change: – (FormCalc, client) ———————————

var sCODE = xfa.event.newText
var sZZ_CODE = $.boundItem(sCODE)
var oDataConn = Ref(xfa.sourceSet.AbraData )

oDataConn.#command.query.commandType=”text”

oDataConn.#command.query.select = Concat(“Select distinct ZZ_DESC from PS_ZZ_HRTABLES Where ZZ_CODE = “, sZZ_CODE,””)

oDataConn.open()
oDataConn.first()
AbraSub.presence=”visible”
AbraSub.ZZ_DESC = sCODE

I get an Script Failed error – Error: syntax error near token ”

If I can get this working I would then like to be able to take multiple fields into my where clause to populate the other fields on the form. For example, i will have a country code and a state code which will drive combined the data that shows on the form. So where clause like so ” Where state = “, sZZ_State,” and country = “,sZZ_COUNT,””)

Any help would be greatly appreciated. In fact I could even fax you a beer! 🙂

Greg Blodgett,

Please see my response over on the FormCalc Expressions (If and For) article.

Ernest L. Kendricks,

Unfortunately, I don’t know much about the FDF toolkit. Perhaps there’s an Acrobat Forum that you could post into in hopes to get an answer from someone more knowledgeable in that area. Sorry I couldn’t be more helpful!

Hi Stefan,
I have a form like “Purchase Order” sample, I have 1 dropdown’s binded to 1 Data Source using XFA 2,5+ custom control, but i have the following strange efect: once i selected any item from list, the text change to another record usually the 2nd register of db, any idea ?

This is my code of DropDown

On Initiatialize Event

same as script default exept:
var sDataConnectionName = “ListaDeArticulos”; // DBConnection Name
var sColHiddenValue = “PrecioVenta”; // Item Price asociated
var sColDisplayText = “NombreArtículo”; // Text show in Drop Down

On Change Event

var sNombreArt = xfa.event.newText // Item’s Name Selected (This change before selected)
PrecioVenta.rawValue = $.boundItem( sNombreArt ); // Get Price asociated
numQty.rawValue = 1.00 ; // Default Value

Once select the item desired, the price is correct, but the Text of dropdown change to anther record

Thanks in advance for any help.

Rodolfo Hernandez,

That’s strange. When you say the text in the drop down list changes to “the 2nd register of db”, do you mean that the selection in the drop down list changes to the second item?

What script language are you using in the Change event? When I look at the script you posted, it looks like a mix of FormCalc (because of the “$”) and JavaScript (because of the semi-colons “;”). If your Change event script language is JavaScript, you should first try changing the “$” to “this” and add a semi-colon at the end of “xfa.event.newText”.

I have hunted high and low, but as a newbie at scripting LCD forms I’m stumped — I want to do something very similar to this, but instead of using a dropdown menu, I would like the user to be able to enter an ID number into a text field, click a button labeled “get employee info,” and have a handful of bound fields populate from my database after the onclick action with the employee information tied to that ID number.

I don’t feel like this should be stumping me…but it is!

Thank you!

Chris

Hi Stefan,

I have forms with ODBC connection and would like to enable modification to the database eg. addition or removal of data records.
With your “Database” script object which uses the function ExecuteSQL(), I am able to do different kind of SQL operations including SELECT, UPDATA, DELETE and so on.
My question is that how would it be to implement ROLLBACK & COMMIT in the form?

Meng,

I’m not quite certain if this would work. You would have to try to see if the ODBC driver you’re using supports the execution of the ROLLBACK and COMMIT actions. In the end, it’s just SQL so you would include it in the data connection’s SQL query. SQL statements are separated by semi-colons (;) so there’s no reason why you couldn’t include multiple statements in the data connection’s SQL query. For instance, you should be able to include multiple INSERT statements in the SQL query to execute multiple insertions rather than opening the data connection for a single INSERT statement on every repetition of a loop in order to insert multiple records into a database.

Hi Stefan,

I’m newbee in designer, javascript and formcalc. i’m using designer 8.

There is a form with 2 drop-down list (ddl). 1st ddl populates company’s department, according to user selection, staff names will be populated in 2nd ddl based on 1st ddl selection.

I found similar questions posted in Adobe Forum but no answer.

I followed your example, i can populate departments in 1st ddl, but i don’t know how to populate staff names in 2nd ddl.

—– form1.#subform[0].DropDownList1::change: – (FormCalc, client) ——————————–

var sDept = $.boundItem(xfa.event.newText)

var oDC = Ref(xfa.sourceSet.cnStaff.clone(1))

oDC.#command.query.commandType = “text”

oDC.#command.query.select =
concat(“select StaffNo, Name from vwActiveStaff where Dept = ‘”, sDept, “‘ order by Name”)

oDC.open()
:
:
—————————————————
i found difficulties…..

Thanks in advance.

YY Julie

Hi Stefan,

i found solution to add database items to list box, however, the isEOF() cannot be detected in while loop, it hangs.
i’ve used $host.messageBox($record.cnStaff.Name) to debug, it was “trapped” at last record. what do i missed?

—– form1.#subform[0].DropDownList_Dept::change: – (FormCalc, client) —————————-

DropDownList2.clearItems();

var sDept = $.boundItem(xfa.event.newText)

var oDC = Ref(xfa.sourceSet.cnStaff.clone(1))

oDC.#command.query.commandType = “text”

oDC.#command.query.select =
concat(“select StaffNo, Name from vwActiveStaff where Dept = ‘”, sDept, “‘ order by Name”)

oDC.open()
oDC.first()

while (not oDC.isEOF()) do
DropDownList2.addItem($record.cnStaff.Name, $record.cnStaff.StaffNo)
oDC.next()
endwhile
oDC.close()

Mega,

I think you’re getting a few syntax errors. If you’re using Designer 8.1, there’s a new Syntax Checker tool available from the toolbar in the Script Editor palette.

Based on the script you quoted, you’ve got the script language set to “JavaScript” yet your syntax is much more like FormCalc than it is JavaScript. You should set the language to “FormCalc” to start off. Also, here’s a few pointers related to your script:

• In FormCalc, you don’t need to specify the “rawValue” property. To access the value of the “SelectField” field, just use “SelectField”.
• “PRO.#command.query.select.nodes.item(0).value” should simply be “PRO.#command.query.select”.
• The “Rtrim” function name in the concat statement is incorrectly spelled.
• You should fix the name of your variable to be “PRO”, not “pRO”, since you’re accessing it as “PRO” in the rest of the script. FormCalc variable names are case-sensitive so “pRO” is not the same as “PRO”.
• After calling “PRO.open()”, you should call “PRO.first()” to make sure the first record is selected.

Fixing those issues should help you get your script working correctly.

Thanks alot Stefan…. I really needed those tips…my script now works flawlessly.

HELP!!!

I am using this code this link and I can get it working is says column name undefined.

Please Help!!

Nolan

For got to mention where does each piece of this code go?

Thanks Nolan

Nolan Smith,

I’m not sure I follow what you’re getting at. Does your code work now? I see that you’re not cloning the data connection which could be causing you some grief (some security error messages).

Bong Guinto,

I’m afraid I can’t help you on this one. I don’t know anything about ABAP/4. You programmatically cause a PDF, created in Adobe Form Designer, to be printed from Acrobat by using a print button from the Object Library palette.

Newbie,

You could try using the following FormCalc script after the data connection executing the query has been opened (ie. after the query has been executed):

if (xfa.dataWindow.recordsBefore == 0 && xfa.dataWindow.recordsAfter == 0) then
    // empty recordset
else
    // there's at least one record
endif

Javier,

It looks like there was a problem when you tried to include the contents of your XML files since I don’t see their contents in your post.

Nonetheless, I would suggest you make sure the fields whose values you want submitted are bound to a node in your data connection. You can check/set this using the “Object palette > Binding tab > Default Binding property”.

I probed an exaplme (Purchase Order (Designer 8.0\ES\Samples\Purchase Order\)) it comes with livecylce designer 8.0. It’s use an xlm connection to populate the form, and when you submit the form the same xml of the connection is submited.
For example, i add a dropdownlist and bound it to a list of items. When i submit, this field doesn’t appears on the xml submit file.
I repeat, can i attach a file or send you one?
Javier.

Stefan

Just wanted to say I’ve learned more on your blog than numerous posts on other Adobe forums.

I had an issue connecting a drop-down and populating it from an Access database. Just one hour here solved a problem I’ve been asking for help on for over a week.

Thanks for all the good info…..

Parwez,

If you’re problem is how to know when the user has entered a value in the text field, just put the script in the field’s Exit event (just like it is in the drop down list’s Change event) and get the new value of the text field using the “rawValue” property.

Hi Stefan,

I followed your advise in post 98. It is the perfect solution for the issue I was facing.

I think I have this figured out by adding two database connections. However, I am running into an error trying to write the select query. The variable “sElevationId” is a numeric text. In other words the database column is a text column but the text is numeric with 12 characters in a 20 character field with the 8 spaces at the beginning of the text. I get various errors when trying a variety of syntax. In the statement below, I get the error from Access that I have too few parameters, expected 1. I am sure that all of the column names are accurate.

var sElevationName = xfa.event.newText
var sElevationId = $.boundItem(sElevationName)
Options.OptionName = sElevationId

var oDataConn2 = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn2.#command.query.commandType = “text”

oDataConn2.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE OPTION_NAME = “, “sElevationId”, ” ORDER BY OPTION_NAME;”)

oDataConn2.open()
oDataConn2.first()

Thanks very much. That was the problem. Could you help some more please? I have now added script based off of Ann Meekers blog, http://forms.stefcameron.com/2008/03/07/livecycle-designer-811-sp2-posted, adding three drop down lists to my form. Each drop down list narrows the database selection until I have only one item to be selected. Is it possible to have the final selection appear in a text box or drop down list without using the change event to select the single item? In other words, in the Options.SALESOPTIONS_ID::change drop down list, only one SALESOPTIONS_ID item exists in the database with a corresponding CURR_SELLING item. However, this does not appear in the drop down list until the button is clicked. Can the CURR_SELLING item appear automatically in a text box or in a list where the item does not need to be selected?

Thanks,
Don Roessler

—– form1.#subform[0].Options.DataDropDownList::change: – (FormCalc, client) ———————

var sCategoryName = xfa.event.newText
var sCategoryId = $.boundItem(sCategoryName)

var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn.#command.query.commandType = “text”

oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE SALESPLANS_ID = “, sCategoryId, ” ORDER BY OPTION_NAME;”)

oDataConn.open()
oDataConn.first()

Options.Model = sCategoryId

var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

Options.OptionDesc.clearItems()
while(not oDataConn.isEOF()) do
Options.OptionDesc.addItem($record.DataConnection3.OPTION_DESC,$record.DataConnection3.OPTION_NAME)

oDataConn.next()
endwhile
oDataConn.close()

—– form1.#subform[0].Options.OptionDesc::change: – (FormCalc, client) —————————

var sElevationName = xfa.event.newText
var sElevationId = $.boundItem(sElevationName)

var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn.#command.query.commandType = “text”

oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE OPTION_NAME = ‘”, sElevationId, “‘ ORDER BY OPTION_NAME;”)

oDataConn.open()
oDataConn.first()

Options.OptionName = sElevationId

var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

Options.SALESOPTIONS_ID.clearItems()
while(not oDataConn.isEOF()) do
Options.SALESOPTIONS_ID.addItem($record.DataConnection3.SALESOPTIONS_ID[1],$record.DataConnection3.CURR_SELLING)

oDataConn.next()
endwhile
oDataConn.close()

—– form1.#subform[0].Options.SALESOPTIONS_ID::change: – (FormCalc, client) ———————-

var sSalesOptionId = xfa.event.newText
var sCurrSell = $.boundItem(sSalesOptionId)

var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn.#command.query.commandType = “text”
oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID WHERE SALESOPTIONS.SALESOPTIONS_ID = “, sSalesOptionId, ” ORDER BY OPTION_NAME;”)
oDataConn.open()
oDataConn.first()

Options.CurrSelling = sCurrSell

I have rewritten my script so that the initial data drop-down list filters the database items for all subsequent drop-down lists. The items in this initial data drop-down list is populated by Specifying item values in the Binding tab. Then using this subset of database items, the subsequent drop-down lists filter the database items further through use of the Enter and Change events. Then two text boxes are populated through scripting that are linked to the database item chosen in the drop-down list. The script is shown below. Is it possible to have the user view the items in the drop-down list and the corresponding values of one of the text boxes at the same time? I would like for the user to be able to view the items in the drop-down list and the corresponding costs for that item which is displayed in one of the text boxes before the user decides which drop-down item to select.

An alternative method is to display both values in the drop-down list, the item and the cost by concatenating the two database lists, however, the format of the display is undesirable. I would like to show the Item at the left side of the drop-down list and the cost at the right side border.

Thanks,
Don Roessler

—– form1.#subform[0].Options.DataDropDownList::change: – (FormCalc, client) ———————

// Get the id for the SalesPlan the user just selected (which is the id of the SalesPlan to filter on):
var sSalesPlanName = xfa.event.newText
var sSalesPlanId = $.boundItem(sSalesPlanName)

// get a *reference* to the data connection object (you’ll get an error if you try to get it by value)
var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

// First, we need to set its query command type to “text” so that we can specify an SQL statement for it.
// Otherwise, we would have to specify a table or stored procedure.
// Set the sourceSet/DataConnection/command/query@commandType attribute to “text”.
oDataConn.#command.query.commandType = “text”

// Next, we need to specify the SQL statement for the data connection. This is done by setting the contents of
// the query/select node.
oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
WHERE SALESPLANS_ID = “, sSalesPlanId, ” ORDER BY OPTION_NAME;”)

// Set the SalesPlans ID in the Options subform.
Options.Model = sSalesPlanId

—– form1.#subform[0].Options.OptionDesc::enter: – (FormCalc, client) —————————-

var sOption = “___10____”
var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn.#command.query.commandType = “text”
oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
WHERE SALESPLANS_ID = “, sSalesPlanId, ” AND OPTION_NAME LIKE ‘%”, sOption, “‘ ORDER BY OPTION_NAME;”)

oDataConn.open()
oDataConn.first()

var sBOFBackup = oDataConn.#command.query.recordSet.getAttribute(“bofAction”)
var sEOFBackup = oDataConn.#command.query.recordSet.getAttribute(“eofAction”)

oDataConn.#command.query.recordSet.setAttribute(“stayBOF”, “bofAction”)
oDataConn.#command.query.recordSet.setAttribute(“stayEOF”, “eofAction”)

Options.OptionDesc.clearItems()
Options.CurrSellElevation.clearItems()

while(not oDataConn.isEOF()) do
Options.OptionDesc.addItem($record.DataConnection3.OPTION_DESC,$record.DataConnection3.OPTION_NAME)

oDataConn.next()
endwhile
oDataConn.close()

—– form1.#subform[0].Options.OptionDesc::change: – (FormCalc, client) —————————

var sOptionName = xfa.event.newText
var sOptionId = $.boundItem(sOptionName)
var oDataConn = Ref(xfa.sourceSet.DataConnection3.clone(1))

oDataConn.#command.query.commandType = “text”

oDataConn.#command.query.select =
concat(“SELECT OPTION_DESC, OPTION_NAME, SALESOPTIONS.SALESOPTIONS_ID, SALESPLANS_ID, CURR_SELLING, SALESOPTIONSAREA.SALESOPTIONS_ID
FROM SALESOPTIONS INNER JOIN SALESOPTIONSAREA ON SALESOPTIONS.SALESOPTIONS_ID = SALESOPTIONSAREA.SALESOPTIONS_ID
WHERE OPTION_NAME = ‘”, sOptionId, “‘ ORDER BY OPTION_NAME;”)

oDataConn.open()
oDataConn.first()

Options.NameElevation = sOptionId
Options.CurrSellElevation = $record.DataConnection3.CURR_SELLING

Thanks Stefan,

Looks like I will need to learn more. But that is good. I will post another note after more progress. Thanks very much for your advice.

Thomas,

You might need to use an INSERT statement then instead of using the Add New button without first selecting a record.

Xancholy,

You can do much more than just set the email address depending on a selection in a list: See my article on submitting by email.

As for submitting the entire PDF, you can do that provided you’ve extended the PDF to enable advanced data export features in Reader using LC Reader Extensions (or if you’re submitting from Acrobat Standard or better). To submit the PDF instead of just the XML data, use a regular submit button, set its type to “submit” and then, on the “Object palette > Submit tab”, set the submit data type to “PDF”.

Peter Ilias,

To view the XML Source, click on the “XML Source” tab next to the “Design View” or “Body Pages” tab. You can also use the “View menu > XML Source” command to show the XML Source. Then scroll almost to the bottom to since the <sourceSet> node.

Milton,

I don’t quite understand. Why can’t you bind the other two fields that aren’t working?

And what are you meaning for the following code to do?

Name.rawValue = $record.DataConnection2.Name_Full.value
JoBTile.rawValue = oDataConn.JOB_TITLE.item

I have been reading through your post and I have a very simple and basic problem for the life of me can’t figure out.

This is my connection string: Provider=SQLNCLI;Server=DBSERV\SQL2005;Database=SIVAPPLICATION; Trusted_Connection=yes;

This is my select statement: SELECT EmpId, Name, Addr1, City, State, Zip, BirthDate, Department, DfltWrkloc, Phone, User5, StrtDate, PayType, Status
FROM dbo.Employee AS Employee order by empid

I have them EmpID field on the form as a drop down list, listing all the EmpID’s to choose from. The problem is I want the rest of the fields to populate according to the record row chosen with EmpID. So there for the SQL statement needs to adjust with “WHERE EmpID = ‘what ever I choose in the EmpID drop down”

Thats where I am scratching my head.

Bob,

Have you looked at the sample form at all? You’ll need two separate data connections in order to achieve what you want: The first will query the database for a list of EmpID values while the second will execute the SQL query you create dynamically after the EmpID value has been selected. Just take the sample form and reconfigure the data connections to use your database and then modify the code in the drop down list’s Change event to use your query. Once you get that working, you can use the pieces in the form to create your own.

Bob,

It’s the sample in this article…

Cesar Rodriguez,

Here’s the script in JavaScript in case it helps:

var sCategoryName = xfa.event.newText;
var sCategoryId = this.boundItem(sCategoryName);
var oDC = xfa.sourceSet.MoviesInCat.clone(1);

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

oDC.resolveNode("#command").query.select.value = "SELECT title, showTime FROM movies WHERE categoryId = " + sCategoryId + " ORDER BY title";

oDC.open();
oDC.first();

Katherine,

I’m glad to hear you’ve found my posts useful!

For your situation, I would recommend you simply follow the script that retrieves the UserID and JobID from the URL with 2 blocks of script like you’ll find in the CategoryList field’s Change event (from the sample I originally posted in this article). In the first block, “sCategoryId” would be the value of UserID and in the second, it would be the value of JobID.

David Wolf,

I see a couple of issues with your script:

1. I don’t understand the beginning. What is this doing?

 Select Preferred Name

current_field_somExpression = this;

if (typeof xfa.host.appType != “undefined”){if (this.rawValue == “netrm_empty_string”){this.rawValue = null};}
else{if (this.rawValue == “netrm_empty_string”){this.rawValue = “”};}

2. You are declaring 3 variables with the same name:

var sColDisplayText = “PrefferedName”;
var sColDisplayText = “ATCCode”;
var sColDisplayText = “ATCCodeText”;

You can only have one variable named “sColDisplayText”. I’m guessing you’re trying to have a multiple-column display however listboxes and drop down lists only display a single value unless you concatenate values together into a single value. Each item may also have a hidden data value (“sColHiddenValue” is the name of the column that contains the hidden data value).

3. In the “while(!oDB.isEOF())” loop, the items aren’t being added to the list properly:

while(!oDB.isEOF())
{
this.addItem(oValueNode.value, oValueNode.value);
//IDList.addItem(oValueNode.value, oTextNode.value);
oDB.next();
}

should be

while(!oDB.isEOF())
{
this.addItem(oTextNode.value, oValueNode.value);
oDB.next();
}

Once these errors are corrected, the list field should give you a list of drugs to choose from with the drug ID as the hidden data value. You would then follow the sample code I included in this post’s tutorial to script the list’s Change event in order to get the selected drug ID from the list and execute a query against the database to select the pertaining record. Bindings in the remaining fields to this secondary data connection (to the same database) would then be automatically populated with further information about the drug.

Hi Stefan,

This is the best site that I can always find solution for Acrobat Designer.

I have tried your sample here, I encountered the problem that I cannot modify the data connection under dropdown list’s change event, so I solve it by cloning it first:

var oDataConn = Ref(xfa.sourceSet.MoviesInCat.clone(1))

when I click any category of the dropdown list, the movie data did show properly. But when I press one of the navigation controls, say dbNext button, with coding:

“xfa.sourceSet.MoviesInCat.next()”

record that is not under the selcted category may be retrieved, that I guess is based on the original selecting criteria on MoviesInCat:

“SELECT title, showTime FROM movies ORDER BY title;”

I wonder how I can refer to the variable oDataConn (defined in CategoryList’s change event) for dbNext control? It could not be simply coded “oDataConn.next()” under dbNext. Should I define a global variable in Designer for ? And if yes, how can I do that?

Thanks in advance

Hilary,

Thanks for the complement!

You are correct in your findings. My article on XFA 2.5 explains why you need to close the data connection prior to modifying it if your form is based on XFA 2.5 or later.

What you need to do is create a script object (right-click on the root subform in the Hierarchy palette and choose “Insert Script Object” from the context menu), define a variable in it to hold the reference to the data connection (oDataConn) and, after cloning and modifying it, set the reference into the variable in the script object. Afterward, in the dbNext button, use the oDataConn variable from the script object, not “xfa.sourceSet.MoviesInCat”.

Aditya,

You basically need to set the button’s Click event to be the script to set the query on the data connection and execute it (see the script in the “Filtering Records Displayed by the movieData Subform” section of this tutorial). Once executed and the record pointer moved to the first record (calls to the open(), then the first() method on the data connection), you need to find the nodes in xfa.dataset.record that represent the columns you’re interested in and iterate through the records in the data connection, getting the values of each record node (as in the sample code in the “Making the script look for the right data” and “Generating MovieSF instances for each record” sections in my tutorial on displaying all records in a database).

Sean Hepburn,

Unfortunately, I can’t see anything in your code that would cause these errors. Are you perhaps trying to run your form in Reader rather than in Acrobat? Reader cannot access data from a database unless the PDF is extended to permit data import.

Shaun,

To get items into another drop down list, you would simply use script similar to the Initialization script used to populate the first drop down list in my sample — just use the second data connection rather than the first and make sure the second data connection’s BOF and EOF Action properties (set on the last panel in the Data Connection Wizard) are “stayBOF” and “stayEOF”. stayBOF and stayEOF will ensure that you can successfully loop through the records in the second data connection like this:

while(!oDB.isEOF())
{
    otherDDL.addItem(oTextNode.value, oValueNode.value);
    oDB.next();
}

As for the error on open(), what ODBC driver are you using?

Jane,

First things first: Are you using Acrobat Standard (or better) to test this or are you using Reader? Reader alone cannot communicate with a database.

When you test your form, are there any errors in the JavaScript Console in Acrobat? (Press Ctrl+J while in “PDF Preview” to view the console.)

Yes, FormCalc has an equivalent to app.response() which you can use in either FormCalc or JavaScript:

xfa.host.response(question[, title[, defaultValue[, useMask (1/0)]);

There isn’t a means of performing a query on a number value (1508) supplied to the field, that would replace the values of fields x y and z based on the row (1508) in the sql database?

I suppose I am having trouble converting your tutorial because it relies on a drop box with options, where I want to have fields populate based on the record number. What is different, what stays the same?

Michael K.,

If you have fields x, y and x bound to nodes within the second data connection (the one whose SQL statement you modify), then you would simply take the Change event script from my sample form’s drop down list Change event and put it in the Exit event of your text field (in a text field, the Change event occurs on every character whilst the Exit event occurs only one the field loses focus — the user moves away from it).

In the Exit event script, you would assing “$.rawValue” to “sCategoryID” rather than “$.bountItem(sCategoryName)”. The rest should be the same.

Stefan, I’m getting the following error when opening a form I created (first form). It loads the correct data on the subform based on the value of TextField1(tested multiple records) but I still get the below error. I have set up a System DSN and testing the connection works. I’m thinking that the LiveCycle connection string is not correct. Can you give me an example a LiveCycle connection string? Or if you see that there is an issue with my code, please let me know. Thanks

Script failed(language is formcalc; context is
xfa[0].form[0].topmostSubform[0].#pageSet[0].State[0].StateSubform[0])
script=

var sProductId = xfa.form.topmostSubform.#pageSet[0].State.TextField1.rawValue

var oDataConn = Ref(xfa.sourceSet.DataConnection.clone(1))

oDataConn.#command.query.commandType = “text”

oDataConn.#command.query.select =
concat(“Select ProductID, LegalName, PresFName, PresLName, PresAddress, PresCity, PresState, PresZip, PresContact, County, City, FStreet, CStreet, Zip, PrintName, Title, SignDate From tblState WHERE ProductID = “, sProductId, “”)

oDataConn.open()
oDataConn.first()

Error: open operation failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ‘=’.

Hi Stefan,

I have difficulty in populating a drop down list box values taken from a database if there is another XML dataconnection to a XML Schema

I have gone through your web sites, and figure out the way to connect to the database. Those postings are really useful.

These are the problems i am facing in the XDP file;

1. If there is another DATA Connection to an XML Schema,
The Data Drop Down list do not populate the values from the Data base. If I remove the XML Data Connection, then the Data Drop Down list works fine, by showing all the records pulled from a table.

2. The form may contain more than 10 DD List, is it necessary for me to have 10 Database open and close connections, as in the initialize script of the Custom Data Drop down list has that. I read the clone Database connection, but now sure how to apply that in the code.
Also i am not able to use the normal DD list, and populate the database values, using its dynamic properties, nothing is loaded if i use the normal DD.

Please note that, I have checked the Delayed Open, extended the form with rights,(Reader Extensions in the server), created a system DSN all that.

But still not sure why if i an XML Dataconnection, the Database connection is not being pulled in and also why normal DD is not working with the dynamic properties set.

Any lead and suggestions would be very helpful. I have been struggling to complete this. Thanks for your time.

Ds

@Ds,

I finally figured-out what’s going-on here. The issue lies in the fact that you have added a schema data connection which means that elements added under xfa.datasets.data must conform to the schema.

When you use ODBC data connections, while data is loaded under “xfa.record”, “xfa.record” corresponds, by default, to the first element below xfa.datasets.data. Since the first element below “xfa.record” is the name of the ODBC data connection, this name must match the name of a schema element directly under the schema’s root.

For example, consider the following schema:

<movieRental>
    <movie>
        <title/>
    </movie>
<movieRental>

Let’s also say you have a database that has a “movie” table in it that has a “title” column.

If you were to create a schema connection to the schema, a database connection to the database would have to named “movie” so that the resulting data element created, upon opening the DB connection, would be

xfa.record.movie.title

If you were to name your DB connection something like “MyMovieDB”, the resulting data element would be

xfa.record.MyMovieDB.title

Since “xfa.record” maps to “xfa.datasets.data.movieRental”, you would get an error like this in the JavaScript console because “MyMovieDB” is not a valid schema element:

GeneralError: Operation failed.
XFAObject.open:23:XFA:movieRental[0]:#subform[0]:Button1[0]:click
Unable to create SOM expression $record.MyMovieDB.title

John Brinkman explains this in more detail here.

Stefan,

Sorry, actually the error is:

property ‘commandType’ cannot be set because doing so would violate the documents’ permissions setting

Art

Stefan,

I have found a sample from Acrobat on browsing records stored in a XML data file.

This example demonstrates how to navigate through records stored in an XML data file by using buttons to navigate between records.

But it has to modify the XML source by adding:
entry
in order to work ok.

(DataConnection => entry => data fields) under DataView

I have tried it and works ok on a new pdf.

However, when I add to a complicated pdf, it crashes on opening after saving.
Following is the original source that I try to add after
–
….
….
….

1.7

1

*

Any ideas?
Rgds.

@helary,

The XML source you posted looks like the contents of the Config DOM. I’m not sure what this has to do with browsing the records.

Are you using an ODBC connection to an XML file (ODBC driver for XML files) in order to use a Data Connection to said file?

All the examples you show seem to use a drop down list that the user can choose from that returns another subset of data. I’d like to know if the user can open a form, enter an ID into an appropriate form field and have the form retrieve that one row of data (and no others) from the DB. Is this possible?

Thank you!

Hi Stefan,

I’ve downloaded your sample but can’t get the connection set up to see how it works.

I’ve created the DB using Access 2000 but can’t figure out how to ‘load’ the sql file so that the data is there.

I’m new to LiveCycle and have been advised to ask someone familiar with Access as to how that’s accomplished. The software doesn’t offer the ability to import that file type and haven’t found the answer using any other means.

I’m told that by modifying your sample we’ll be able to have a user type text into a text field (probably their last name) and when you tab out of that field it activates the look-up to the DB which will then show only those records that match what the user has typed. It must show numerous columns such as lastName, firstName, middleInitial etc. (all info contained in the DB) so that the user can select the correct entry which will then automatically populate other fields on the form. We need to do this with employees, establishments etc.

Step-by-step instructions would be greatly appreciated as this is a first for me and we need to create some forms that need this functionality.

Thanks,

Lena

Hi Stefan,

Thanks, but from your response I realize that what you’re doing in your example is really not what we’re needing to do. Therefore, I would prefer to continue this dialogue offline in order to provide more detail to you as to what we’re trying to accomplish. If that’s possible, then please email me.

Thanks,

Lena

Stefan

Can you provide a sample PDF with xml file that will populate a dropdown(30 values), and script that will fill 30 textfields(values from same xml) based on the DDL selected. I am running LC8.2. I have not seen “any” examples other than “switch” with one(value/text), but that coding for 30 fields is too much.

-Allan