Link Variables to Database Fields

Once you connect your template to a database, you can select a table in the database and link its fields to variables in your template. These links allow HotDocs to retrieve information from the database table each time the template is used to assemble a document.

When linking variables to fields, data types must match. For example, if the field type is text, you must link to either a Text variable or a Multiple Choice variable (since Multiple Choice variables are text values).

During assembly, HotDocs processes a template from top to bottom. When HotDocs encounters a variable that you have linked to a field in a database table, HotDocs first checks to see whether the list of records for that database table has been displayed yet. If it has not, HotDocs displays it so the user can select one or more records.

Database components are designed to link to only one database table. That means that even though you may see several tables listed in the Table name drop-down list, you can choose only one table for the component.

To create links between variables and database fields

  1. Open the database component you want to link to the database table. (See Edit a Database Component.) The Database Editor appears.
  2. Click the Field Map tab. The window changes to show the fields in the table as well as the variables in the template.
  3. In the Linked Variable column, click the drop-down button that corresponds to the database field to which you want to link.
  4. Select the variable.
  5. Optionally, to show both linked and unlinked variables in the Linked Variable drop-down lists, clear Show unlinked variables only. Be aware, however, that if you select a variable that is already linked, HotDocs will unlink it in order to link it to the new field. Each variable in the template can be linked to only one field at a time. (In these lists, HotDocs uses brackets to show the variable is linked, for example, [Employee Name].)
  6. Repeat this process for every variable that can be answered using data from the database table.

In order for HotDocs to remember which records your users have selected during assembly, you must assign a key field. For information on doing this, see Remember Selected Records.

In HotDocs, NULL values retrieved from a database are considered unanswered while empty string values ("") are considered answered, but empty.

When HotDocs queries the database, it retrieves every record from the table, displays them for the user, and then disconnects from the database. Because of this, you should use a filter on your database component to minimize the number of records that are initially retrieved. (If filtering is not an option but you still want to avoid retrieving all records at once, you can click the Options tab of the Database Editor and clear Use disconnected (client) record set. Be aware, however, that clearing this option can impact the speed with which the user can scroll through and filter large data sets. It also prohibits the user from sorting the data.) (See Choose a Database Cursor for more details.)