Pre-populating dropdown lists in dynamic PDF forms from a back-end data source using LiveCycle ES2.5

A working knowledge of LiveCycle Designer ES2 and the database of your choice (this tutorial revolves around MySQL).

MySQL Database
A Text Editor like Notepad ++

Intermediate

One of the main benefits of electronic forms is their dynamic nature. A form need not be created with one set of data and then left that way indefinitely. An electronic form once wired to a back-end data source can be dynamically updated at any time. A common use of this concept is having a dropdown list in a dynamic PDF pre-populate with data from a database.

To illustrate the process we will create a simple table in a MySql database. We will then add three rows into the database table, and we will create a simple PDF form in Designer that will include a drop-down list that is automatically pre-populated by this data source.

This tutorial describes using a MySql database as the back-end data source, though the general principles in binding this data source to the form are the same despite which back-end LiveCycle-compliant database you choose to use. This tutorial also assumes that you are familiar with how to create and edit tables in MySql and will not cover those topics in depth, as the main focus of this tutorial is how to bind these data sources to the dropdown list form object.

The first step is to create a table in your database. This table stores the values that will eventually appear in the dropdown list object:

The next step is to create an XSD Schema document. There are two ways to do this.  In both options, the first step is to create a “schema” folder in your LiveCycle project hierarchy in Workbench. Once the folder has been created, complete one of the following actions to create a schema:

• Option One:  Right-click on the newly created “schema” folder and opt to create a new XML schema file.  Workbench will open the default text editor associated with Workbench and the new file (a text file) will have the appropriate doctype declaration and base schema tag structure in place for an XML schema. You can then use the editor to write the rest of the schema code and then save the schema document (being sure to use the “.xsd” file extension).
• Option Two: Create the schema document using a third-party editor like Notepad ++. The benefit of using an editor like Notepad++ is once you put in your XML doctype declaration at the top of the text document in the editor, the editor uses color to make coding easier. Once the file has been created, name the schema accordingly and be sure to save the schema with the “.xsd” file extension. At this point, you will need to drag the XSD file (from wherever you saved it after editing) and drag and drop the document into your schema folder.

In either event, you will need to create a simple schema document as displayed in the schema below. 

Note, though the firstName and lastName items are in the pictured schema, these are not required for the drop-down list population. The important part of the schema code for this tutorial is the testerList element. 

Note also that this item is a complexType that has a sequence of “Tester” sub-elements, this “Tester” type in turn, is also a complexType that contains a series of values that include the “id” and the “name” for the objects that will eventually appear in the dropdown list.

For the sake of brevity, this tutorial assumes that the reader is able to create a new form using Designer from Workbench or directly in Designer. At this point in the process, create a new form (or open the existing form to which you want to bind the backend data) and open the form for editing in Designer. 

Once the desired form is opened, click on the “Data View” tab, by default this tab is located on the left-hand side of the Designer IDE, two tabs over from “Hierarchy”.

Right click on the whitespace of this “Data View” tab, and click New Data Connection. 

In the window that pops up (see Figure 1), name your new connection something appropriate (though the naming of this object is arbitrary to the task at hand), and select the  “XML Schema” radio button and press the “Next” button.

Then simply point to the schema document location. If you are following the steps directly in this tutorial, this location will be the “schema” folder in your Workbench hierarchy (see Figure 2).

Alternatively, if you decided to create the new form using Designer from Workbench, you are given the option to specify the form data model, which allows you to simply point to this schema (see Figure 3).

Accept the defaults for the remaining views in the Wizard.

Create a drop-down list by dragging/dropping the drop-down list object onto the body of the form from the Object Library Pallete (see Figure 4).

Once the drop-down object has been dragged onto the form, click on the “Tools” menu located at the top of the Designer IDE. Select “Options” under this “Tools” menu. In the “Options” popup that appears, navigate to the “Data Binding” selection in the menu to the left. Verify that the “Show Dynamic Properties” option is checked, and then click the “OK” button (see Figure 5).

Navigate back to the drop-down list in the body of the form and click on it. In “Object” Pallette for the drop-down list (by default in the lower right-hand side of the Designer IDE, two tabs over from “Layout”)  and navigate to the “Field” subtab (See Figure 6).  

In the middle of this tab, click on the green “ListItems:” link object.

On the “Items” line, click on the “navigation” object to the right of the “Items” input field.  Navigate to the “Tester” level of hierarchy. The data input field should populate with the following value:  $record.testerList.Tester[*]

Notice the “[*]” convention that is used here, this convention is used whenever a schema element is going to be iterated over multiple times. Remember that in the schema, we had a segment that looked like this (see Figure 7):

When we bound this schema to the drop-down list object, we bound it at the “Tester” level of schema hierarchy. Notice also how this element has a maxOccurs attribute value set to unbounded. This attribute value must be set in the schema in order for the “[*]” convention to work in LiveCycle.

Lastly, note how there are only two element values—one for “name” and one for “id” in the schema. These elements are bound to the “testUsers” section of the database which looked like this: testUsers values (100, 'Jim Jones', 0). Remember that there were three testUsers in the database (Jim, Sally and Fred), yet here we only have one “Tester” element that contains the “id” and “name” elements. These elements correspond to the “100” (id) and “Jim Jones” (name) values in the database (we will show how the schema is connected to the database in the following section). Just understand that the “[*]” convention, when bound to the maxOccurs=”unbounded” element in the schema, will iterate through the database table and “for each” item that it iterates over in the table, it will grab that value, conform it to the data standards that the XSD is defining, and then using the schema binding to the form, populate the drop-down list field accordingly.

There are still a few more steps to complete the binding procedure. The next step is to click on the “Binding” tab under the “Object” Pallete.  Next to the “Data binding” input field, again click on the “navigation” icon to the right. Hover your mouse over the “Use ‘Data Connection’” item (see Figure 8). A side popout appears populated with the values from the elements in the schema. 

When the user chooses an item in the dropdown list, this binding will store the chosen ID into the “selectedTester” element, so click on this name in the list.

In the “Binding Properties” popup that appears (see Figure 9), choose which properties you would like to update on the drop-down list item.  For most circumstances, one can opt to not update any properties, or as we have opted here, to update the “Name” field of the LiveCycle form object (simply done for the sake of consistency between the form object names and the schema element names). 

For the most part we let LiveCycle handle the Caption and Type information; if you were to leave these boxes checked, it would overwrite the data in the LiveCycle form. For instance, if you map a “Date” field of “date” type to a schema element that has the “Date” element cast to a “xs:string” and leave the “Type” option checked in this dialog box, it would overwrite the type information on the “Date” object to be string. We often leave schema elements as “xs:string” values in order to allow the schema to be a bit more forgiving in the data typing, and leave the structuring of the data to the built-in data typing and validation tools within Designer.

At this point, save the form and exit Designer.

Now we’ll create a main process in Workbench, then a pre-render process where we’ll execute our database lookup. This is how the database will interface with the drop-down list form object via the schema.

In Workbench, right-click on the desired location in your hierarchy and create a new process (i.e.: in a “processes” folder) using the “New Process” wizard (see Figure 10). Follow the wizard to create a new process, telling it to create a start point in the Workspace.

Accept the defaults for the remaining steps in the wizard.

In the process designer, select the green “Start Point,” then in the Properties pane, click on the button to the right of the Action Profile as seen here (see Figure 11):

In the dialog box that pops up, click the button next to “Prepare data process” to launch the “New Prepare Data Process” dialog box (see Figure 12).

Accept the default values, and click the “OK” button twice to get out of the dialog boxes and back into Workbench proper.

In the “NewForm1PrepareData” process, add a JDBC activity (Java Database Connector) of type “Query for Multiple Rows as XML”. The data source is automatically set to your primary data source (see Figure 13).

Click the “Sql Statement” button and fill out the dialog box (see Figure 14):

To see if you have successfully established the connection, press the “Test” button and you should have the “id” and “name” results returned (as pictured above); this indicates a good query.

Once again navigate back to the Process Creation view, click on the “JDBC” object and click on the “XML information” in the pane to the right in order to get the dialog box pictured below, which should be filled out as such (see Figure 15):

Once the top section of this dialog box has been completed, press the “Test” button to verify a good connection  The desired result is the XML illustrated in the bottom white box. As you can see, we’re mapping database “columns” to XML “elements,” and in our case we’re leaving the names the same though they need not be.

Now that the data connection has been established and bound to the schema, we need to create a variable in order to more easily manipuate the returned data.  To do this, press the Add Variable (“+” ) button next to “Output” and create a new variable of “Type: XML” and name it “Testers” (see Figure 16). This variable will now store the XML values resulting from the database query.

Next, create an “Assign Value” activity, which we’ll use to move the XML output of our query into the “Testers” XML variable that will in turn be used to populate the form.  Connect this “Assign Value” activity to the JDBC object as illustrated in the picture below (see Figure 17).

Click on the “Activity” object and in its property pane, click the “Pencil” edit icon in the “Mappings” section, to bring up the edit dialog box. Here we assign the “Testers” variable into the appropriate place in our output variable, as below (see Figure 18):

The destination Location is:

/process_data/xmlPrepareData/xdp/datasets/data/FormSchema/testerList

Now save the process.

Now, you are ready to deploy the application. Running it in Workspace, you’ll see the populated drop-down list in the form, like this (see Figure 19):

That’s it, you have successfully populated the drop-down list with the database values.

If you’d like to prove out that the “selectedTester” variable is being set with the user’s choice from the drop-down list, you can temporarily create a text input by going back into Designer, and dragging that field from the Data View onto the form (see Figure 20):

Save the form, deploy and run again. You’ll see that the Selected Tester field will echo the ID of the user you choose from the dropdown list (see Figure 21).

The next step might be to use the data entered or chosen by the user and insert it into a database table.

The tutorial above illustrated the principle behind creating a database table, binding it to a schema, binding the schema to a form and the creation of XML variables to store the returned data.

For further information on similar topics, check out Binding an XML Schema Document (XSD) to repeating subform elements using Adobe LiveCycle Designer.