In response to Lala and malik’s questions on connecting a form to a database (whether it’s Microsoft Access, MySQL, etc. doesn’t really matter), I decided to write a little tutorial on how to do it.

Even if you already know how to do it, I encourage you to pay special attention to the section on Auto-Incremented Table Columns because it might help you understand and resolve some of the issues you may have already run into.

Create a System DSN

First, you need to create a System DSN for your database using the ODBC Data Source Administrator Windows tool.

That will let Acrobat interface with your database when your form is opened in Acrobat in order to be read and/or filled. Because of the DSN, it doesn’t matter what kind of database you need to connect to.

For this tutorial, I’ll be using my FormBuilder database which contains the same kind of movie information found in the XML Data files from various other tutorials I’ve already posted:

Download Movie Database [sql]

This SQL file will create the database and a user if you’re using something like MySQL (for which you can also download a free ODBC driver). You should also be able to easily tweak it to create tables in a Microsoft Access database if that’s what you want to use.

Make a New Data Connection

The second thing you need to do is create a data connection in Designer: From a new or existing form, open the Data View palette (you can use the “Window | Data View” menu item to open it) and choose “New Data Connection” from the palette’s fly-out menu.

In the “New Data Connection” wizard, pick “OLEDB Database” from the first screen, using the “Build” button on the next screen to open the “Data Link Properties” dialog, go to the “Connection” tab and pick the name of the DSN you created in the first step from the first drop down list. Then click the “Test Connection” button to make sure a connection can be established to the database via the DSN.

Click on OK to close the “Data Link Properties” dialog and return to the “New Data Connection” wizard. Now that you’ve chosen a DSN, you’ll be able to specify the resource within that DSN to which the connection should be made: You may either pick a Table from the list, specify a Stored Procedure or specify an SQL Query. If you’re connecting to a single table, you may be able to simply pick its name from the list of tables. If you need to connect to multiple tables in the same data connection, then you’ll need to use a Stored Procedure or an SQL Query.

Auto-Incremented Table Columns

If the table you’re wanting to connect to contains any auto-incrementing columns, you must use the “SQL Query” option instead of simply choosing a table name from the “Table” option. If you pick a table with an auto-increment column, you’ll be able to read from it but you’ll get errors when you try to push data into it. If this is the case, write an SQL Query that selects all columns in the table except for those which are auto-incremented. In the image above, I chose “SQL Query” because the “movie” table I’m connecting to has an auto-incrementing column named “id” that needs to be excluded from the data connection.

Bind Fields to Data Connection Nodes

At this point, you should have a new data connection listed in the Data View palette which contains a list of “nodes”, one for each column in the table(s) you picked while setting-up the data connection:

The next step is to create fields to represent each node in the data connection and bind each field to its respective data node. The easiest way to do this is simply to drag & drop the nodes from the data connection onto your form. This is handy for two important reasons:

1. The Data View palette has inspected the definition of each node and pre-determined the best type of field to use in order to edit its data.
2. When you drop the nodes onto the form, the fields that are created are automatically setup to be bound to their respective data nodes.

The database I’ve connected to is one that uses the Movie Data I’ve used in previous tutorials. In this case, I’ve connected to the Movie table’s “title” and “showTime” columns. Since the “title” column is described as VARCHAR, the Data View palette figured it should be a text field. As for the “showTime” column, described as TIME, it’s set to be a date/time field with its Data Format property preset to Time.

After you’ve completed this step, the Data View palette now shows the data nodes in the data connection as “bound” with special icons:

Add Control Buttons

The last step in this process consists in adding a set of controls to manipulate the records in the database obtained via the data connection. The simplest way to do this is to use a set of buttons where each is assigned one of the following statements (each statement is one line and provided in FormCalc):

xfa.sourceSet.{DataConnectionName}.first()
xfa.sourceSet.{DataConnectionName}.previous()
xfa.sourceSet.{DataConnectionName}.next()
xfa.sourceSet.{DataConnectionName}.last()
xfa.sourceSet.{DataConnectionName}.addNew()
xfa.sourceSet.{DataConnectionName}.update()
xfa.sourceSet.{DataConnectionName}.delete()
xfa.sourceSet.{DataConnectionName}.cancel()

where {DataConnectionName} should be replaced by the name you gave to the data connection you created earlier (“DataConnection” by default).

Each statement above represents a different action to take with the data connection: Move to the first, previous, next or last record, add a new record, update or delete the current record and cancel changes to the current record, respectively.

Note that the “first”, “previous”, “next” and “last” statements imply an “update” by default which means that if you simply use

xfa.sourceSet.{DataConnectionName}.next()

to move to the next record and the user has made changes to the current record, those changes will be committed prior to moving to the next record. If you want those navigation controls not to commit changes (and therefore require the user to explicitly click on the “update” button in order to apply any changes to the current record), you must specify the cancel statement prior to the next statement:

xfa.souceSet.{DataConnectionName}.cancel()
xfa.souceSet.{DataConnectionName}.next()

To help you do this quicker now and in the future, here’s a Library Object Snippet that you can place into the Custom tab of the Library palette (you’ll have to save the XFO file to the following folder on your system: C:\Documents and Settings\{userid}\Application Data\Adobe\Designer\en\objects\custom where userid is your windows user id).

Download Data Connection Controls Snippet [xfo]

Minimum Requirements: Designer 7.0, Acrobat 7.0.

Once you place the file in the folder indicated above, you’ll then have a new object in the Custom tab of your Library palette named “DataConnectionControls”. Simply drag the object onto your form and the buttons will appear, all pre-configured and ready to go.

Run Your Form

Now that the DSN, data connection, fields, bindings and navigation controls have been setup, you should be able to preview your form in Acrobat Pro and see the first record in the database table(s) pre-loaded into the bound fields.

If you’re having problems getting this going, you can check-out my form (assuming you’ve created the FormBuilder database and a DSN for it) to see if you missed any steps:

Download Sample [pdf]

Minimum Requirements: Designer 7.0, Acrobat Standard 7.0.

_{Updated: October 17, 2006}

---

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

---

One of the hottest topics on the Designer Forums these days seems to be methods by which one can add a table of contents to their form. Since there are many different ways to achieve this, I thought I would post a little sample to demonstrate how this can be done.

Of primary concern when adding a table of contents to your form is ensuring that the links provided to the form’s various pages/sections remain valid at all times. Static forms whose page set never changes don’t really need to worry about this but dynamic forms do. That is, regardless of whether content pages are added, re-ordered or even deleted from your dynamic form, you need to ensure that when a TOC link is clicked, the user is taken to the correct page pertaining to the topic they selected.

The best way of ensuring that TOC links don’t get broken as a result of changes to the form’s pages is by using layout information provided by the XFA Layout Model. This model provides information such as an object’s actual dimensions (width/height), the page on which it is located as well as a few other interesting pieces of information.

By using the

xfa.layout.absPage

we’re able to get the page number on which an object is currently located, taking into account the various pages which may have been inserted or removed in-between the TOC page and the page in question. Taking that page number and assigning it to the

xfa.host.currentPage

property then sets the current page to the one on which is located the object in quetsion.

Download Sample [pdf]

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

page or absPage?

If you’re looking at the Adobe XML Form Object Model Reference (page 188), you’ll notice that there are actually two methods which provide the number of the page on which an object is currently located:

xfa.layout.page // returns a 1-based number

and

xfa.layout.absPage // returns a 0-based number

Since we need to set that information to the

xfa.host.currentPage

property and that it expects a zero-based page number, it’s simpler to use the absPage version.

The Layout:Ready Event

When using the XFA Layout Model, it’s very important to realize that the information it contains is constantly updated whenever something on your form changes that may affect an object’s positional data. Therefore, you must ensure that the form’s layout has been updated before you attempt to obtain information from it about a particular object.

For TOC page fields that display the page number on which a topic is currently located, it’s easier to simply put script in the Layout:Ready event of that field since this event is fired every time the form’s layout process is complete. Therefore, whenever pages get added, re-ordered or even removed, this event is fired and the fields are updated with the most current information.

Alternatively, if you’re writing script in another event and you need to know whether information in the XFA Layout Model is up-to-date, you can use the

xfa.layout.ready

boolean property which returns true if the form’s layout process is complete and false otherwise.

Links in XFA

A lot of customers have asked me how to create links in XFA. The concept of links is essential to a TOC page since it’s sole purpose is to provide links to various pages within a document.

Unfortunately, XFA 2.4 (used by Designer 7.1+ and Acrobat 7.0.5+) doesn’t have the ability to describe the equivalent of an HTML anchor (link) or a PDF Bookmark. Therefore, we’re forced to cheat a little. One way of doing that is by using a transparent button overlaid on top of another object (text or field) which displays information about a link. You could also use a button directly by using its caption to describe what it links to but you’ll most likely run into text alignment problems if you need the button’s caption to be anything but centered.

My sample uses the former solution and places transparent buttons over the fields which display information about the link (title and current page number). You can make a button transparent by removing its caption text and customizing its Appearance property (on the Object palette’s Field tab) to remove its borders and fill.

Finally, the z-order (or top-down order in the Hierarchy palette) of the transparent buttons used as links with respect to the fields over which they’re to be located is very important in that the buttons must be above (higher in z-order or lower in the Hierarchy palette) the fields to which they pertain. One way to do this is by selecting a button and choosing the “Layout | Bring to Front” menu item. This will ensure that when you click on the topic, you’ll click on the link button rather than the field which describes the topic.

---

Posted by Stefan Cameron on September 4th, 2006
Filed under Scripting,Tutorials

---

A colleague of mine here at Adobe pointed-out today that the use of the AcroForm Document object’s getField method wasn’t necessary in the script I used for my original Invalid Flashing Fields sample.

There’s an alternative which uses xfa.form.resolveNode in the app.setInterval script. xfa.form.resolveNode takes a SOM Expression and returns a reference to an XFA node. What’s more is that this API call can be made from within the context of the AcroForm Scripting Object Model.

The app.setInterval script therefore changes from this:

moFlashTimerID = app.setInterval(
  "var f =  this.getField('" +
    GetAcroFormFieldName(oField) + "');  " +
  "if (color.equal(f.fillColor, color.red))" +
    "{ f.fillColor = [" + moAcroFieldFillColor.toString() + "]; }" +
  "else" +
    "{ f.fillColor = color.red; }",
500);

to this:

moFlashTimerID = app.setInterval(
  "var f =  xfa.form.resolveNode('" +
    oField.somExpression + "');  " +
  "if (f.ui.oneOfChild.border.fill.color.value == '255,0,0')" +
    "{ f.ui.oneOfChild.border.fill.color.value = '232,232,232'; }" +
  "else" +
    "{ f.ui.oneOfChild.border.fill.color.value = '255,0,0'; }",
500);

Also note the changes in the way the color values are compared and assigned (whereby the newer version uses more familiar XFA script rather than the AcroForm script from the first version).

Since the use of the AcroForm Scripting Object Model should always be secondary to using the XFA Scripting Object Model (because AcroForm objects are, after all, in a separate Object Model which may change separately from the XFA Scripting Object Model), I wanted to highlight this alternative which makes more extensive use of the XFA Scripting Object Model than the first version did.

Download Sample [pdf]

Minimum Requirements: Designer 7.1, Acrobat 7.0.5.

---

Posted by Stefan Cameron on August 15th, 2006
Filed under AcroForm Objects,Scripting,Tutorials

---

I just recently received another comment from Zack. This time, he was wondering about how one would go about tracking mouse clicks on an image field.

I had never attempted to do that so I took it on as a challenge and thought I would share the results in this post.

I knew from the start that XFA alone wasn’t going to be able to handle this simply because (to my knowledge) it doesn’t provide any information as to the position of the mouse pointer when an event occurs. The most logical place I thought would’ve provided the information — the Event Pseudo Model (the xfa.event object available in all XFA events) — didn’t live up to my expectations. Thankfully, XFA at least provides a Click event so that I could know when the image got clicked.

The next logical place to look was in Acrobat’s Scripting Object Model (in the AcroForm Objects). In the Acrobat Document object, I found what I was looking for: the mouseX and mouseY properties which provided the location of the mouse with respect to the document window.

The last thing I needed was information about the dimensions and location (within the Acrobat Document Object’s coordinate space) of the image field and the Acrobat Field object’s rect property would give me just that.

The combination of the XFA Click event, the Acrobat Document object’s mouseX and mouseY properties and the Field object’s rect property was just what I needed to get this to work.

Of course, I soon discovered that I had another problem to figure-out: The behaviour of an image field in a PDF form running in Acrobat is that when clicked, it opens a browse dialog that lets you pick the content for the field. Unfortunately, there isn’t any way to suppress that dialog other than making the image field read-only or by using a static image object but then both alternatives prevent the Click event from firing. So I needed some clever way to capture a mouse click over an image (whether it was a field or a static object) and I decided to use a button with a transparent fill and no border (so it was essentially transparent). Since buttons are fields just like image fields, the mouseX, mouseY and rect properties would still be available for the button and if I sized the button to fit the image and placed it over-top, I would essentially end-up with an HTML <map>.

Download Sample [pdf]

Minimum Requirements: Designer 7.1, Acrobat 7.0.5.

The first challenge was getting an instance of the Acrobat Field object which represents the button using the

event.target.getField

method. That was easily accomplished by using the script I provided on my AcroForm Field Name Generator article.

The next problem to be solved was the fact that the default behaviour, in Acrobat, for a button when it’s clicked is to invert its content area. Since I was trying to hide the button, I needed a way to suppress the inversion so that clicking on the button would give no visual feedback to the user. That way, it would give the impression that the user was actually clicking on some sort of hyperlink on the image itself. That was easily solved by using the highlight property of the Acrobat Field object representing the XFA button in its Enter event (had to be Enter and not Initialize because Initialize is too early in the form’s initialization process for the association between the XFA button and it’s Acrobat Field counterpart to be established):

event.target.getField(ScriptObject.GetFQSOMExp(this)).highlight =
  highlight.n;

The last problem was with respect to calculating the coordinates of the hot spots on the button that would trigger a reaction (in this sample, I was just going to set the value of a text field somewhere else on the form to reflect the area that was clicked). The problem there was that while I had the mouse location and the button’s dimensions all in the same coordinate space (Acrobat Document), the coordinates were specified with (0,0) set to the document’s lower left corner.

While this may not seem like a big deal to some of you, it really messes me up when (0,0) isn’t at the top left corner (with the maximum (x,y) set to the bottom right corner). I guess that’s a result of years of writing code for Windows where an MFC CWnd’s coordinate space places (0,0) at the top left corner. Anyway, after lots of hair pulling, I finally figured-out how to properly calculate the hot spots in this strange — no, alien — coordinate system.

Zack, if you have any other questions, please post a comment.

---

Posted by Stefan Cameron on August 4th, 2006
Filed under AcroForm Objects,Scripting,Tutorials

---

A few days ago, I posted about Designer 7.1’s new Dynamic Properties feature. In that post, I explained how this feature could be used to automatically populate a list box or drop down list field with data from a data connection without having to write any script.

Today, I thought I would highlight one of the main advantages to using this feature: localization of your forms!

By using the Dynamic Properties feature to bind the caption of form fields to data nodes in a data connection, you can easily localize your forms without having to write any script!

To illustrate how this would work, I’ve designed a simple little form which has an address block on it (taken from the “Address Block” custom object that ships with Designer, found under the Custom tab in the Library palette). Each field in the address block (which excludes the “Locale” field at the top that’s just there for informational purposes) has its Caption property bound to a specific data node in the data connection I’ve defined, based on some different localized XML Data files. To localize the form at run-time (e.g. in Acrobat), just open the form and then load the XML Data file pertaining to the locale you want to use.

Download Sample [zip]

Minimum Requirements: Designer 7.1, Acrobat Pro 7.0.5.

Note: If you open the form in Acrobat, don’t forget to load a data file into it by using the options under the “File | Form Data” menu.

I should also mention that since it’s possible to have multiple data connections pointing to databases and/or WSDL connections, you could use one connection to localize your form and another to capture the data entered into your form (by binding the field values as usual, using the Binding tab in the Object palette, to data nodes in the second data connection). If you have any questions about this, I’ll be glad to answer them!

---

Posted by Stefan Cameron on August 2nd, 2006
Filed under Data Binding,Tutorials

---