Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

'Data Binding' Category Archive

Connecting a Form to a Database

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.

ODBC Data Source Administrator

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.

Data View Palette 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.

Data Link Properties Dialog

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.

Table Or 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:

Data Connection in Data View Palette

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:

Fields Bound to Data Nodes

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.

Data Connection Control Buttons

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.

Form Loaded In Acrobat

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

Auto-Localizing Your Forms

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

Dynamic Properties

Did you know that as of Designer 7.1, there’s way to automatically populate certain field properties with data without having to write any script? This is what the new Dynamic Properties feature is designed to do.

First, you have to enable it because it’s disabled by default. You can do this by going to the Data Binding panel in the “Tools | Options” dialog. There, you’ll find a check box labeled, “Show Dynamic Properties”. Check the box and press OK. After doing so, put a list box (for example) on the form and take note of the changes in the Object palette’s Field, Value and Binding tabs.

You’ll notice that some property labels have now changed color (default is green) and are underlined. You can now click these property labels to make the properties they pertain to dynamic (i.e. to automatically push values into them when data is loaded into the form via a certain data connection that you specify). For instance, the Field tab now has dynamic Caption and List Items properties.

If you click on the List Items label, you’ll get the following dialog (this screen shot shows the properties already configured for this sample):

Using the Dynamic Property dialog (above), you can then specify the data connection from which the data will be loaded and also the data node(s) that will contain the data (in this case, for a list field, you can bind data nodes to the text and value items of the list).

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 import the data into it using the “File | Form Data” menu.

For this sample, we’re trying to list all the movie titles and, when a movie is picked from the list, display its show time in a date/time field on the form. Therefore, we’ve bound all movie data

$record.movie[*]

to the list and then we’ve bound the text items to the

title

data node (which translates to “$record.movie[*].title” since it’s relative to the property’s binding) and the value items to the

showTime

data node (which translates to “$record.movie[*].showTime”).

Based on these settings for the list’s text and value items, the list will be automatically populated with all movie titles and show times when the movie data is imported into the form. All this without having to write a single line of script!

By the way, if you’re curious about the script you would have to write in order to do the equivalent without using the Dynamic Properties feature, just drop a “Data List Box” or “Data Drop Down List” object, from the Library palette’s Custom tab, onto the form and have a look at the script in its Initialize event. Now that’s not simple script! Of course, there are advantages to using script over Dynamic Properties because script is more flexible but in this case, there’s really no need to worry about using script.

Once you’ve filled-in the properties inside the Dynamic Properties dialog for the list box’s List Items property, you’ll notice that the property’s label now has a little “link” icon to signify that the property is bound to nodes in a data connection:

Finally, you just need a single line of script in the list box’s Change event to display a selected movie’s show time in a field on the form (in FormCalc):

ShowTime = $.boundItem(xfa.event.newText)

Please note that the Dynamic Properties feature is still in its early stages therefore it only handles simple data binding situations like the one for this sample. Many customers who’ve discovered this feature attempt to use it in dynamic subforms in order to populate lists with data pertaining to the current record and soon discover that the lists always end-up containing identical record data when they use the Dynamic Properties feature. That’s because the feature simply doesn’t yet support dynamically updating the data node occurrence which is used to populate a list, for example (that is, updating the occurrence of the “$record.movie” binding to a specific occurrence as opposed to always using [*]).


Posted by Stefan Cameron on July 29th, 2006
Filed under Data Binding,Tutorials