Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

'Scripting' Category Archive

Databases: Inserting, Updating and Deleting Records

Since all of the previous tutorials I’ve posted with regards to data connections have dealt with searching for records and displaying the results, I thought I should post a little tutorial on how to use data connections for inserting, updating and deleting records as well.

You may recall that the tutorial on connecting a form to a database did demonstrate one method of inserting, updating and deleting records from a database. The problem with using bindings to fields in order to modify records in a data connection is that you must set those fields to the values for the current record and then you have to use the data connection object scripting methods like "addNew()", "update()" or "delete()". This can get really awkward if all you’re wanting to do is insert a new record, for instance, and just doesn’t cut it if you want to avoid having to load-in data when the form is opened (the only purpose for the data connection may be such that new records can be inserted or existing ones can be deleted yet displaying existing records is not required).

This small tutorial uses a basic ODBC data connection defined in the form and then modifies it, via script, in order to be able to execute SQL statements which either insert, update or delete records from any table in the database for which the data connection was setup to work with. It then uses a separate data connection to the same database for query purposes only since there’s no sense complicating things with an all-purpose data connection — especially when it comes to running "select" SQL statements in order to iterate through records returned by the query.

Key Concept

The key concept with this tutorial is the fact that the <query> node inside an ODBC data connection (as we saw in the tutorial on selecting specific database records) can be used to execute all sorts of SQL statements — not just "select" queries. This means that if you set the <select> node (inside the <query> node) to be an "insert" SQL statement and open the data connection, the result will be a new record in the database as per the insert statement’s parameters (and the same goes for "update" and "delete" SQL statements). In fact, the XFA 2.4 Specification states, on page 772, that "despite the name [of the <query> node], this element can also be used to delete, insert, and update records."

BOF and EOF Actions

One very important thing to note is that the "Beginning of File" and "End of File" actions on the <query> node’s <recordSet> child node (which describes how records in the data connection are navigated) must be set to "stayBOF" and "stayEOF", respectively, otherwise you may run into serious problems. That is, the result of reaching the beginning or end of the record set when opening the data connection must be to "stay" where you (the record set navigator) are when the SQL statement is one or more of "insert", "update" and "delete".

See the script in the "Database" script object in the sample form for more details on how to specify this. Use the Hierarchy palette to locate it under the root subform.

Sample Form

I’ve designed a form that should put this all into perspective for you as well as give you a very useful script object which you can place in your Custom Library tab and re-use in other forms.

In short, these are the steps I followed to design this form:

  1. I created the "RunSQLDataConnection" ODBC data connection to my "FormBuilder" database, specifying a short SQL query to the new "movie_comments" table (although any other table would’ve been just fine).
  2. I created the second "ListComments" ODBC data connection to my "FormBuilder" database, specifying an SQL query that exposes the "username", "title" (movie title obtained from a join on the movie table) and "comment" columns.
  3. I inserted the various buttons and fields and wrote the scripts.

I’ve included lots of comments through the scripts to detail what’s going on at each stage as well as why certain things are being done so please have look at the sample (note that you don’t have to have the data connections setup in order to open the form and look at the script) and let me know if you have any questions.

Download Sample [pdf]

Download FormBuilder Database Definition [sql]

Minimum Requirements: Designer 8.0, Acrobat Pro/Std 8.0


Posted by Stefan Cameron on December 18th, 2006
Filed under Data Binding,Scripting,Tutorials

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

Instance Manager Object Reference

It seems lately a lot of my posts and a lot of the comments that you’ve posted had something to do with the Instance Manager: The object available only on repeatable (dynamic) subforms which allows you to manage that subform’s instances (add, remove, etc.).

Since it’s at the core of repeatable subforms which are necessary when designing flowable (dynamic) forms that, say, display one row per record in a table connected to a database, I thought it would be useful to give a quick overview of the Instance Manager Object’s properties and methods.

Accessing the Instance Manager

Before we get to the properties and methods, here’s a refresher on how to get at the Instance Manager for a particular subform (or table row).

Repeatable Subform Required

First, you must make the subform repeatable. To make it repeatable, it must be placed in a flowed container — that is, another subform whose Content type (found on the Object palette’s Subform tab) is set to flowed.

Side Note: The simplest case is a report-style form that simply displays records from a database where each record’s information is displayed in separate fields within a subform. So you have a subform with fields in it that are bound to data nodes in some data connection and the subform itself is bound to a repeating data section in that same data connection. For this scenario, you’ll find it much easier to place your fields on the first page and then shrink the page (which is a subform) such that it’s snug against the fields you placed on it and looks more like a row rather than a page. This is because the page subforms are, by definition, subforms parented to the root subform (named "form1" by default on new forms — check it out at the top of the Hierarchy palette) and the root subform is, by definition, flowed. By using the page subform as your repeatable subform for your data records, you’ll find it much easier to quickly get to a state where all records show-up on your form and new pages get added when previous ones are full (can’t fit any more rows).

Once the subform is placed in a flowed container (parent subform), you must then specify that it’s a repeatable subform by going to the Binding tab on the Object palette and checking the "Repeat subform for each data item" box.

After these two easy steps are complete, you’ll then automagically get an Instance Manager object on the repeatable subform you just defined.

Script Access

It’s nice to have a repeatable subform but unless you’re just using the default behaviour (which may very well be just fine in most cases), you’ll need to write scripts that use it’s Instance Manager’s properties and methods and you’ll need to know how to access the Instance Manager in your scripts.

It turns out there are two ways of doing this:

  1. instanceManager Property: As long as you have at least one existing instance of the repeatable subform, you can access its instanceManager object directly like you would any other property: RepeatableSubform.instanceManager. Note, however, that this property will only be accessible if at least one instance of the RepeatableSubform object exists (which could be a problem if you’ve specified that its minimum allowed number of instances is zero and its initial instance count is zero as well).
  2. Underscore Prefix: The other recommended way to access a repeatable subform’s Instance Manager is to use, for lack of a better term, "the underscore-prefixed repeatable subform name" object. That is, whenever a subform becomes repeatable, its Instance Manager is added to the Script Object Model as a child of the subform’s parent container (remember, that’s the flowed subform from earlier) and is given a name that is the repeatable subform’s name with a "_" prefix. Therefore, if your subform was named "RepeatableSubform", its Instance Manager would get a name of "_RepeatableSubform" and you would access it as a property of the flowed container subform like this: FlowedContainerSubform._RepeatableSubform. The nice thing about accessing it this way is that you always have access to it — even if no instances of the RepeatableSubform object currently exist.

Properties and Methods

Now that you know how to make a subform repeatable and get access to its Instance Manager object when writing scripts, here are the various properties and methods that you have access to:

Properties

  • count: Returns the number of instances that currently exist. This is very useful when writing loops that do something to each instance.
  • min: Returns the minimum allowed number of instances. When removing instances, you’ll get a scripting error if the resulting number of instances is less than the minimum and the minimum number of instances will still remain (even if you meant to remove them). This can be set using the Min property on the Binding tab of the Object palette or my accessing the subform’s "occur" element and setting its min property: RepeatableSubform.occur.min = "value".
  • max: Returns the maximum allowed number of instances. When adding instances, you’ll get a scripting error if the resulting number of instances is more than the maximum and no additional instances will be added. You can modify this in the Object palette’s Binding tab or with the following property: RepeatableSubform.occur.max = "value".
  • occur: This is a reference to the pertaining subform’s &lt;occur&gt; element which lets you modify its minimum (min), maximum (max) and initial (initial) number of instances.
  • name: This property sets the name of the Instance Manager object itself. I doubt this would ever really be useful but it’s a property nonetheless. Be forewarned, however, that it’ll affect all subsequent scripts. For example, if you were to write "FlowedContainer._RepeatableSubform.name = ‘myIM’;" and then write "FlowedContainer._RepeatableSubform.addInstance(0);", you would get a an error stating that "_RepeatableSubform" doesn’t exist. That’s because you’ve changed its name to "myIM" and therefore should write "FlowedContainer.myIM.addInstance(0);".

Methods

  • addInstance(bool merge): Adds a new instance of the repeatable subform and returns a reference to the new instance (or null if no instance was added) . Setting merge to 1 will cause any additional data to be merged with the new instance. Also, don’t forget that new instances aren’t automatically added to the form’s calculation dependencies. Also, be careful not to add more than the maximum number of allowed instances (see the max property).
  • removeInstance(int index): Removes the instance with the specified zero-based index. [A/R 7.x] You may need to call xfa.form.remerge() after removing an instance. Also, be careful not to remove more than the minimum number of allowed instance (see the min property). [A/R 8.1+] If there‚Äôs anything else you need to do (any other script statements) along with removing an instance, make sure that the instance is removed once all other statements have been executed. The new Direct Rendering engine in A/R 8.1 removes the instance, and all objects that it contains, immediately (as opposed to removing it on the next rendering pass as in previous versions) which means that any subsequent statements are not executed.
  • moveInstance(int fromIndex, int toIndex): Moves the instance at the zero-based fromIndex such that it becomes the instance at the zero-based toIndex (and other instance in between are shifted up/down as necessary).
  • insertInstance(int position [, bool merge = 0]): [New in A/R 8.0] Inserts a new instance at the zero-based position specified and returns a reference to the new instance (or null if no instance was added). You may optionally specify 0 or 1 for the merge property which, if set to 1, will merge any additional data with the new instance. The same rules and gotchas apply for this method as they do for the addInstance method described above.
  • setInstances(int count): Adds or removes instances to/from the end of the set depending on the difference between the current instance count and the specified instance count. In other words, if there are currently 4 instances and you call "setInstances(6)", two new instances will be added to the end of the set. Conversely, if 3 instances exist and you call "setInstances(2)" the last instance in the set will be removed. Note that this method abides by the currently-specified min and max restrictions.

Recommended Scripting Language

On a last note, I simply wanted to recommend using JavaScript as opposed to FormCalc when scripting the Instance Manager — especially when adding or removing instances. While FormCalc technically fully supports the Instance Manager, I personally find it’s a little flakey. JavaScript support, however, is quite consistent and stable.

Updated: June 26, 2009


Posted by Stefan Cameron on November 11th, 2006
Filed under Instance Manager,Scripting

oneOfChild Scripting Property

Sometimes it’s necessary to access the properties contained within a field object’s UI property. For example, you might want to highlight only the content area (the value portion as opposed to the caption portion) of a text field. The problem with doing that is that you need to access the UI property’s one-of property in order to get at the Border object that it contains. The "ui.{one-of property}.border" object controls the appearance of the field’s content area. (Tip: It’s also the object that’s affected when you use the Appearance property on the Field tab in the Object palette).

If you were to look at the XFA specification supported by Designer 7.1, you would find, on page 664, that the UI element contains various one-of properties (which describe a set of properties from which only one may be specified at any given time) which vary depending on the type of field you’ve created on your form. For example, if it’s a text field, it has a

field.ui.#textField

property whereas a drop down list would have a

field.ui.#choiceList

property.

Based on the various definitions of XFA fields, you would have to write the following script to change a text field‘s content area color (shown here in FormCalc):

$.ui.#textField.border.fill.color = "255,0,0"

But what about a drop down list? Because it uses a different UI one-of property ("choiceList" instead of "textField"), you would have to write the following script (shown here in JavaScript) to change its content area color:

this.ui.resolveNode("#choiceList").border.fill.color.value = "255,0,0"

This is all fine as long as you know the type of object on which the script will be executed but what if you wanted to change the content area color of all fields on your form? You might decide to define multiple arrays that contain references to text fields, numeric fields, etc. (one array for each type) but that wouldn’t be very practical because you would have to remember to keep that list up-to-date for every change you made to your form.

A much easier way to do this is simply by using the oneOfChild scripting property: It’s designed to return a reference to the one-of property that has been specified in the Object Model on the object on which it is used. In other words, if you used it on a text field‘s UI property, it would represent the "#textField" property whereas on a drop down list, it would represent the "#choiceList" property.

If we put this in practice, it means you could use the following script to change the content area color of any field object you find on your form (shown here in FormCalc):

FieldObject.ui.oneOfChild.border.fill.color = "255,0,0"

where "FieldObject" would be a variable holding a reference to a field object on your form.

Of course, the use of the oneOfChild property also works because the Border property is common to all UI one-of properties. There are some cases where some properties are specific to a certain "UI type" (like the "#choiceList.textEntry" property). In those cases, you may need to verify that you are, in fact, attempting to access those properties on the right kind of UI one-of property or else you’ll get a scripting runtime exception.


Posted by Stefan Cameron on November 9th, 2006
Filed under Scripting

Expanding to Fit the Entire Value

A while ago, I posted an article detailing a trick to make the value of a field be displayed entirely within the field’s content area. Essentially, by setting the value font size to zero, this tells Acrobat to shrink the field’s content area (value) font at will to make the entire value entered fit horizontally. This can certainly be useful but there’s one significant drawback: the value font may shrink such that it becomes too small for anyone to read depending on how much data the user enters into the field.

Fortunately, there’s an alternative method to making a value fit within a field’s content area when you don’t know how long the value will be: Making the field’s width and/or height expandable!

The advantage of this solution is that the value font’s size remains constant (the same as which you specified it to be when you designed the form). When a field is made expandable, its "w" (width) and/or "h" (height) attributes are replaced by "minW" (minimum width) and/or "minH" (minimum height) attributes, respectively. These attributes define the initial and minimum size of the entire field (that is, its caption and content areas combined). When the width is extended, however, only the field’s content area is increased in width. Its caption area remains the same width. On the other hand, causing the height of a field’s content area to be extended will also cause the caption area’s height to be extended.

Making a Field Expandable

Specifying that a field’s width and/or height is to expand to fit its content is quite simple: You just need to check the "Expand to fit" check box that pertains to the width and/or height property on the Layout palette.

Getting a Tight Fit

So far, you’ve learned how to make a field’s width and/or height expandable, essentially by specify a minimum width and/or height instead of a set width and/or height. Now what if you wanted to ensure that the field’s width and/or height was always just wide enough to contain whatever value was entered? For example, you don’t want a whole bunch of empty space if you set a minimum width of 2 inches to have a nice initial size to enter a value into the field but the user only entered a value that required 1 inch to be entirely displayed.

In that case, you could simply set the minimum width to zero when the user leaves the field, if they’ve entered a value. This is done by scripting the field’s Exit event (shown here in FormCalc):

$.minW = "0"

Look-out for Long Values!

One thing you have to look-out for is extra long values — especially if you haven’t specified a maximum length for the field. If the user enters too much data, the field might simply run off the page.

If you don’t want to set a maximum data length for the field but you don’t want it to expand beyond a certain width, you can set a maximum width and/or height. Since Designer’s UI doesn’t let you set this property directly, you can use the field’s Initialize script to set it (shown here in JavaScript):

this.maxW = "4.5in"; // max width of 4.5 inches

Note, however, that if the user enters more data than can fit within the specified maximum dimensions, the value will be cut-off and won’t print so you may consider setting a maximum data length or resorting to the previous solution (setting the font size to zero).

Sample

This sample form implements the solution I’ve described in this post.

Download Sample [pdf]

Minimum Requirements: Designer 7.x, Acrobat 7.x.


Posted by Stefan Cameron on October 31st, 2006
Filed under Scripting,Tutorials