Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

Better Form Design with XFA 2.5

You may have noticed that the new version of Designer and Acrobat that Adobe recently released uses a new version of XFA (2.5 to be exact). While the language has many new features in and of its own, the version of the language (2.5 vs 2.4 or older) also dictates how your forms will behave in Acrobat 8+ with respect to certification (digital signatures) and ubiquitization (Reader-enablement).

Background

Since this new behaviour, which is geared to encourage better form design going forward, will ultimately change the way you write certain scripts, I believe it’s important to start with some basic knowledge of XFA forms, see what happens when they get certified/ubiquitized, examine how each used to behave in Acrobat 7 and then how they’ll behave in Acrobat 8+.

XFA Primer

Simply said, an XFA form is described inside an XDP file. When it’s saved as a PDF file, the XDP is actually embedded into the PDF so that it can be processed by Acrobat.

If you look at the XDP (via Designer’s XML Source view), you’ll see that it’s simply a collection of packets that describe the form’s various components. For example, <template> describes the form’s layout and behaviour while <sourceSet> contains a collection of data connection descriptions you’ve defined using the Data View palette. When the form is loaded as a PDF into Acrobat, each packet is loaded into its own in-memory (temporary) model and that’s what you actually reference in your scripts. For example, you access the sourceSet packet via the sourceSet model with “xfa.sourceSet“. Changes to these models are reflected directly into their pertaining packets.

Form Certification/Ubiquitization

Without going into more details than are necessary, once a form is either certified and/or ubiquitized, modifications to any of the protected content (the various models) should be prevented. Modifying the form’s protected content post-certification/ubiquitization would invalidate the certification/ubiquitization status. Such modifications mean that the document is no longer in the state in which it was when it was digitally signed and therefore can no longer be trusted by the recipient as being authentic. Even though the modification may have been caused by authored script in the form, no distinction is made between those kinds of modifications and malicious attacks by an unknown party to, for instance, cause form data to be submitted to an alternate server.

Acrobat 7 and XFA 2.4

Acrobat 7 supported XFA forms up to XFA 2.4 (which Designer 7.1 would author). Once an XFA 2.4 form would be either certified and/or ubiquitized, Acrobat 7 would detect modifications to the form’s protected content and would invalidate its certification/ubiquitization status if such modifications occurred. It didn’t, however, prevent the sourceSet model from being modified post-certification/ubiquitization even though the <sourceSet> packet was included as part of the certification/ubiquitization process.

The inherent danger in this was that while any form that would do common things like display all the records in a database or select specific records in a database (filter records) — which required the modification of data connection nodes contained within the sourceSet model — would function happily at first, problems would ensue later on when a form’s certification/ubiquitization status would be inadvertently invalidated, for example, because of an unauthorized modification to a certified/ubiquitized model (sourceSet).

Acrobat 8 and XFA 2.4

In order to address the problem of inadvertent modifications (by form scripts) to certified/ubiquitized XFA 2.4 forms, Acrobat 8 was designed to prevent the sourceSet model (and any other certified and/or ubiquitized content) from being modified post-certification/ubiquitization. This means that if an XFA 2.4 form, loaded in Acrobat 8+, becomes certified and/or ubiquitized, any attempt by a form script to modify the sourceSet model (as in the two examples I mentioned earlier) will result in a security exception:

GeneralError: Operation failed.
XFAObject.setAttribute:25:XFA:form1[0]:initialize
This operation violates your permissions configuration.

One could argue, of course, that this change effectively “breaks” XFA 2.4 forms in Acrobat 8+ but in the end, inadvertently invalidating a form’s certification/ubiquitization status is likely just as bad as a security failure in a form’s script (because it attempted to modify a now-protected model) from a user experience point-of-view. As I mentioned earlier, Acrobat makes no distinction between authored scripts and malicious attacks when certified/ubiquitized content is modified — and neither does the user (in their minds, the content simply can’t be trusted any longer)!

Acrobat 8 and XFA 2.5

With a new version of Acrobat and XFA, there was an opportunity to further improve on the user experience of both certified/ubiquitized and non-certified/ubiquitized forms going forward. It was done simply by ensuring that modifications to any model that becomes protected post-certification/ubiquitization are now prevented from the start (whether the form is certified and/or ubiquitized or not) and by using XFA 2.5 as Acrobat 8+’s “trigger” for imposing the new behaviour.

The result is that we’re now forced to think about security from the very beginning of the form design process by opting to work with copies of the in-memory models (which is achieved by cloning models) rather than with the base models such that our forms don’t fail regardless of their certified/ubiquitized state. With XFA 2.5′s support for “on-the-fly” certification/ubiquitization, a form may become secured and locked-down at any point in its “live cycle” which makes it imperative to use scripting techniques which won’t fail post-certification/ubiquitization.

Legacy Mode

New forms authored in Designer 8.0 will be XFA 2.5 forms by default and you’ll need to use the new cloning technique described later in this article. That being said, if you need things to be back the way they were, there is a way that you can still use Designer 8.0 to design XFA 2.4 forms and that’s by using what’s called the Legacy Mode processing instruction.

Put simply, switch to the XML Source view for an XFA 2.5 form in Designer 8.0 and insert the following processing instruction under the <template> node (as a child element):

<?originalXFAVersion http://www.xfa.org/schema/xfa-template/2.4/?>

The result will be that Acrobat 8.0 will run your form as though it was an XFA 2.4 form — but be aware that this will also prevent you from using any of the new language extensions and APIs that come with XFA 2.5 (more on those in later posts).

(By the way, when you load an older form — earlier than XFA 2.5 — into Designer 8.0, even though the form’s version is upgraded to XFA 2.5, the Legacy Mode processing instruction specifying the form’s original XFA version is automatically added so that your form continues to work properly with respect to the XFA version is was originally designed for.)

Modifying sourceSet in XFA 2.5+ Forms

In order to avoid unexpected security exceptions in your forms after they get certified and/or ubiquitized and to handle the fact that you may not necessarily know for sure at which point in the form’s workflow that it’ll happen (if ever), you need to make sure that when you’re working with the sourceSet model, you’re actually using a cloned in-memory copy of the original sourceSet model rather than using the original sourceSet model directly.

Cloning Form Nodes

Don’t worry: You don’t have to be a scientist to use this simple technique. Using the

clone(deep)

method on the node that defines the particular data connection you’re wanting to modify within the SourceSet model and making sure your script keeps using the clone instead of the actual definition will do the trick. This method accepts a boolean parameter which, when set to 1 (or true), will clone the node and all its children (which is definitely what you want to do or else you will only get a shell instead of the full data connection) and return a reference to the in-memory copy.

As an example, let’s consider the following script taken from the Data Drop Down List object (found in the Library palette’s Custom tab):

...
var oDB = xfa.sourceSet.nodes.item(nIndex);
...
// Search node with the class name "command"
var nDBIndex = 0;
while(oDB.nodes.item(nDBIndex).className != "command")
  nDBIndex++;

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");
oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");

Notice that the script first obtains a reference to a data connection node found within the original sourceSet model and then goes on to modify some of its properties. In an XFA 2.4 form loaded in Acrobat 8+, prior to certification/ubiquitization, this will function properly although it’ll stop functioning if the form ever gets certified/ubiquitized. In an XFA 2.5 form, however, it’ll immediately fail with a security exception simply because Acrobat 8+ determines that the sourceSet model may eventually become protected and protects it from the start.

Applying the cloning technique to this script is trivial. All you need to do is change the line which accesses the sourceSet model to this:

// JavaScript:
var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);
// FormCalc:
var oDB = Ref(xfa.sourceSet.nodes.item(nIndex).clone(1))

Notice the clone(1) method appended to the end of the statement. At that point, “oDB” now receives a reference to a copy of the original sourceSet model which it’s free to modify regardless of the form’s certification/ubiquitization status. (Also note that in FormCalc, you have to wrap the statement in a call to the Ref() function which will ensure you get a reference to the cloned object.) The rest of the script doesn’t need to be modified at all!

Note that you could just as easily store the cloned data connection node into a Form Variable or a variable defined in a Script Object in order to reference it again at a later time if you make modifications to it that you would like to persist while the form is running in Acrobat.

Updated Library Objects

If you had already installed Designer 8.0 and tried using the Data List Box and Data Drop Down List objects under the Custom tab in the Library palette, you more than likely ran into the security exception I described earlier. That’s because those custom objects managed to miss the ever so important update which they required in order to function properly in XFA 2.5+ forms with Acrobat 8+ (as we saw in the previous section).

For your convenience, I’ve posted updated versions of both the Data Drop Down List and Data List Box custom Library objects which you can save to your local system and add to your personal (or shared) Library in Designer 8.0.

Updated: December 11, 2006
Updated: March 10, 2010 — Added pointer on using Ref() function in FormCalc to get a reference to the cloned data connection.


Posted by Stefan Cameron on December 7th, 2006
Filed under Acrobat,Data Binding,Designer,Scripting,Tutorials
Both comments and pings are currently closed.

98 Responses to “Better Form Design with XFA 2.5”

  1. LHigbee on December 7th, 2006

    Is this part of why we’re having problems with field and subform presence settings not switching from invisible to visible in 8.0 products? And does this also explain why the 8.0 products are not working with ADBC connections that did work with 7.0 version products?

    You mention a new version of XFA, but on the Adobe site there is no documentation anywhere – am I looking in the wrong places?

  2. LHigbee on December 8th, 2006

    About the Legacy Mode, I read that Designer 8.0 is supposed to intuitively know to open a form created with XFA 2.4 in legacy mode. But – it doesn’t seem to be working. I open the form in 8.0 and none of my database functionality works. But, if I use your workaround and insert the child element in the XML source directing it to use XFA 2.4 and then save the document with that change, the file now opens and works just like I expect it to. Is this a bug that it’s not opening it correctly in legacy mode to begin with?

  3. Stefan Cameron on December 8th, 2006

    LHigbee,

    • I’m not aware of problems with switching subforms from invisible to visible in Designer/Acrobat 8.0. If you give me a little more detail on what’s happening, I might be able to figure-out what’s going on.
    • Unfortunately, I don’t think the new XFA 2.5 spec has been posted at the spot where I linked to in my post. It should be up there soon.
    • About the need for Legacy Mode, this is an Acrobat 8.0 specific thing. Are you saying your data connections don’t work in Designer 8.0 at design-time or in Acrobat 8.0 at run-time? When you look at the XML Source view in Designer 8.0 (where you added the Legacy Mode processing instruction), can you perhaps find an identical processing instruction near the end of the <template> element which actually specified XFA 2.5 as the form’s original version? It is technically possible to have the template set to XFA 2.4 but have the processing instruction specify XFA 2.5 and Acrobat 8.0 will load it as XFA 2.5, which would break your data connections (unless you fixed them with the cloning method).
  4. LHigbee on December 11th, 2006

    Thank you for the reply Stefan. I can send a copy of the form to you – where can I get your email address? I’m thinking that we’re going to find that it is something about the ADBC data connections in my form. Does XFA 2.5 still support the ADBC object?

    I’ll keep looking for the new documentation.

    My data connections don’t seem to run at run-time in either Designer PDF preview mode or in Acrobat 8 (or Reader 8 for that matter). I don’t know if they work at design time since this is a form created with Designer 7.0 and the data connections are through ADBC rather than the data bindings most Designer forms probably use.

    I’ve since uninstalled 8.0 from my machine – I kept getting a splash screen every time I opened Acrobat 8.0 saying that I was in the 30 day grace period (even though we have a production copy of 8.0 and a license number), and when I tried to click on the Download eLicense all I got were errors saying that my system was not set up correctly and I’d have to contact our Adobe rep for help.

    I use Acrobat every day – it’s vital to the work I do. And this Designer form gets used 30+ times every day. So when it wouldn’t open correctly in 8.0, I started getting behind on work and finally had to uninstall it and reinstall 7.0 so that I’d be able to get work done. I will reinstall on another machine and follow your instructions above to see if that makes a difference.

    I really appreciate your help with this. I love Designer – it has allowed me to create a form which saves me hours of work every week, so I’m anxious to know what I can do to keep the form working in newer versions. :)

    LHigbee

  5. LHigbee on December 13th, 2006

    I could find nothing further in the XML source that specified XFA 2.5 (I perhaps could have missed it.)

    I’m very, very frustrated with this – we’ve called Expert Support and I was told that if it involved scripting that they didn’t support it. When I tried to explain that it wasn’t my scripting, but that it was a potential issue with an object in the new version of XFA built into the product, they still said that they provided no support for that. They suggested I post to the forums. I have – one person responded and asked me to submit a copy of the form, but I haven’t heard back since then. No one else has any ideas…

    I’d look it up in the XFA 2.5 documentation, but there isn’t any…is there a chance I could get a draft copy? I’m not sure what else to do to resolve this.

  6. Stefan Cameron on December 13th, 2006

    LHigbee,

    I’m very sorry that this issue has resulted in a lot of frustration on your part.

    I will send you an email to the address you provided with your comment to solicit your form for further investigation. I can’t promise I’ll find a solution nor can I promise I’ll find it quickly but I’ll do my best to figure-out what’s going on.

    As you do, I suspect it’s something with Acrobat’s support of ADBC via XFA forms. I assume you’re using ADBC via JavaScript in XFA events rather than using the usual ODBC data connections that are supported in XFA. Nonetheless, I’m surprised that your form would be broken in Acrobat 8.

  7. LHigbee on December 13th, 2006

    Again, thank you! I know this one is probably very technical, so I figured I’d need a developer to help me out – I appreciate whatever time you could give me with this or any suggestions.

    Yes, I’m using ADBC through Javascript on the form initialize event and then in some button scripts. Problems arose because I was trying to write info to a database that has an auto-incrementing primary key (actually, three tables with auto-incrementing primary keys). The ADBC object calls an already established ODBC connection set up through the Control Panel | Administrative Tools | Data Connections.

    I did read your previous post regarding the data connectivity and the issues with the auto-incrementing primary key, and your solutions for getting around those, but I couldn’t find an option that would work in my situation. Possibly because I don’t know enough of the technical stuff yet.

    Again, thank you for any help you might be able to give. For the time being, we’ll just have to alert my form users that they can only access and use the form in Acrobat/Reader 7.x versions. I’m really curious about what might be causing this.

  8. Stefan Cameron on December 13th, 2006

    LHigbee,

    I did some digging and I believe I have found the answer to this problem.

    It appears that ADBC was deemed a security risk and therefore has been disabled by default in Acrobat 8. The reason being that it makes no efforts to validate that the form’s user has agreed to permit the connection. In some ways, you could draw parallels between this and proctecting the SourceSet model in Acrobat 8 with XFA 2.5, as I mentioned in this post.

    Fortunately, there’s a simple way to enable ADBC on a system and it can be found in the Acrobat 8 SDK Readme. Since that link is currently broken, here’s the excerpt you’re interested in:

    ADBC Support

    Acrobat Database Connectivity (ADBC) can now be turned on and off via a registry setting. To activate ADBC, create a registry key of type DWORD with the name “bJSEnable” and a value of “true” (1) in the following location:

    HKEY_CURRENT_USER\SOFTWARE\Adobe\Adobe Acrobat\8.0\ADBC

    This activates ADBC in Acrobat 8.0. In previous releases of Acrobat, ADBC was active by default. In Acrobat 8.0, this setting has been changed to require user intervention to activate ADBC because most users do not want to have ADBC accessible from PDF.

    Windows shell command to activate ADBC:

    reg add “HKEY_CURRENT_USER\SOFTWARE\Adobe\Adobe Acrobat\8.0\ADBC” /v bJSEnable /t REG_DWORD /d 1

    Note that ADBC is still only available via Acrobat Standard or Professional and on Windows only. Reader doesn’t have the ability to use ADBC.

  9. LHigbee on December 14th, 2006

    Beautiful – that is exactly the info that I needed! Thank you SOOOOOOOOOO much!

  10. Rick Kuhlmann on January 31st, 2007

    Can you give us/me a date for the availablity of the 2.5 XFA specifications?

    Thanks.

  11. Stefan Cameron on February 5th, 2007

    Rick,

    Unfortunately, I can’t give any specifics but I would expect it to be posted within the next few months.

    In the mean time, is there a specific XFA 2.5 question I might be able to answer for you?

  12. Rick Kuhlmann on February 7th, 2007

    Stefan,

    Thanks for the information. I have found the current XFA 2.4 document very helpful especially when writing javascript.

    Waiting a little longer for XFA 2.5 will, I am sure, be worth the wait.

    Thanks,

    Rick Kuhlmann

  13. LHigbee on February 15th, 2007

    Hi Stefan,

    Thanks again for the info on getting my form working in 8.0. We’re still on 7.0 officially, and we just updated to 7.0.9 and now the form no longer works – doesn’t recognize the established data connection. Is this possibly the same thing, that they’ve turned off ADBC as a security precaution now? HELP!!!!

    LHigbee

  14. LHigbee on February 15th, 2007

    Stefan,

    It appears that the ADBC is indeed turned off now with the 7.0.9 update. So I went in and added a new registry key for 7.0 based on the one you posted previously for 8.0.

    I’m really quite miffed about this – turning ADBC off in an existing version when it worked in all previous updates is something I consider to be a HUGE, HUGE change. And I think that there should have been some warning that the 7.0.9 update turned ADBC off due to security issues. Is there anywhere on the Adobe site where there is documentation on what the 7.0.9 update includes? I didn’t see anything about it from the auto-updater feature within Professional. Where do I find out what each of these updates is going to change or fix before I update?

    So, this leaves me with a rather large dilemma – it’s obvious I’m going to have to stop using ADBC for the data connection in my form. But in the alternate methods, I’m not sure I understand how it would handle locating an Access database that is in different locations on each of 20+ computers. Do you know where I could find more info on that? I guess I just don’t quite grasp how it would work.

    Thanks again for all your help.

    LHigbee

  15. Stefan Cameron on February 15th, 2007

    LHigbee,

    You’re correct: ADBC is, in fact, disabled by default in all flavours of Acrobat 7.0.9, as well as 8.0.

    I wish it would’ve been clearer to everyone downloading the update — as well as installing Acrobat 8.0 — that this change was going to happen and I’m sorry it caused you so much grief. Unfortunately, the only documentation I can find on Adobe’s web site about the update is a security bulletin but it doesn’t mention anything about disabling ADBC. I’ll do some digging to see if I can find more detailed information about the update.

    As for migrating to XFA data connections from ADBC, I don’t know ADBC well enough to tell whether the migration will be easy or not but the one thing they have in common is ODBC. That is, you can create data connections in XFA forms which connect to databases via ODBC using a DSN just like you use a DSN with ADBC.

    Theoretically, as long as all the computers have the same DSN setup, whether the form uses ADBC straight from Acrobat or an ODBC data connection within XFA, there shouldn’t be any difference in the way the form connects to the database and the physical location of the database on each computer shouldn’t matter either since it’s abstracted by the DSN.

    There will undoubtedly be differences in the way the form interacts with the data connection as opposed to interacting with ADBC and for that, you can always ask me questions or post to the Designer Forum.

  16. Jackie on February 28th, 2007

    Hey I read over this and I apear to not be able to solve my problem by adding the .clone to my project I still get the

    This operation violates your permissions configuration

    Error now I am a newbie but here is what I am doing…

    I create a data dropdown and I set the text value to a column called name and the hiddenvalue t0 the auto increment ID. Once the item is selected from the drop down I have the user click a refresh button like in the 7 code and use the same code given there

    if (Len(Ltrim(Rtrim(DataDropDownList.rawValue))) > 0) then
    $sourceSet.DataConnection.#command.query.commandType = “text”
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(“Select * from testDb Where ID = “, Ltrim(Rtrim(DataDropDownList.rawValue)) ,”")
    //Reopen the Dataconnection
    $sourceSet.DataConnection.open()
    endif

    Any ideas guys?

    thanks

  17. Stefan Cameron on March 8th, 2007

    Jackie,

    I believe you simply aren’t using the “.clone” method in the correct location.

    Based on the script you outlined, you’re still modifying the Data Connection node directly:

    $sourceSet.DataConnection…

    If you change your script to first retrieve a reference to a cloned version of the DataConnection node, you should be fine (note the use of the Ref function to retrieve a reference to an object in FormCalc):

    var oDC = Ref($sourceSet.DataConnection.clone(1))

    if (Len(Ltrim…) then
        oDC.#command.query…

    The important thing to remember from this article is that if you’re working on an XFA 2.5 (Acrobat/Designer 8.0) form, you must always work on a cloned copy of any Data Connection node (node parented to the $sourceSet model) otherwise you’ll run into these security exceptions.

    Updated Mar 21, 2007: Jackie, Doug’s comment below (on Mar 19) made me realize that I forgot to use the “Ref” function to retrieve a reference to the cloned copy of the data connection in memory when scripting in FormCalc. This is vital to the remainder of your script functioning properly. I’m sorry I didn’t realize this earlier.

  18. fursten on March 12th, 2007

    Hello Stefan,

    Regarding Form Certification/Ubiquitization,can you tell me where can I read more about Form Certification?

    I need to find a solution to this situation:

    My forms have scripts that change the layout of the form depending on who is opening it (for instance, if the user is from the financial department or form any other department).
    At the same time, each kind of user needs to digital sign the form.

    So for instance, a user at the financial department opens a form and see specific text in some text fields or he can even see specific input fields (some can be hidden).

    After he digital sign the document another user from another department will open the form, but while opening the form, the scripts in the form will change some text in the text fields and hide (or not) some sections or some input fields.

    Despite the form is being programmatically changed, this user need to see the digital signature from the first user, and the signature needs to be valid. He will then place his own digital signature on the document.

    I know Adobe as the LiveCycle Designer workflow for doing this, however my solution can´t be dependent of a specific technology. Plus, I think that while using livecycle workflow, these different views of the forms would be accomplish with rules that would create different files. However, I would like to accomplish this kind of functionality using only one file.

    Is this possible?

    Thank you

  19. Stefan Cameron on March 16th, 2007

    fursten,

    Applying a Certification Signature to a form enables the verification of the integrity of the document at the time at which it was signed but still permits changes to the form, if the author of the signature decided to allow changes of a specific type.

    Based on that, if the signatures applied to the form in its various stages throughout the workflow and departments allow for changes to the form, then I believe you would be able to achieve what you’re attempting to do (a single form which modifies itself as per each department).

  20. Doug Einstadter on March 19th, 2007

    I’m having the same problem that Jackie noted above. I’m using Adobe Acrobat 8 Professional and LiveCycle Designer 8.0. I’m trying to add a drop down list and Refresh button to a form to allow the user to go to any record in the database. I’m using the code described in the article ‘Adobe LiveCycle Designer 7.0 Providing interactive database lookup from forms,’ modified by addition of a clone(1) reference as suggested.

    Here’s my code for the Refresh button:

    var oDC = $sourceSet.FCGDB.clone(1);

    if (Len(Ltrim(Rtrim(SelectID.rawValue))) > 0) then
    //Change the commandType from TABLE to TEXT. TEXT is the equivalent of SQL Property

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

    //Set the Select Node. Select in this case will be whatever the SQL Property you want.
    oDC.#command.query.select.nodes.item(0).value = Concat(“Select * from fcg1 Where ID = “, Ltrim(Rtrim(SelectID.rawValue)) ,”")

    //Reopen the Dataconnection
    oDC.open()

    endif

    Adding the .clone(1) reference solved the permissions error, but now I get the following error:

    accessor ‘oDC.#command.query.commandType’ is unknown.

    Any ideas what the problem might be?

    Thanks for any assistance.

  21. Stefan Cameron on March 21st, 2007

    Doug,

    I’m glad you asked this question because you made me realize that I made a mistake when I answered Jackie’s question earlier:

    I’m noticing now that both you and Jackie are using FormCalc script to do your work as opposed to JavaScript.

    The major difference between the two, in this case, is that in JavaScript, the call to “clone(1)” will return a reference to the new cloned data connection object in memory. In FormCalc, however, you must explicitly request a reference to the cloned object. Otherwise, you seem to end-up with a copy of a generic object which isn’t what you’re expecting it to be (hence the error about the unknown accessor).

    The correct syntax for cloning the data connection object in FormCalc is the following:

    var oDC = Ref($sourceSet.FCGDB.clone(1))

  22. osama on March 25th, 2007

    Hi advance
    I’m design form with Adobe Designer. I can’t send data
    to database access or sqlserver.
    I hope give me and to learn me what I doing
    thanks

  23. Mike Fitzgerald on March 26th, 2007

    Hey Guys,

    I’m in the same boat with this example. Here is my code:

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

    var oDataConn = Ref(xfa.sourceSet.DataConnection.clone(1))
    oDataConn.#command.query.commandType = “text”

    oDataConn.#command.query.select =
    concat(“SELECT LN_APP_GNRL_A1.acn,LN_APP_GNRL_A1.ln_app_no,LN_APP_GNRL_A1.nam
    FROM LN_APP_GNRL_A1 WHERE LN_APP_GNRL_A1.LN_APP_NO = “, 22809)

    oDataConn.open()
    oDataConn.first()

    I now get the “expect a dict object”

    Please help.

  24. Stefan Cameron on March 28th, 2007

    osama,

    I’m afraid I’ll need some more details about the problem you’re running into.

    What exactly is your form attempting to accomplish?

    Are you getting a specific error message when you run your form in Acrobat?

    Have you checked the Acrobat JavaScript Console for error messages (press “Ctrl + j” in the Preview tab or in Acrobat to show the console)?

    What versions of Acrobat and Designer are you using?

  25. Stefan Cameron on March 30th, 2007

    Mike,

    Unfortunately, I can’t see what’s wrong with your script based on what you quoted in your comment. It all looks good to me.

    Is the error message telling you the line which contains the error?

  26. Mike Fitzgerald on April 5th, 2007

    Stefan,

    Can you provide me some clarifcation? Can I have a form that I designed in lifecycle 8.0 with data access, enable it in professional and have it work in reader 8.0. After talking to adobe support, they didn’t seem to think that reader has data access.

  27. Stefan Cameron on April 6th, 2007

    Mike,

    If you extend your form using either Acrobat 8.0 Professional or Reader Extensions, it will then enable data import in the free Adobe Reader for that specific form so that it can retrieve data from a database.

  28. Jeff Drouet on May 15th, 2007

    Stefan,

    I am having the same problem that Jackie and Doug had with the “accessor ….. unknown” error. I have tried the cloning method and have “explicitly requested a reference” to the cloned object like you advised Doug. I am still gettin the same error. Here is my code:

    ——————————————–
    var oDataConn = Ref(xfa.sourceSet.DataConnection.clone(1)) // get a *reference* to the data connection object (you’ll get an error if you try to get it by value)
    oDataConn.#command.query.commandType = “text”;
    oDataConn.#command.query.select.nodes.item(0).value = “SELECT * FROM main WHERE ((AppID)=2242);”
    oDataConn.open();
    ——————————————–

    My data connection seems to be working fine – I get the first record in the database upon previewing the pdf. When I click the button with the above code attached to it with the plan of getting another record (AppID 2242 in this case), I get the accessor unknown error.

    I have searched your forums thoroughly and cannot seem to find anything that will allow me to implement this type of solution without having my client spend thousands of dollars on LiveCycle Forms. CAN THIS WORK???

    thanks in advance….

  29. Maxx on May 15th, 2007

    Stefan,
    I believe I’ve got this working to spec so far in designer 8… we’ve got a database where employee reviews are submitted to by up to 9 people who review that employee. Amongst others, there’s a Responses table, and an Employees table. The form I’m designing to pull up and print the reviews after they’re submitted works for selecting a review number from a dropdown, and it then populates that employee’s name and other information. What I’m having problems with is the next section which lists the question that was asked in the review, and then is supposed to list each response to that question from the 9 reviewers. All the responses are in the same column in the Responses table of the DB, and so I can’t figure out how to display all those values at once. If I make 9 text fields, the only way I can get them to display a value is to bind them to the response column of my Response-table data connection… but then all 9 of those fields always display the same thing. Am I seriously missing something here, or is there any way to do this (display all 9 values that my SQL-query pulls from the same DB Field at the same time)? Thanks for any help, even if it’s just a “nope… not possible” ;)

    maxx

  30. Stefan Cameron on May 16th, 2007

    Jeff Drouet,

    Assuming you’re running your form in Acrobat Standard or Pro (or you’ve extended the form and you’re running it in Reader), you should be able to do this.

    The only thing I can see wrong with the script you posted is the following line:

    oDataConn.#command.query.select.nodes.item(0).value = “SELECT * FROM main WHERE ((AppID)=2242);”

    More specifically, I question the use of “nodes.item(0)” after the “select” property. “select” doesn’t contain any nodes, which would explain the “accessor unknown” error. Try removing that and using only “select” (in FormCalc, you don’t have to specify “value” since it’s used by default):

    oDataConn.#command.query.select = “SELECT * FROM main WHERE ((AppID)=2242);”
  31. Stefan Cameron on May 16th, 2007

    Maxx,

    As long as the form is used in Acrobat Pro or Standard (or is extended and used in Reader), you should be able to do this.

    Based on your explanations, it sounds like you’re basically wanting to filter a table for responses to a question and the result is 9 rows, each containing a column which holds the actual responses.

    What you need to do is use a second data connection who’s SQL Query can be set to a query string you build depending on selected values (which will let you filter the table for the responses you’re looking for). Then, once you open the connection, you need to iterate through the records and create instances of a repeatable subform which contains a text field which will, in turn, contain the response to the question from a particular reviewer. That’s the part I think you’re missing.

    A little while ago, I wrote an article on displaying all records from a database. It contains JavaScript that opens a data connection, loops through the records and creates an instance of a repeatable subform for each record, populating the subform instance’s fields with record data.

    Have a look at that sample. Create a second data connection for this purpose. When the selection is made and you have enough data to filter the table for the responses, execute a function that’ll set the second data connection’s SQL Query to properly filter the table and then loop through all records returned and create/populate subform instances.

  32. Josko Radic on August 21st, 2007

    Hello Stefan

    I am having exactly the same problem as Jeff Drout. I am using Adobe Acrobat 8.1 and LiveCycle Designer 8.0 trial version, Adobe Reader 8.1.
    Pdf is Acrobat 8 (Static).
    Code works in Acrobat, breaks in Reader 8.1 at exactly first line even when it is “Reader Extended”. It looks like sourceSet collection is empty when user clicks the
    button which results in accessor unknown error ($sourceSet.nodes.length=0).

    Here is the code
    var oDC = Ref($sourceSet.DataConnection.clone(1))
    oDC.#command.query.commandType = “text”
    oDC.#command.query.select = Concat(“Select * from Brod Where BrodID = “, BrodID2.rawValue, “”)
    oDC.open()

    I can send you pdf and mdb.
    Thank you very much in advance.

  33. Stefan Cameron on August 30th, 2007

    Josko Radic,

    What happens if you save your form as a dynamic PDF form? You could also convert your script into JavaScript to see if that makes a difference. Sometimes the JavaScript engine handles things a little better than the FormCalc one does.

  34. Vinayak Sapre on May 19th, 2008

    Hello Stefan,
    I reached your blog while searching for a sample that demonstrates use of clone function. We are using Designer / FormServer 7. As per XFA 2.4 doc, I can modify the template model before data merge.

    Since combs do not work on non-interactive static PDF, I am planning to write a function to simulate combs. I have added vertical lines in a subform which I turn on or off. This still leaves issue of character spacing. I am thinking of adding series of 1 char text fields dynamically. I am able to clone a field. But I don’t know how to add it to the template DOM.

    Thanks in advance.

  35. Eric Horde on May 22nd, 2008

    Stefan, I’ve been studying this blog for several days now and I still can not get my forms to populate from the database. The dropdown works and although I have whittled the myriad of errors slowly away I am stuck at this one when I click the refresh button.

    Error: open operation failed. Data type mismatch in criteria expression.

    Here is my code for the button;
    ————
    var oDC = Ref($sourceSet.BBCC.clone(1))

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

    oDC.#command.query.select.nodes.item(0).value = Concat(“Select * from dbo_BB_vw_MergeDocuments Where JobDescription = “, (JobSelect) ,”")

    oDC.open()
    ———-

    Any help is greatly appreciated,

    Thank you,

    Eric Horde

  36. Eric Horde on May 22nd, 2008

    Update

    Changing to:
    ——
    var oDB = xfa.sourceSet.BBCC.clone(1)
    xfa.sourceSet.BBCC.#command.query.commandType = “text”
    xfa.sourceSet.BBCC.#command.query.select.nodes.item(0).value = Concat(“Select * from dbo_BB_vw_MergeDocuments Where JobDescription = ‘”, JobSelect.rawValue, “‘”)
    oDB.open()
    —–

    Results in: Error: This operation violates your permissions configuration.

  37. Stefan Cameron on May 22nd, 2008

    Eric Horde,

    In the first version of the code, I think the error is related to “(JobSelect)” in the Concat function: It’s not being implicitly converted into a string and so the Concat function doesn’t know what to do with the value.

    In the second version of the code, you’ve fixed the “(JobSelect)” problem by accessing the “rawValue” property which returns a string. Now the problem is that you’re creating a clone of the data connection but you’re not using it in the subsequent lines: Change “xfa.sourceSet.BBCC” to “oDB”.

  38. Eric Horde on May 22nd, 2008

    Stefan, thank you for replying. I have done a lot of vb and dotnet stuff over the years but this LiveCycle is down-right irritating.

    Code is now:

    —-
    var oDB = xfa.sourceSet.BBCC.clone(1)
    oDB.#command.query.commandType = “text”
    oDB.#command.query.select.nodes.item(0).value = Concat(“Select * from dbo_BB_vw_MergeDocuments Where JobDescription = ‘”, JobSelect.rawValue, “‘”)
    oDB.open()
    —-

    Resulting in:

    Error: accessor ‘oDB.#command.query.commandType’ is unknown.

  39. Stefan Cameron on May 22nd, 2008

    Eric Horde,

    I think you still need to get a reference to the cloned data connection (I forgot to mention that in my previous reply as well):

    var oDB = xfa.sourceSet.BBCC.clone(1)

    needs to be

    var oDB = Ref(xfa.sourceSet.BBCC.clone(1))

    If you’re familiar with JavaScript (C# is sort of similar), you might want to switch the script language to JavaScript (you’ll have to update the script syntax accordingly). I’ve always found it much easier working with JavaScript than FormCalc, especially with data connections. If you switch to JavaScript, you won’t need the “Ref()” function since you automatically get the reference to the cloned object.

  40. Eric Horde on May 28th, 2008

    Stephan, finally got it working, thanks for all the feedback. (you realize you’re the only one on the internet with productive advice on LiveCycle right?)

    The only problem I have left is getting it to work in reader. It craps out in reader with a script error. Do I have to “extend” the form? If so, what does that mean and how do I do that? Once again Adobe IS NOT intuitive on this.

    Here is the final working code for the Dropdown.

    *************************************

    —– topmostSubform.Page1.JobSelect::initialize: – (JavaScript, client) —————————

    // This dropdown list 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 dropdown. Specify the table column name used for populating.
    // sColDisplayText - this is the display text column of the dropdown. Specify the table column name used for populating.
    //
    // These variables must be assigned for this script to run correctly. Replace with the correct value.

    var sDataConnectionName = “BBCC”; // example – var sDataConnectionName = “MyDataConnection”;
    var sColHiddenValue = “ContractAdmin_Key”; // example – var sColHiddenValue = “MyIndexValue”;
    var sColDisplayText = “JobDescription”; // example – var sColDisplayText = “MyDescription”

    // 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);
    oDB.open();
    oDB.first();

    // Search node with the class name “command”
    var nDBIndex = 0;
    while(oDB.nodes.item(nDBIndex).className != “command”)
    {
    nDBIndex++;
    }

    // Backup the original settings before assigning BOF and EOF to stay
    var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute(“bofAction”);
    var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute(“eofAction”);

    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(“stayBOF”, “bofAction”);
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(“stayEOF”, “eofAction”);

    // Clear the list
    this.clearItems();

    // 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(oTextNode.value, oValueNode.value);
    oDB.next();
    }

    // Restore the original settings
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, “bofAction”);
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, “eofAction”);

    // Close connection
    oDB.close();

    *******************************************

    And here is the final working code for the buttton.

    *******************************************

    —– topmostSubform.Page1.Button1::click: – (FormCalc, client) ————————————

    var oDB = Ref(xfa.sourceSet.BBCC.clone(1))
    oDB.#command.query.commandType = “text”
    oDB.#command.query.select.nodes.item(0).value = Concat(“Select * from BB_vw_MergeDocuments Where ContractAdmin_Key = ‘”, JobSelect.rawValue, “‘”)
    oDB.open()

  41. Eric Horde on May 28th, 2008

    I forgot to add that all(most) of the query work is done at the SQL server via a “view”. LiveCycle ODBC connection does not let you see the views you create in sql, only tables. If you simply type in the name of the view in the box where it wants to select the table, it works anyway.

  42. Eric Horde on May 28th, 2008

    Stephan, I think the problem reader is having is with the FormCalc for the button.

    I am going to try and change:

    ******************
    —– topmostSubform.Page1.Button1::click: – (FormCalc, client) ————————————

    var oDB = Ref(xfa.sourceSet.BBCC.clone(1))
    oDB.#command.query.commandType = “text”
    oDB.#command.query.select.nodes.item(0).value = Concat(”Select * from BB_vw_MergeDocuments Where ContractAdmin_Key = ‘”, JobSelect.rawValue, “‘”)
    oDB.open()

    **************************

    To JavaScript.

    The problem is I don’t know Java. Oh well, that’s never stopped me before.

    … to be updated later …

  43. Eric Horde on May 28th, 2008

    Soory for all the posts, I probably should have grouped these together….

    Here is the verbatum FormCalc error for the button that is popping up in Reader only.

    **********************
    Script failed (language is formcalc; contect is
    xfa[0].fomr[0].topmostSubform[0].Page1[0].Button1[0])
    script = var oDB = Ref(xfa.sourceSet.BBCC.clone(1))
    oDB.#command.query.commandType = “text”
    oDB.#command.query.select.nodes.item(0).value = Concat(”Select * from
    BB_vw_MergeDocuments Where ContractAdmin_Key = ‘”, JobSelect.rawValue,
    “‘”)
    oDB.open()
    Error: accessor ‘xfa.sourceSet.BBCC.clone(“1″) is unknown.

    **********************

    ???

  44. Stefan Cameron on May 28th, 2008

    Vinayak Sapre,

    Modifying the Template DOM is certainly not something that will continue to work with later versions of Designer and Acrobat (and I can’t say that I was aware this was actually ever possible) but if you can and want to do it, then I think you would have to get the subform’s child list (a treeList object) and use its “append” method to add the cloned object to the subform:

    theSubform.nodes.append(theTextEdit.clone(1));
  45. Stefan Cameron on May 28th, 2008

    Eric Horde,

    Thanks for the feedback on the data connection stuff. I’ll be sure to pass it along.

    While I would suggest you switch to JavaScript (since I’ve seen strange errors with respect to data connections when using FormCalc in the past), I’m afraid that wouldn’t help you much since your form would, as you suggested, have to be extended (using LiveCycle Reader Extensions) in order to import/export data in Reader. Without extending the form, only Acrobat Standard and Pro can import/export data.

  46. Eric Horde on June 11th, 2008

    What Now *%$@!

    Dropdown was displaying all records in the database and working fine. After spending 5 days on the forms and mathcing up all the fields I was finally done!

    So I thought

    During final testing today the Dropdown has decided to now only dispaly the first 5 records. ??? (!#$%&@!)
    I double and triple and quadruple checked the code and nothing has changed.

    I do not want to think I have wasted 5 days of matching fields to the form.

    Any thoughts?

    Here is the dropdown’s code again…

    ——–Begin Code——

    var sDataConnectionName = “BBCC”; // example – var sDataConnectionName = “MyDataConnection”;
    var sColHiddenValue = “ContractAdmin_Key”; // example – var sColHiddenValue = “MyIndexValue”;
    var sColDisplayText = “JobDescription”; // example – var sColDisplayText = “MyDescription”

    // 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);
    oDB.open();
    oDB.first();

    // Search node with the class name “command”
    var nDBIndex = 0;
    while(oDB.nodes.item(nDBIndex).className != “command”)
    {
    nDBIndex++;
    }

    // Backup the original settings before assigning BOF and EOF to stay
    var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute(“bofAction”);
    var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute(“eofAction”);

    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(“stayBOF”, “bofAction”);
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(“stayEOF”, “eofAction”);

    // Clear the list
    this.clearItems();

    // 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(oTextNode.value, oValueNode.value);
    oDB.next();
    }

    // Restore the original settings
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, “bofAction”);
    oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, “eofAction”);

    // Close connection
    oDB.close();

  47. Eric Horde on June 11th, 2008

    Here is the error that is now generated.

    Error: next operation failed. Row cannot be located for updating. Some values may have been changed since it was last read.

    It would be nice if the message displayed the “Row” in question. Why is trying to update anything? The connection is set to READONLY on purpose.

    I suspect it is vomiting over a null value somewhere.

  48. Eric Horde on June 11th, 2008

    Fixed it. Changing the Data Connection ADO properties to READ ONLY made the error go away. All works now.
    What I don’t understand is why that did not cause any problems duing the intial dropdown testing.

  49. Eric Horde on June 11th, 2008

    And yes I have been working on it for 5 straight hours!

  50. Stefan Cameron on June 11th, 2008

    Eric Horde,

    That’s a really frustrating problem indeed! I’m glad you figured it out in the end.

    I’m curious: Did you mean for this data connection to be read-only in the first place or were you wanting to use it as a read/write data connection?

  51. Eric Horde on June 12th, 2008

    Read only, the Forms I am working with are Local and State Regulatory forms that are usually in a package of about 30-40 pages. All the data we use on the forms is contained in our database. A person could spend hours filling out the forms by hand. The agencies are VERY picky about thier forms too. No modifications or alterations. So, what I did was compile all their forms together into one big ala-carte menu driven package. I then plugged all the database fields into the form and using a dropdown I have it working where we select the project from a dropdown and select the forms to print and “PRESTO” 30 Seconds instead of 3-4 hours.

    I do have another question if I could pick your brain one more time.

    Here is an example of a print button’s code that prints pages 16 and 17 of the form.

    xfa.host.print(1, “16″, “17″, 2, 0, 0, 0, 0);

    I know what the first 3 parameters are for, but are the remaining parameters documented anywhere? I have searched to the ends of the internet and even java references without luck.

    I would like to auto-set some of the print buttons to print 2 copies of a particular form.

    Thanks for all your help..

    Eric

  52. Eric Horde on June 12th, 2008

    Found it.

    xfa.host.print(1, “startPageIndex”, “endPageIndex”, 1, 0, 0, 0, 0);

    In order…

    Print User Interface
    Start Page
    End Page
    Silent Mode
    Shrink to Fit
    Print As Image
    Reverse Order
    Annotations

  53. Aleksey K. on June 13th, 2008

    I need to have several digital signatures on my form. When i create a form and run it, adobe tries to save the file every time I apply the signature. Can I run a script or is there an option to apply all the signatures and have only the last signature to save the document. So that a filling person does not have to save the same form two times or more for each signature he applies.

  54. Stefan Cameron on June 14th, 2008

    Eric Horde,

    Thanks for taking the time to explain your project. It’s really cool that you’re able to save 3-4 hours of work filling those forms!

    Pick my brain? Sure but you’re going to fast for me to keep up! I’m glad you found what you were looking for. In the future, you should use the LiveCycle Designer ES Scripting Reference to get that information (see the print() function details on page 389). You can find other helpful documentation under the “Develop” tab on the Adobe LiveCycle ES Documentation site.

  55. Stefan Cameron on June 14th, 2008

    Aleksey K.,

    Unfortunately, I don’t think it’s possible to avoid having to save the form every time a signature is applied. Put simply, signing a form produces a hash of the form’s current state using the information from the digital signature certificate. A new version of the form must be saved to preserve this state. This also enables a viewer to get information about the state of document, such as whether it was modified after it was signed.

  56. Massimiliano Pecoraro on June 17th, 2008

    Hello to everybody, i’m writing from Italy (Rome).
    I’m at my first experience with Adobe Livecycle (i’m a VB programmer), and I need a form that read from and write into an Oracle table.
    With more difficult (and a big help from this site) I realized all that I need, but now there is a problem:
    Our PDF does not work with all the computers of the Company, even if there is Acrobat Writer 8 and the Oracle Client installed.
    I converted all the scripts from FormCalc to JavaScript, then tested the Connection (inside the LiveCycle designer of the problematic pc) and there is no problem.
    But in run-time, no actions is performed.
    With the FormCalc the error is the classic accessor $sourceSet.DataConnection.clone(“1″) is unknown.
    In JavaScript no errors but no action at least.
    I manually debugged row by row and seems that the xfa.sourceSet connection is empty!!
    the value of the xfa.sourceSet.nodes.length is zero!!!
    Why this happens on certain pc?
    All the pc have the same installation: Acrobat 8 professional with LiveCycle, Oracle Client, Windows 2000 Professional.

    Does anybody have some ideas?

    Thanks a lot

    Massimiliano

  57. Eric Horde on June 18th, 2008

    Stephan, I have a new issue (seems to be a lot of these in livecycle / or with me ;-) )

    The dropdown is not displaying new records from the data base.
    It is almost as if the dataconnection is working from “cached” data.

    If I open up the form in livecycle designer and refreshing the connection and then save the form, the dropdown then shows new records from the database.

    Something simple I have overlooked perhaps?

  58. Eric Horde on June 18th, 2008

    Adding a button that does

    sourceSet.BBCC.requery();

    fixes the dataconnection refresh issue.

    Shouldn’t this take place on some kind of “form open?” process though?

  59. Eric Horde on June 18th, 2008

    Stefan,

    I don’t know if this will work but I am going to try this in the dropdown code:

    var oDB = xfa.sourceSet.nodes.item(nIndex);
    oDB.open();
    oDB.requery();
    oDB.first();

    Thanks,

    Eric

  60. Stefan Cameron on June 21st, 2008

    Massimiliano Pecoraro,

    Unfortunately, I don’t have any suggestions. All I can say is that if there is a problem on certain PCs and not others, the problem would likely be between Acrobat and the database. When you create a data connection in Designer, it merely attempts to access the database to get the structure of the records it will load but it may do it in a different way that Acrobat does. When you’re running the form in Acrobat (i.e. at runtime), Acrobat is the application attempting to establish the connection to the database as defined in the form’s data connection. That’s probably “clear as mud” — I think this one is out of my hands.

    I just had a thought: Could it be that on some PCs, you’re running Acrobat and the form under a user that has more privileges than on others (hence it works on some machines and on others it fails silently when attempting to connect to the database because of a user rights access violation)?

  61. Stefan Cameron on June 21st, 2008

    Eric Horde,

    According to the documentation, “calling the requery() method is equivalent to calling the close() and open() methods in succession.” It sounds like it would do what you were wanting. Did it work? I’m not certain but it’s possible that Acrobat caches, to some extent, the query results upon opening the connection, which would explain why you don’t see new records (or changes to existing ones) if they’re added after the connection has been opened.

  62. Massimiliano Pecoraro on June 24th, 2008

    Stefan, thanks for your reply.
    The user was always the same, probably there is some technical problems just in some PCs.
    Otherwise the thing is not so tragic, we tested the application on PCs with the standard installation and it works fine!!!
    I have just another curiosity: I opened my document (positioned inside our LAN) with Internet Explorer from a PC that has no Oracle Client installed, and the doc was unable to establish the connection.
    So is not possible to realize server-side connections? If our “mission” was the document be avalaible for Internet Users what was the right way?

    Excuse me for my horrible english, and thank you so much for your very usefull blog!

    Max

  63. Stefan Cameron on June 25th, 2008

    Max (Massimiliano Pecoraro),

    You’re very welcome! I’m glad it ended-up working in the end.

    When you use a database connection, each system that uses the form must have access to that database via ODBC. In your case, that also involves each system having the Oracle Client installed on it. If you wanted this form to be used over the Internet by people who didn’t necessarily have the database connection setup, you would have to implement a web service and connect your form to it. The web service would then be exposed publicly (on the Internet) and would interface with the database in the back-end.

  64. Roman Shypailo on July 6th, 2008

    Stefan,

    I’ve spent most of the weekend working through this blog (and many others), mostly trying to get the standard database form example to work (using the dropdown box and refresh button referenced exhaustively here). Yes, I have a much better understanding of the process, but I have a few very fundamental questions that are still not clear.

    My company has a website with many PDFs that customers can fill out and mail in – think of them as order forms. Because certain fees are changing rapidly, they want to make the PDFs dynamic, so that data (prices, e.g.) can be pulled from a database to populate the forms. This way, the myriad of PDF files do not need to be altered, as I’m hoping to distill the number of files down to a minimum.

    Anyway, I’m unclear as to what tools will really make this work. I’m sure the customers will be using Adobe Reader. I got the example form to work with Adobe Pro, and it will work with Reader as long as the data is only initialized (the Refresh button will not work in Reader). The database connection was fairly simple, but will that work on a website form?

    In short, I want to pre-populate PDFs using a database and put them on a web site to be read by Adobe Reader. I don’t anticipate pulling the data back into the database. Can this be done with the techniques listed in the blog, or do I have to go to the XML / ASP.NET or whatever route? And do we need Reader Extensions or some of the Enterprise software packages? The choices are mind-boggling. I would very much appreciate your input on this.

    Thanks,

    Roman

  65. Chris Jasabe on July 10th, 2008

    I am working in Adobe LiveCycle Designer 8.0 and have created designed a form that has four fields (ID_NUMBER, FIRST_NAME, MIDDLE_NAME, LAST_NAME). They are all text fields but in the ID_NUMBER I am trying allow a user to input a ten digit number and have the related row data for the other three fields populate. I successfully created my connection to the Oracle database that has the data source (a table named “IKON_DATA_OUTPUT”) and when I click on the Preview PDF tab the pdf from that is created show the first row of data in the oracle table. The problem occurs when I try to update the ID_NUMBER with an existing ten digit number…on pressing enter the data should update bringing in the related row values for the inputted ID_NUMBER but it is not doing that. My guess is that the script below is referencing in SQL and not appropriate for retrieving a record in oracle. The examples I have seen on the internet are all related to SQL server connections so I am in a bit of a bind. PLEASE HELP!

    This is a snippet of the xml source code with the script in question….

    //Change the commandType from TABLE to TEXT. TEXT is the equivalent of SQL Property

    $sourceSet.DataConnection.#command.query.commandType = “text”

    //Set the Select Node. Select in this case will be whatever the SQL Property you want.
    $sourceSet.DataConnection.#command.query.select.nodes.item(0).value = Concat(“Select * from IKON_DATA_OUTPUT Where ID_NUMBER = ‘”, SelectField.rawValue, “‘”)

    //Reopen the Dataconnection
    $sourceSet.DataConnection.open()

  66. Stefan Cameron on July 13th, 2008

    Roman Shypailo,

    The main issue with forms that have database connections is the fact that if the PDF is connected directly to a database via an ODBC data connection, everyone who opens the PDF must have the same DSN configured on their system to connect to the database. If people outside your organization will be using this PDF, this likely won’t be a viable option.

    To get around this problem, you would typically create a public-facing web service and connect your form to it. The idea is that the web service would connect to the database and pull-out the necessary data. This option is also much more secure than exposing your database on the Internet…

    Finally, if users of the PDF will be using the free Reader to fill-out the form, the PDF will either need to be extended with data import capabilities using LiveCycle Reader Extensions ES or you’ll have to use LiveCycle Forms ES to serve-out the pre-filled (with your database data) PDF to the client (browser).

    I summary I think you have the following options:

    1. Connect your form directly to the database:
      1. everyone has the DSN configured and uses either Acrobat Standard or Pro to open the form.
      2. read-extend your form and users can use Reader or Acrobat to open the form.
      3. use LC Forms to serve the PDF to the browser. Reader Extensions is not needed and users can use Reader or Acrobat to open the form.
    2. Connect your form to a web service, web service to the database:
      1. Acrobat Standard or Pro is used to open the form.
      2. read-extend your form and users can use Reader or Acrobat to open the form.
      3. use LC Forms to serve the PDF to the browser. Reader Extensions is not needed and users can use Reader or Acrobat to open the form.
  67. Stefan Cameron on July 17th, 2008

    Chris Jasabe,

    Have you read this blog post yet? It explains how in Designer 8.0 and above, you need to clone the data connection prior to modifying it otherwise you’ll get a security error and your script won’t execute. I would first try cloning the data connection to see if that fixes your issue:

    var dc = Ref($sourceSet.DataConnection.clone(1)) // in FormCalc
    dc.#command.query.commandType...
    dc.#command.query.select...
    dc.open()
  68. Geir Istad on August 5th, 2008

    Howdy Stefan! It seems like every time I google for a Designer problem/solution your name ends up on the very top of the result list, great work!

    Now I have an issue with a form I’m trying to glue together, what I want is a ‘address book’ function where I pick a name out of the (now working) listbox that is populated with data from a SQL server, in two different dataconnections.

    One DB is ADRLIST containing catID and catName.
    Other DB is ADRDATA that contains KundeID, Navn, Avdeling, Addresse, Postnummer and Sted.
    Both data connections are linked to the same SQL database, just displaying different (except KundeID) info.
    The data is in one table named ‘Kunder’

    I also have a collection of Textboxes linked to ADRDATA; Avdeling, Addresse, Postnummer and Sted.

    This is my SQL query for ADRLIST
    SELECT KundeID AS catID, Navn AS catName FROM Kunder ORDER BY Navn
    (My reason for changing the variablenames for KundeID and Navn is basically so it’ll be easier on the eye for me.)

    My SQL query for ADRDATA
    SELECT KundeID, Navn, Avdeling, Addresse, Postnummer, Sted FROM Kunder ORDER BY KundeID

    This is my my variables in the default listbox in designer 8.0.
    var sDataConnectionName = “ADRLIST”; // example – var sDataConnectionName = “MyDataConnection”;
    var sColHiddenValue = “catID”; // example – var sColHiddenValue = “MyIndexValue”;
    var sColDisplayText = “catName”; // example – var sColDisplayText = “MyDescription”;

    Now all of the above are working (afaik) as it should, my problems are with the Change script I am (trying) to use to display the contact information of the people in the address book .

    Script is in FormCalc, my scripting knowledge are next to nil so I’ve already given up trying to work it out in js.

    var xDB = Ref(xfa.sourceSet.ADRDATA.clone(1))
    var xKundenummer = xfa.event.newText
    var xId = $.boundItem(xKundenummer)
    xDB.#command.query.commandType = “text”
    xDB.#command.query.select = concat(“SELECT Avdeling, Addresse, Postnummer, Sted FROM Kunder WHERE KundeID = “, xId, ” “)
    xDB.open()
    xDB.first()

    Could it be an issue with me only having one table and basically cat KundeID to.. compare and find KundeID in the same Table? Although on different data connections.

    The error I am getting now is;

    “Error: open operation failed. Incorrect syntax near ‘=’.”

    I’ve looked over the code probably 15 times, compared and cross compared to other similar snippets and I just cant figure out what is wrong.
    Also, I’ve noticed Ref($sourceSet… and Ref(xfa.sourceSet… are both used, are there any differences?

    Either way if I could get some assistance with this script I would be forever grateful (and so would my boss!) as it is the last part of a huge form-rewamp in my company.

  69. Nolan on August 7th, 2008

    Hi,

    I am using an access Db and trying to pass parameter to the form to display a specific id anyways I am getting a data type mismatch in criteria expression.

    xfa.sourceSet.museum.#command.query.commandType = “text”
    xfa.sourceSet.museum.#command.query.select.nodes.item(0).value = Concat(“SELECT tblJewelleryItem.JewelleryItemID, tblJewelleryItem.CustomerID, tblCustomers.Title, tblCustomers.FirstName, tblCustomers.MiddleInitial, tblCustomers.LastName, tblCustomers.Address1, tblCustomers.Address2, tblCustomers.City, tblCustomers.Province, tblCustomers.PostalCode, tblJewelleryItem.DateOfAppraisal, tblJewelleryItem.PurposeOfAppraisal, tblJewelleryItem.Gold, tblJewelleryItem.Description1, tblJewelleryItem.Description2, tblJewelleryItem.Appraiser FROM tblCustomers INNER JOIN tblJewelleryItem ON tblCustomers.CustomerID = tblJewelleryItem.CustomerID WHERE tblJewelleryItem.JewelleryItemID = ‘” ,Message.rawValue, “‘;”)
    xfa.sourceSet.museum.open()

    Can you help me out.

    Thanks,

    Nolan

  70. Stefan Cameron on August 13th, 2008

    Geir Istad,

    When in FormCalc, there’s no difference with “$sourceSet” and “xfa.sourceSet” — they mean the same thing. “$sourceSet” is just a short-cut (not much of one, but that’s what it is).

    As for the script error, I’m a little puzzled. Does the error message give you a little more context, like a line number? Your script looks fine to me. You could always try the Syntax Checker in the Script Editor to see if it would flag the line with the error (in Designer 8.1+, the errors will appear in the Warnings palette) or you can check the Log palette to see if there’s more information there.

  71. Stefan Cameron on August 13th, 2008

    Nolan,

    Could it be that the data type for the “tblJewelleryItem.JewelleryItemID” column is a number and by wrapping “Message.rawValue” in single quotes, you’re passing the value of the Message field to the database as a string instead of a number, causing an unsupported string-to-number comparison?

  72. Robert on September 9th, 2008

    Hello,

    These pages have always been great help, but now I don’t seem able to solve my problem w/o even more help.

    Which problem?
    Well, a data connection works in Acrobat Pro, but not in Reader. And I’ve “extended” the form.

    My set-up:
    - Dev-env = LiveCycle Designer v8.05.2073 (on WinXP, everything fully patched)
    - Form = Acrobat 7 (Dynamic) XML Form
    - Form “extended” with Acrobat Pro 8.1.2 tru “Enable Usage Rights in Adobe Reader…”
    - Choices:
    — ODBC to System DSN
    — Javascript, not Formcalc
    — Legacy mode (yes, I’m lazy)
    —– I’v entered:

    Here’s the script to retrieve data depending on user input:

    var oQuery = xfa.resolveNode ( ‘sourceSet.mw.#command.query’ ) ;
    oQuery.select.value = ‘ SELECT * FROM [EMPDATA$] WHERE ID = ‘ + IDinput.rawValue + ‘ ; ‘ ;
    xfa.sourceSet.mw.open();
    xfa.sourceSet.mw.close();

    This works like a charm in Acrobat Pro 8 versions on various machines (configured with the needed system DSN) , but not in Reader (7 or 8) on those same machines.
    And it won’t work either in Acrobat Pro 7.
    It seems that those versions (Pro 7 & Reader 7 or 8) don’t know “xfa.sourceSet”.

    How do I get this workig in Reader 7 AND 8?

  73. Stefan Cameron on September 12th, 2008

    Robert,

    The issue here is that using the “Enable Usage Rights in Adobe Reader” feature is not equivalent to using LiveCycle Reader Extensions ES to enable data import features in the free Reader.

  74. Suresh talla on September 17th, 2008

    Hi Stefan…..Im beginner to the Adobe Livecycle designing…So plz can u forward me the code or an example code with explanation for the below task…..
    I need to design a pdf form which includes (textfields,checkboxes,dropdownlists and many other controls…)…..My task is : On a button click I have to insert the filled form data into sql database……My E-mail Id is : suresh0534@gmail.com

  75. Robert on September 18th, 2008

    In reply to reply #73:

    Tnx Stefan,

    I could have guessed it wouldn’t be that easy.
    Solution is gonna be: buying Acrobat Pro for all intended users (±15).
    Way more economical than one Reader Extension license.

    To cover all possiblities:
    In reply #27 you mention extending forms using Acrobat 8.0 Professional, preparing them for data import in Reader.
    But I’ve got the feeling that that is no longer applicable.
    Am I (or my feeling;) correct?

  76. Stefan Cameron on September 24th, 2008

    Suresh talla,

    I suggest you have a look at all of my data binding articles/tutorials. There’s lot of information and examples there to get you going.

  77. Stefan Cameron on September 24th, 2008

    Robert,

    That’s correct. Back when I answered Mike Fitzgerald’s question, I was under the impression, as you were, that Enable Usage Rights in Adobe Reader actually enabled data import because the feature does enable “local save” which I thought was the same permission. It turns-out that they aren’t the same permissions. Importing data is something totally different than saving a form’s state. So to import data into a form in Reader, you either need Reader Extensions (if you want the import to happen client-side) or you need LiveCycle Forms (if you want the import to happen server-side and the resulting PDF sent to the client who can then open it in the free reader with the data already in it).

  78. Suresh talla on October 6th, 2008

    Thanks for Reply…Stefan Cameron

    We have successfully inserted the data from PDF Form into the Sql DataBase using WebServices in Dotnet. Now the Problem is :

    I have created a LoginPage in Dotnet( i.e ASPX Page) to Authenticate User…..On successfull Login I want to display the Same UserName on the Acrobat Form ( i.e PDF Form) when the PDF Form gets loaded for the first time…………So my question is how to pass the UserName From ASPX Page to PDF Form……If possible plz send the example code…..

  79. Stefan Cameron on October 9th, 2008

    Suresh talla,

    While this isn’t the most secure way to get data into a PDF from a web page, it might do the trick for you: Using URL Requests in PDF Forms

  80. TJ Dohjerty on October 28th, 2008

    Stefan -

    I am trying to populate a dropdown list (filtered) and am using Designer 8.2. The following code works correctly in Acrobat Pro 9.0 but fails in Acrobat Reader 8:

    var sEmployeeName = xfa.event.newText
    var sEmployeeID = $.boundItem(sEmployeeName)
    var oDataConn = Ref($sourceSet.connEmployeeEmail.clone(1))
    var sReturnMail = sEmployeeName
    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select = concat(“SELECT EmailAddress FROM Contacts WHERE ContactID = “, sEmployeeID, “;”)
    oDataConn.open()
    oDataConn.first()
    oDataConn.close()

    When it fails, I get that “accessor ‘xfa.sourceSet.connEmployeeEmail.clone(“1″)’ is unknown. Any ideas??

  81. Stefan Cameron on November 4th, 2008

    TJ Dohjerty,

    It’s likely because you can’t import data in Reader without extending the PDF or using LC Forms.

  82. TJ Doherty on November 7th, 2008

    Stefan -

    Thank you for the reply. Perhaps you misunderstood my question. I have extended the PDF for data access in Reader. I am able to access unfiltered data from my DSN using the Reader. However, when I attempt to populate a filtered dropdown list using the above script, it fails in Reader and gives me the “accessor ‘xfa.sourceSet.connEmployeeEmail.clone(“1″)’ is unknown” message.

  83. Stefan Cameron on November 10th, 2008

    TJ Doherty,

    How exactly have you extended your form? Did you do it by enabling data import using Reader Extensions or did you use Acrobat Pro’s “Extend Feature in Adobe Reader” tool?

  84. TJ Doherty on November 11th, 2008

    Stefan -

    I used Acrobat Pro’s “Extend Feature in Adobe Reader” tool.

  85. Stefan Cameron on November 11th, 2008

    TJ Doherty,

    That’s the problem: Extending the form in this way does not enable any data import features which are required to interact with data connections in the free Reader. This feature just enables Reader to save the data entered in the form. In order to do this in Reader, you’ll need to use LiveCycle Reader Extensions ES.

  86. Murat on November 18th, 2008

    Hi Stefan,
    First of all I have thank for your web site. It’s very helpful to me.
    I have 2 drop down list on my form. Both of them is connected to Access databases. (Countries and cities.) I want to select country on first drop down list then I want to show just selected country’s cities on the second drop down list.
    What I did till now is;

    I wrote this on first drop down list’s “change” event

    var sCategoryId = country.rawValue
    var oDataConn = Ref(xfa.sourceSet.DataConnectionCOUNTRY.clone(1))
    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select = concat(“SELECT * FROM COUNTRY WHERE IL_ID = “, var sCategoryId)
    oDataConn.open()

    I don’t have any message but i doesn’t work? Do you have any idea?

    Thank in advance..

  87. Stefan Cameron on November 19th, 2008

    Murat,

    First, you shouldn’t precede “sCatagoryId” with “var” in your concat statement.

    Second, if IL_ID is a number, then simply removing “var” should make it work. If it’s a string, you’ll need to wrap sCategoryId’s value in quotes so that the database interprets the value as a string instead of some number or other type of identifier:

    ... = concat("SELECT * FROM COUNTRY WHERE IL_ID = '", sCategoryId, "'")
  88. Murat on November 21st, 2008

    Hi Stefan,
    I have changed my script as you advice.

    I wrote the script (below) on first DDL’s (drop down list) exit event. But nothing happens.. I choose “A” from first DDL but still get all list of second DDL.

    form1.#subform[0].buyuk::exit – (FormCalc, client)
    var sCategoryId = buyuk.rawValue
    var oDataConn = Ref(xfa.sourceSet.DataConnectionKUCUK.clone(1))
    oDataConn.#command.query.commandType = “text”
    oDataConn.#command.query.select = concat(“SELECT * FROM KUCUK WHERE KUCUKID= “, sCategoryId)
    oDataConn.open()

    I have uploaded pdf and access db at this link. Could you please have a look at it?
    http://www.medyasoft.com.tr/poc/tuik/ddl.rar

    Thank in advance..

  89. Murat on November 25th, 2008

    Hi all,
    We found the solution;

    form1.#subform[0].buyuk::change – (JavaScript, client)

    var sCategoryId = this.boundItem(xfa.event.newText);
    var sql = “SELECT * FROM KUCUK WHERE KUCUKID = ” + sCategoryId;

    var oDataConn = xfa.sourceSet.DataConnectionKUCUK.clone(1);
    oDataConn.nodes.item(1).query.setAttribute(“text”, “commandType”);
    oDataConn.nodes.item(1).query.select.nodes.item(0).value = sql;
    oDataConn.open();
    oDataConn.nodes.item(1).query.recordSet.setAttribute(“stayBOF”, “bofAction”);
    oDataConn.nodes.item(1).query.recordSet.setAttribute(“stayEOF”, “eofAction”);

    kucuk.clearItems();
    kucuk.rawValue=”"
    var i = 1;
    while(!oDataConn.isEOF() && i < 10)
    {
    kucuk.addItem(xfa.record.DataConnectionKUCUK.KUCUKNAME.value, xfa.record.DataConnectionKUCUK.KUCUKID.value);
    oDataConn.next();
    }
    oDataConn.close();

    You have to make two data connection. First DDL must be binded with connection, second is not.

    Thanks..

  90. Patti on May 5th, 2009

    Hello,
    Am trying to create simple pdf form using livecycle 8.0
    Wheni nter employeeid, it should display employee info
    but i get “This operation violates your permission configuration”

    xfa.sourceSet.DataConnection2.#command.query.commandType = “text”

    Please help…am using Acribat 8 to view/run it

    also, if possible please let me know if i can use ASP page to output query on PDF form….i am trying to print mailing labels using PDF document

  91. Stefan Cameron on May 15th, 2009

    Patti,

    As I mention in this post, you need to clone your data connection before you can modify it. Acrobat 8 supports XFA 2.5 which means it falls under the new security measures that require cloning.

    For outputing the query to ASP, you would have to submit your form to your ASP page using an HTTP Submit button.

  92. Amy Wall on July 2nd, 2009

    Stefan,
    I’m am completely confused and ready to give up. I have a form created in Adobe LiveCycle Designer 8.2 ES to run in Adobe Acrobat Standard and Pro 9.0. Currently the company has job jackets with all customer and job information filled in by hand. I created the form so the info could be typed in and printed onto the jacket. I was able to link the form to a database of customer info so instead of keystroking all the customer info it’s now in drop down lists. I’ve been trying to find a way to update or link the info so once the customer’s name is entered all the customer specific info fills in (i.e. business name, address, email etc.) I know nothing about writing script. I’ve been going through these messages for months and still can’t seem to figure out what to do. I don’t even know what script to write. I’ve tried using the suggestions in many of these messages but nothing works. Do you have any suggestions? Everything I’ve tried comes back with errors from accessor errors to scripting syntax errors.

  93. Mark A on July 6th, 2009

    Stefan,

    I too am very thankful for this informaton! It’s allowed me to take care of many issues less this one. I’ve created a form with LiveCycle 8.2.1. When I open this up within Adobe Acrobat 9 Pro the form allows me to click on the drop down, select an ID, and then click on the refresh button and have the remainder of the data display. This uses the “purchase.mdb” file.

    I’ve opened the PDF form within Adobe Acrobat Pro 9 and extended the features. I saved the form, opened it with Adobe 8.1 and I’ve lost the drop down and data display functionality.

    Any thoughts or suggestions on this?

    Thanks, Mark

  94. bloonsterific on July 10th, 2009

    Just wanted to tell you all know how much I appreciate your postings guys.
    Found you though google!

  95. Stefan Cameron on July 14th, 2009

    Amy Wall,

    I’m sorry to hear you’re having such a difficult time getting your solution to work.

    What you should do is try to follow my tutorial on selecting specific database records. In that tutorial, I show how to load a list of values into a drop down list and then show only records that match the chosen value. The list would be customer names and, assuming each name is unique (or has a unique ID associated to it that you can search on), the info specific to that customer could then be loaded from the database.

  96. Stefan Cameron on July 15th, 2009

    Mark A,

    Unfortunately, extending the PDF with Acrobat does not enable the PDF for “data import” — which is required for importing data from a data connection in Reader. You would need to extend the form using LC Reader Extensions to enable data import, or execute the data connection on the server using LC Forms.

  97. Ed on March 5th, 2010

    HELP!!!!
    I have Acrobat 9 pro and it comes with lifecycle 8.2 ES
    I decided to try the data drop down with an address block for a simple database hello world form.
    I have read every web reference to this (on the surface) simple hello world style forms project.
    In the process I’ve learned formCalc, accessors, clone(1), Ref, the XML form model, sourceSet
    binding, nodes, items, shortcut formCalc notation, form vars, defining OLEDB and OLEDB ODBC (tried both).
    I’ve coded the change event at least 30 different ways using both formCalc and javascript.
    I still can’t get this to work. I belive in RTFM but this has finally kicked my a** and need some help.
    I know what the problem is but I don’t believe it. The sourceSet node in the change event has 0 nodes even though I have two data connections and I can access them in the initialize event. Did they remove access to the sourceSet in the change event completly in Acrobat 9 and just didn’t tell anyone?

    My database is SQL server with two tables.
    1. A customer table with Name and CustomerID
    2. An Address Table with CustomerId and address information

    I set up the data drop down to display the Name and then in the change callback I tried to get the CustomerId
    and query for the data in the second table. I’ve bound the address block data to fields in the second table and set the second table to delayed open.
    I’ve tried this with every possible way of coding an accessor. When coded directly (using the intellsence thing) I get unknown accessor. When using the index method and searching for it with a while, I get index out of range with an index value of 0.

    Here is a formCalc version of the change callback:
    ========================
    $host.messageBox(“In Change”)
    var sName = xfa.event.newText
    var sCustomerId = $.boundItem(sName)
    var oDataConn = Ref($sourceSet.DataConnectionCRM.clone(1))
    $host.messageBox(sCustomerId )
    oDataConn.#command.query.commandType=”text”
    oDataConn.#command.query.select = Concat(“SELECT [Name], Customer.[CustomerId], [AddressLine1], [AddressLine2], City, State, Zip FROM [DSD_JDEPROD].[dbo].[Customer] join [DSD_JDEPROD].[dbo].[Address] on PrimaryAddressId = Address.AddressId where Customer.Name = ‘”, sCustomerId ,”‘;”)
    oDataConn.open()
    ========================

    This version of the code has the unknown accessor error at this line:
    var oDataConn = Ref($sourceSet.DataConnectionCRM.clone(1))

    I’ve coded this line many different ways using xfa notation, $sourceSet notation, resolveNode notation, but in the end it doesn’t matter
    because this message box:
    $host.messageBox(Concat(“sourceSet=”,xfa.sourceSet.nodes.items.length))

    returns a 0 count in the change event.
    I’ve check the XML and I have sourceSet in there and as I’ve said, the intellisense thing works and
    the initialize callback works. The dropdown is correctly populated.

    Note: I do realize I’m going to have to do something different in order to get the customerId as this:
    var sCustomerId = $.boundItem(sName)
    doesn’t work due to the way the initialize event populates the drop down. I’m sure I can fix that.
    I just can’t get past the problem of no sourceSet nodes in the change event.

  98. Stefan Cameron on March 10th, 2010

    Ed,

    I’m stumped! I don’t see anything readily apparent in your script that looks like it might be causing that issue.

    Things I’ve learned over the past that might help in situations like these are:

    • sometimes it’s better to use JavaScript than FormCalc
    • a query that results in columns named ‘name’ or ‘id’ can have adverse affects on the form since ‘name’ and ‘id’ are attributes on almost every XFA object there is (the XFA processor gets “confused”)

    Perhaps the “delayed open” has something to do with this? Do you need to set that?

    Are you certain of the version of XFA you’re using?