The HotDocs Computation Archive

0118 - Internal Database Tables - An Example

Description:

A sample template to demonstrate one way of implementing internal database tables.

• Explanation •

The sample template described in this explanation is available below as a HotDocs Auto-Install File (.hdi). Both WordPerfect and Word versions are included.

Introduction. This example shows one way of implementing Lee Knight's Computation #0105: Internal Database Tables. In a nutshell, internal database tables are a substitute for pick lists. They allow data such as names to be entered just once and then reused in as many different roles as required. The template in this example is the articles of organization for a limited liability company.

Overview. This template stores all information about people and places in a master repeated dialog called Names. This dialog will hereinafter be referred to as the "Table". The names in the Table are accessed through drop-down lists during assembly. For example, when the user is prompted for the name of the Organizer, they will see a drop-down list containing all names currently in the Table. They may choose one of these names, or they may enter a new name. If a new name is entered, the user will be prompted for the extended information (address, phone, etc.), and the name will then be recorded in the Table and made available in future drop-down lists.

Field values for the names are accessed through a series of generic computation variables. For example, there is a computation called NameFull-c which can return the full name for any entry in the Table. The retrieval of names is controlled by a series of "get" computations, one for each role in the template. For example, there is a getOrganizer-c computation which retrieves the Organizer's information, a getRegisteredAgent-c computation for retrieving the Initial Registered Agent's information, etc. The following code will retrieve the full name, address (multi-line format), phone and fax for a company manager:

«getManager-c»
«NameFull-c»
«AddressML-c»
«Phone-c»
«Fax-c»

Note that once a party's information has been retrieved with a "get" computation, their information remains current until another party is retrieved.

A Note on Variable Naming. The sample template uses the following variable naming scheme:

• Text variables are appended with "-t" (e.g. Name-t)
• Number variables are appended with "-n"
• Date variables are appended with "-d"
• Multiple choice variables are appended with "-m"
• True/False variables are appended with "-b" (boolean)
• Computation variables are appended with "-c"
• All "internal" variables begin with lower-case letters. An internal variable is one that is not saved to the answer file or that will never be inserted into a document.
• The arguments for internal "functions" will generally have the same name as the function. For example, addName-t contains a text string that is passed to the computation addName-c.
• Four generic variables (txt, num, dte, or tf) are used to pass values to function-type computations when there is no compelling reason to have a custom "argument" variable. For example, the computation Period-c determines if the preceding text lacks a period. The value that Period-c checks is the text variable txt. So the following line of code determines whether the variable TextVar needs a period: «TextVar»«SET txt TO TextVar»«Period-c»
• All other three-character, lower-case variables are provided for general use in computations. A few such variables are int, pos, and str. You should not expect the values in these variables to persist past the current computation.

The Nitty-Gritty. All of the inner-workings revolve around two different kinds of computation variables: Internal Computations and Formatting Computations.

Internal Computations. These computations are in charge of all behind-the-scenes work. They update the Table, create the drop-down lists, and retrieve names. There are four of these computations:

1. makeLists-c is in charge of creating the drop-down lists. It purges all values currently in the lists, sorts the items in the Table alphabetically, and then repopulates the lists from the Table.
2. addListItem-c will add a single text value found in the variable addListItem-t to all of the drop-down lists. This computation is used by both makeLists-c and addName-c.
3. addName-c takes a new name from the text variable addName-t, prompts the user for the remaining information, and then adds the name to the Table and to the drop-down lists. It will also cause the Table to be re-sorted (through makeLists-c) as long as a REPEAT block is not currently in progress.
4. The get... computations are in charge of verifying that the selected name in a given drop-down list is an item in the Table. If the name is a Table item, its index value (the three-digit number following the name) is placed into the special variable nameIndex-n for use by the formatting computations. If the name is not a Table item, it is fed to addName-c to be added to the Table and the lists. Once the name has been added, the index value for the name can be assigned to nameIndex-n.

Formatting Computations. These computations are in charge of taking data from the Table and formatting it for use in the document. Each of these computations look to the variable nameIndex-n (set by the get ... computations) to tell them the index number of the name to work with. With this index number, they can retrieve any or all of the values associated with that name. Some examples of formatting computations are NameFull-c, NameLastFirstMid-c, AddressSL-c (address in single-line format), etc. You can create as many of these as your needs require.

Dialogs. As mentioned, the main dialog of this scheme is Names. Its variables are called First-t, Middle-t, Last-t, etc. The most crucial variable on this dialog is a number variable called Index-n. This variable holds the unique value used to identify the name. It is hidden by a dialog script (HIDE Index-n) so that the end users cannot tinker with it.

Critical: A name's Index-n must always correspond to the COUNTER level of the name in the unsorted Names dialog! Reread that sentence. The problem is that when we sort the Names table the COUNTER numbers for the names will not correspond with their actual position in the Table. So we need Index-n to tell us where the name is actually positioned.

A second dialog, Add a Name, is necessary for adding new names to the Table. Both It and its variables are identical to Names, except they are preceded with add: addFirst-t, addMiddle-t, addLast-t, etc. This dialog does not have an Index variable.

A Word on Sorting the Lists. It is nice to have the contents of the drop-down lists sorted. Sorting is done by the makeLists-c computation. Unfortunately, this requires a REPEAT, making it so that makeLists-c cannot be called from within a REPEAT block. This means that if your users will be adding names as part of a REPEAT, those names will not be sorted but instead will appear at the end of the drop-down lists. To get the names sorted again, run makeLists-c immediately after the REPEAT block. This doesn't help the situation inside of the REPEAT block, but it will ensure that the lists are sorted in the future.

A Step-By-Step Example:

• (If you plan on populating the Table from an outside data source, this should be the very first thing done).
• The first thing that assembly should typically encounter is the makeLists-c computation. This will clear out any old values in the drop-down lists, check whether there are any values in the Table (from the answer file or an outside data source), and add these values to the lists.
• Your template should then contain get... computations everywhere that personal information is needed. When a get... computation is encountered, the following will happen:
• The variable will be asked via a drop-down list (if it is UNANSWERED).
• If a name is selected from the list, its Index-n value will be placed in the special variable namesIndex-n and the computation will terminate.
• If a new name is provided, it will be added to the Table as follows:
1. The name is placed in the text variable addName-t, and addName-c is called.
2. addName-c parses the name into first, middle and last, and pre-sets these values in the the Add a Name dialog (via the text variables addFirst-t, addMiddle-t, and addLast-t). The Add a Name dialog is then brought up so that the user can provide the remaining details. (Note that if this is ocurring within a REPEAT block, the Add a Name dialog will not appear until after the REPEAT is done).
3. The values in Add a Name are then added to the very end of the Names table.
4. If there is not a REPEAT currently in progress, makeLists-c is called so that the new name will appear in alphabetical order within the drop-down lists.
5. If there is a REPEAT in progress, the new name is added at the end of the drop-down lists (to avoid errors).
6. The proper drop-down list entry for the name is returned to the get... computation.
• The value returned from addName-c is now SET as the answer for the drop-down list, and its Index-n value is placed into the special variable namesIndex-n.
• Finally come the formatting computations. These will pull and format names (and other personal info) from the Table based on the current value of namesIndex-n. Once namesIndex-n has been set, it will remain current until it is changed by another get... computation.

Conclusion. The initial setup for this system is quite intensive. But once the system has been created, it is 100% reusable across every template set thereafter. All of your internal and formatting computations are completely generic. They will work anywhere without modification. In the future, the only personal information variables you will ever need to create are "get" computations. Low overhead, easy maintenance, and complete reusability. That equals pleasant template development!

• Model Template •

This template has everything you need set up and configured for you. It will work as-is, or can be adapted to your variable and dialog names. It contains: 1) sample Word and WordPerfect templates (or an Automator form) to demonstrate an implementation of the computation, 2) a component file containing the computation and all supporting dialogs and variables, and 3) instructions for adapting the computation for your use.

(Go to the download page)

• Contributors •