Here’s a sample in response to Ernest’s question on passing values to PDF forms via URL. His intent is to use it to provide a key to a PDF form such that it can be used to filter records from an ODBC Data Connection in order to pre-populate the form with data.

I love these kinds of questions because they challenge me to find answers!

Of course, this is quite specific but there are many other uses for this. In fact, you could have a whole lot of fun with it too! You could even use this to alter the appearance of your form: Say you had one form that you were using for multiple departments in your company and every department had its own header. You could place each header in a subform, make them all hidden and then, based on the URL request which would include a department code, decide which one to show — no XML Data file, database connection or web service needed!

Beware, however, that URL request strings are not secure because they get posted in plain text for anyone to read so be careful of the information you pass-in to your form this way.

This sample form looks for a “message” and a “color” key in the URL request in order to show a message in a text field and change the text field content area’s color (an RGB value). For example:

• //URLRequests.pdf?message=Isn’t%20this%20cool%3F — Shows the message “Isn’t this cool?” in the text field.
• //URLRequests.pdf?message=Think%20of%20the%20possibilities…&color=0%2C255%2C0 — Shows the message “Think of the possibilities…” in the text field and makes the text field green.

Download Sample [pdf]

Minimum Requirements: Designer 7.0, Acrobat 7.0.

The trick to all this is obtaining the URL that was used to access the PDF form. This can easily be achieved by accessing the Acrobat Document Object associated with the form and then obtaining the value of its URL property. This is done in JavaScript via the “Message” text field’s Initialize event:

event.target.URL

This property will give you the following string (from the second sample URL I provided earlier):

//URLRequests.pdf?message=Think%20of%20the%20possibilities...&color=0%2C255%2C0 

From there, it’s just simple JavaScript code to extract the request

?message=Think%20of%20the%20possibilities...&color=0%2C255%2C0

and then parse the key/value pairs

message=Think%20of%20the%20possibilities...
color=0%2C255%2C0

Finally, since we’re passing-in a string that may contain spaces which will have been URI-encoded (that’s use of “%20” instead of spaces and “%2C” instead of commas in the request string), we need to decode it back to a normal string using JavaScript’s built-in “decodeURI” and “decodeURIComponent” functions:

decodeURI(decodeURIComponent(string))

If you want to have fun submitting different strings to show in the message box, I found a little tool online which converts regular strings into URI-encoded strings.

---

Posted by Stefan Cameron on October 20th, 2006
Filed under AcroForm Objects,Scripting,Tutorials

---

So far, I’ve covered two ways of connecting forms to ODBC data sources:

1. Connecting a Form to a Database — explains how to design a form which lets the user iterate through each record one at a time; and
2. Selecting Specific Database Records — takes the first tutorial one step further by providing a means to specify which record(s) to view.

This time, in response to Y. Gautham’s recent comments, I’ve decided to post a little tutorial on displaying all records from an ODBC data connection for reporting purposes (as opposed to editing).

XML Schema vs ODBC Data Connections — What’s the difference?

Those of you who have been using XML Schema data connections might think this is just as easy to do but it actually involves some scripting and some knowledge of the Instance Manager. This is due to the differences in the way data is loaded between the two data connection types: XML data loaded via an XML Schema data connection is loaded completely. This allows you to use bindings like “$record.XMLSchemaDataConnection[ * ]” to bind all instances of a repeating data node to a subform and therefore cause a new instance of that subform to be generated for each instance of the repeating data. On the contrary, ODBC data connections are normally used to iterate through records and therefore only one record is loaded at any given time. Because of that, you can’t binding a repeating subform to a binding like “$record.ODBCDataConnection[ * ]” in order to get one instance per record because only one record will ever be loaded at any given time. (That’s not to say that the binding doesn’t work — it does, in fact, but you never get more than one record and therefore you never get more than one instance of the subform).

Where to start?

At this point, I’m assuming you’ve already created an ODBC data connection using the Data View palette — similar to what you may have done when following my previous ODBC data connection tutorials.

Since my goal is to display all records in an ODBC data connection, it would be nice to “hit the ground running” with some pre-canned script. The Data List Box (found in the Library palette’s Custom tab) object’s Initialize script will give me a good start because it’s already designed to iterate through records in a data connection and populate a list box with record data.

Modifying the Data List Box script

In order to list all data from all records from an ODBC data connection, I’ll need to use a container into which I can add items. One way to do this is to use a repeating subform inside a flowed container. This will let me use the repeating subform’s Instance Manager in order to add a subform for each record I find.

For my form (based on the Movie Database from my previous database tutorials), I decided to use the page subform as my record data container (and named it “MovieSF”) since it’s already in a flowed subform (that is, the root subform, named “form1” by default). In the MovieSF subform, I then placed title, showTime, category and actor fields and specified “None” as the binding for each field and the MovieSF subform. Then, I specified that it should repeat for each data item using the Binding tab in the Object palette and specified no min, max or initial count. Finally, I placed the Initialize script from the Data List Box into form1’s Initialize event.

A note on bindings

Notice that I specified a binding of “None” (which means I explicitly specified that objects should not be bound to data nodes in any data connection) for the MovieSF, title, showTime, category and actor objects using the Binding tab in the Object palette. I did this to avoid binding problems later where you might end-up with an instance of the MovieSF subform for each record but no data in the fields and this is related to implicit bindings attempting to execute on each instance (or explicit binding if you explicitly specified which data node an object should be bound to).

Making the script look for the right data

The first step in modifying form1’s Initialize script (based on the Data List Box’s Initialize script) is to make sure it’s looking for the right data nodes. This is a little difficult to explain without using a diff tool so you’ll have to check-out my sample form (linked later in this post) to see it in detail. I will, however, highlight the main point of modification for this step which is labeled “Find the data nodes” in the script:

var oTitleDataNode = null;
var oShowTimeDataNode = null;
var oCategoryDataNode = null;
var oActorDataNode = null;

for (var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)
{
  if (oRecord.nodes.item(nColIndex).name  "title")
  {
    oTitleDataNode = oRecord.nodes.item(nColIndex);
  }

  if (oRecord.nodes.item(nColIndex).name  "showTime")
  {
    oShowTimeDataNode = oRecord.nodes.item(nColIndex);
  }

  if (oRecord.nodes.item(nColIndex).name == "category")
  {
    oCategoryDataNode = oRecord.nodes.item(nColIndex);
  }

  if (oRecord.nodes.item(nColIndex).name == "actor")
  {
    oActorDataNode = oRecord.nodes.item(nColIndex);
  }
}

Notice how I’ve added a couple of objects and changed the contents of the For loop to look for each type of data node I need (and which is mapped using the MovieDataConn data connection).

Generating MovieSF instances for each record

This is the brains of the script which generates a new MovieSF subform instance for each record found in the MovieDataConn data connection. This is done using the “_MovieSF” Instance Manager:

while(!oDB.isEOF())
{
  // Create a new instance and get a reference to it.
  var oNewMovie = _MovieSF.addInstance(0);

  // Populate the fields inside the new instance.
  oNewMovie.title.rawValue = oTitleDataNode.value;
  oNewMovie.showTime.rawValue = oShowTimeDataNode.value;
  oNewMovie.category.rawValue = oCategoryDataNode.value;
  oNewMovie.actor.rawValue = oActorDataNode.value;

  // Move to the next record in the data connection,
  //  thereby causing all data node references to be
  //  updated accordingly.
  oDB.next();
}

You should take note of how I save a reference to the new MovieSF subform instance into a local variable which I can then use to populate its fields using the appropriate data nodes. Also, if you’re wondering why I’m specifying zero as the parameter for the addInstance function, here’s why (in short, because I don’t want to merge the new instance of the MovieSF subform with data).

The goods

If you’re curious to see how my sample form works, here it is:

Download Sample [zip]

Minimum Requirement: Designer 7.0, Acrobat Standard 7.0.

Don’t forget to create a System DSN named “FormBuilderDB” using the included SQL file.

_{Updated: October 17, 2006}

---

Posted by Stefan Cameron on October 12th, 2006
Filed under Data Binding,Instance Manager,Scripting,Tutorials

---

A couple of weeks ago, I started a series of posts on FormCalc Expressions. The first ones I convered were the If and For expressions. This time, I thought I would explain the Foreach expression.

Foreach Expression

The Foreach expression is quite similar to the For expression but is meant to be used to achieve a slightly different kind of iteration that may be better suited to your needs rather than just increasing/decreasing the value of a counter (as for a regular For expression). The difference is that the Foreach expression assigns a different value taken from an argument list (list of objects/values) to a variable for each iteration of the loop (and there’s one iteration for each argument in the list).

FormCalc Syntax

foreach Variable in (ArgumentList) do
  ExpressionList
endfor

In this syntax, there’s an ArgumentList. This is simply a comma-separated list of objects/values to assign to the Variable on each iteration.

Based on the syntax, you can do things like set a total field’s value to the sum of subTotal, shipping and taxes fields:

foreach val (subTotal, shipping, taxes) do
  total = total + val
endfor

In this example, each time the expression loops, the variable val assumes the “identity” of a different object in the argument list: First, the subTotal field, then the shipping field and, finally, the taxes field.

JavaScript Syntax

For comparison, here is the equivalent JavaScript examples (note the semicolons and curly braces):

var arr = {0: subTotal, 1: shipping, 2: taxes};

for (key in arr)
{
  total.rawValue += arr[key].rawValue;
}

(In this case, the curly braces are optional but I think this is better style.) It’s certainly more cumbersome in JavaScript using the For…In expression (which is the closest equivalent to FormCalc’s Foreach expression I could find) because it’s only meant for iterating through an object’s properties or an array’s keys (which is what my example is doing).

---

Posted by Stefan Cameron on October 7th, 2006
Filed under FormCalc,Scripting

---

Description

If you’ve ever tried to use an image field’s contents (an image) as the background for a form and wanted to let the user change it at run-time, you’ve probably run into problems if you had placed objects on top of this image field. The most common result is the image field’s contents always appears on top of all other fields which are technically higher in z-order (“in front” in the hierarchy) than the image field.

While this may seem like buggy behaviour, it’s not technically a bug and I’ll try to explain why it isn’t.

The behavior occurs because of the way image fields are translated from XFA objects to AcroForm objects when your form is saved as a PDF and rendered in Acrobat.

The PDF format has a definition for what is called an Annotation object which represents essentially all the “extra” stuff you can add to PDF documents such as comments, stamps, highlighting, etc. When it comes to image fields, their contents is actually an Annotation in the PDF layer as opposed to contents in the XFA layer. Since the PDF layer is above the XFA layer, Annotations can’t be placed behind objects on the XFA layer — hence why the contents of an image field is always displayed above any objects that may be “in front” of it in the XFA hierarchy (z-order).

Workaround

Unfortunately, there aren’t any workarounds to this problem if you want users to pick the image field’s contents at run-time (in Acrobat) or if you specifically need to use an image field object.

If you are able to restrict the possibilities to a known set of images that you can store in the form at design-time (in Designer), however, then you could embed all the images in separate read-only image objects (note that this is a static image object — not an image field object) and then change their presence attribute (make one visible and all the others invisible) when the user performs an action that would’ve let them change the background image.

Since static image objects (as opposed to image field objects) render their contents in the XFA layer at the moment the form is rendered in Acrobat, their contents appears “behind” all the other objects, as is desired in this case.

Fix

Please refer to the Bug List for updated information on the version(s) affected by this bug as well as if and when it was/will be fixed.

---

Posted by Stefan Cameron on October 4th, 2006
Filed under Acrobat,Bugs

---

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.

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:

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:

//query@commandType

You 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.

Data Node Names

Another 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:

SELECT id as catId, name AS catName FROM movie_categories
GROUP 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.

Building the Form

Category List

The 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”.

Movies in the Category

Next, 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.

Filtering Records Displayed by the movieData Subform

Finally, 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:

var sCategoryName = xfa.event.newText
var sCategoryId = $.boundItem(sCategoryName)

Given the XML structure of the <sourceSet> packet displayed above, you first set the query’s command type to “text”:

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:

xfa.sourceSet.MoviesInCat.#command.query.select =
  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:

xfa.sourceSet.MoviesInCat.open()
xfa.sourceSet.MoviesInCat.first()
movieData.presence = "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.

Use the FormBuilderDB20060929.sql file to build the database, create an ODBC Connection named “FormBuilderDB” and load the form.

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}

---

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

---