Selecting Specific Database Records

Every now and then, someone posts a comment with a question on how to do something and the answer requires more than just a quick response. In this case, it was “Ricardo’s question”: on how to select a specific record from a data connection to a database for editing in a form.

If you read my previous post on “Connecting a Form to a Database”:, you might’ve realized that the result was a single live data connection to the entire set of records in a database. This is great if you want to iterate through all records one at a time and update them on an individual basis. You might’ve also realized that you could narrow the scope of the data connection by specifying a more specific SQL statement (with a _WHERE_ clause, for example). But what if you wanted the form to filter, on the spot, the data loaded from the data connection? For example, you might want to let the user pick from the different movie categories (action, comedy or drama) and then let them iterate through only that subset of the Movie Database.

If you’ve been scratching your elbow, pinching your nose and blinking your eyes in hopes that this might “just work”, well, it’s actually scratch your nose, pinch your elbow and roll your eyes — ok, just kidding…

The idea with this sample (based on the Movie Database) is to design a form which has a drop down list for picking a movie category and then a subform (which appears only once a category has been selected) that contains the movie data for all movies with the selected category.The key to achieving this functionality is to use two data connections. (It’s important to note here that while you may only have a single data connection which loads data from an XML Data file, you may have any number of data connections to web services (WSDL) and databases (ODBC).) Furthermore, the use of SQL statement is crucial to making this work properly.h2. xfa.sourceSet.{DataConnectionName}When you define a data connection in the Data View palette, you’re actually defining a <source name=”{DataConnectionName}”> node within the <sourceSet> packet inside the XDP file (which is then wrapped in a PDF if you save your form as a PDF file). Since this is defined in the XDP using XML, you can access its properties just like you can get at the properties of the objects you place on your form.In this sample, I’ve defined two data connections to the Movie Database:== Two Data Connections ==If you look at the XML Source which describes these connections, you can see that there’s an interesting _command_ node which contains information about the query currently being used by each data connection. That’s what we ultimately want to modify once the user picks a movie category:== Source Set XML ==h3. //query@commandTypeYou should note that the query node’s *commandType attribute* value is very important. Setting it to _text_ will let you specify the SQL statement used by the data connection. Other possible values are _table_ (to let you specify a table name for the data connection) and _storedProc_ for specifying a stored procedure.h3. Data Node NamesAnother very important thing to note is the names given to the data nodes in the *MovieCategories* data connection. You’ll notice that the following SQL statement is used for the data connection:bc. SELECT id as catId, name AS catName FROM movie_categoriesGROUP BY name ORDER BY name;In particular, the _id_ and _name_ columns have been renamed to _catId_ and _catName_, respectively. That’s because having data nodes with the names “id” and “name” in your data connection will give you a lot of headaches when attempting to iterate through the xfa.record.{DataConnectionName} node in order to find the data associated to the current record from a data connection (so that we can display the category names in the drop down list, for example). This is because the words “id” and “name” conflict with properties of the xfa.record object.h2. Building the Formh3. Category ListThe first step is to use the Data Drop Down List object from the Custom tab in the Library palette. This is a really handy object that has code in its Initialize event that’s already setup to populate its item list based on data nodes from a data connection.In the Initialize event of the object, set the data connection name to “MovieCategories”, the hidden value column name to “catId” and the display text column name to “catName”.If you run the form at this point, you should get three values in the list: “Action”, “Comedy” and “Drama”.h3. Movies in the CategoryNext, create a subform (let’s call it “movieData”) which contains fields with explicit bindings to the _title_ and _showTime_ data nodes from the MoviesInCat data connection using the Binding tab in the Object palette. Also, add the Data Connection Controls object from the “Connecting a Form to a Database”: sample to this subform (making the proper adjustments for the data connection name in each button’s Click event script) and make this subform invisible.At this point, you should have a form which displays a list of categories and contains an invisible subform.h3. Filtering Records Displayed by the movieData SubformFinally, in the Data Drop Down List’s Change event, write a script which sets the SQL statement used by the MoviesInCat data connection, opens the connection and displays the movieData subform. For this sample, I chose to use *FormCalc* to script this event.First, get the category selected by the user and determine it’s associated ID:bc. var sCategoryName = xfa.event.newTextvar sCategoryId = $.boundItem(sCategoryName)Given the XML structure of the <sourceSet> packet displayed above, you first set the query’s command type to “text”:bc. xfa.sourceSet.MoviesInCat.#command.query.commandType = “text”This ensures that the data connection will use an SQL statement. Note the pound (#) prefix to the _command_ property of the MoviesInCat node.Then, set the SQL statement, on the MoviesInCat data connection, which will filter the records from the movie table in order to show only those that belong to the selected category:bc. =concat(“SELECT title, showTime FROM movies WHERE categoryId = “,sCategoryId, ” ORDER BY title;”)Finally, open the data connection, move to the first record and show the invisible subform:bc. = “visible”Opening the data connection will cause the explicit bindings you set earlier on the fields in the movieData subform pertaining to the movie title and show time data to be used in order to load data from the xfa.record.MoviesInCat record (which will now contain the data from the first record of the MoviesInCat data connection as per the SQL statement we just built using the selected category ID).If you want to “run” this sample, you can download the form and Movie Database here:”Download Sample [zip]”:*Minimum Requirements:* Designer 7.0, “Acrobat Standard 7.0”: the FormBuilderDB20060929.sql file to build the database, create an ODBC Connection named “FormBuilderDB” and load the form.%{color:red} *Update for Designer/Acrobat 8.0 forms:* % If you’re attempting to reproduce this sample or something similar in your own forms using Designer and Acrobat 8.0, you’ll most likely run into security errors when attempting to run the form in Acrobat 8.0. This is due to “new restrictions imposed on modifying data connections at run time in XFA 2.5 forms”:*Updated:* February 6, 2007~