External Database Connections--Indirect
See this page for information on direct links to a database.
TimeMatters(r) Amicus Attorney(r) and ACT(r) are a popular time, billing, practice and contact management programs among attorneys. There are others (TimeSlips, PCLaw, Tussman, etc.) Other professions have their own popular contact and customer managers. More focused database programs also abound--Access(r), Alpha5(r), FoxPro(r). The list goes on and on.
These programs are all similar in that they have the ability to manage a large variety of important information about clients and customers, adversary parties, dates, etc. They are quite powerful in what it can do, but at their core, they are all database programs. As such, they contain information that you might want to use in your document assembly and other word processing functions. Some of them allow direct links to the tables comprising the database. Some will allow it, but only if you purchase a special driver. And even then, you need to know how to create merge and linking fields to make the two systems tie together.
Some of these programs are so possessive of their data that you can only get to it indirectly. But even that is okay. An indirect link may actually serve you better than a direct link.
The beauty of Pathagoras' 'plain text' approach is that you don't have to mess with merge and linking fields, and with connecting a document to a database using (You can do this if you wish. It works very nicely. But it can get complicated. We recommend it only for the experienced Pathagoras user and databaser)
| External Database Connection: 1-minute tutorial 1. Activate your external database. Export to a comma separated file the data that you wish to use in your Pathagorized document that alone or in combination with other fields will be the equivalent of the variables in an Instant Database mask. (Make sure that you export the field names along with the personal data.) 2. One time only (add 5 minutes to tutorial). Skip if previously performed. Activate the External Database Matching Screen via Utilities/Settings>>InstantDatabase Tools. Recall an InstantDatabase mask to which you want to map external data. Following instructions on that screen, match up the external database field(s) with the IDB variables. Save the information. 3. Create or recall a document that you want to personalize for the client/customer identified in step 1 above. (If you haven't exported the data from the external database, do so now.) Display the InstantDatabase mask displaying the assembled document's variables. 4. Click the <Database> button and click option #1 ("Complete Mask with Data from Last Exported Client"). Pathagoras quickly imports the external data and places the information from the external database onto the mask in the appropriate locations. 5. Edit the imported or other fields if appropriate. Save the data if you intend to reuse it. Press the <Next> button to replace the variables with the personal data. |
The nuts and bolts:
Every popular database program allows you to export one (or dozens) of selected records from their files. The export can be made into a simple "flat, comma separated" file. Pathagoras can easily read that file, and use its content to automatically, and instantly complete any InstantDatabase mask. Once the information is in the mask it can be used like any other IDB mask to personalize an assembled document.
Further, you can save the data out to the IDB system and recall it in order to complete other documents for the same client that are based on the same mask.
Here are some other advantages of this method of data sharing.
1. If you create direct links to your data, you leave the 'plain text' environment. You will not be able to readily edit and text your documents unless you are on (or are connected to) the machine that has the data.
2. Direct links do not typically allow you easy access to data in relational databases. Relational databases are actually separate tables of data that are linked via a common field. E.g., some databases allow you to create a client level record, in which you place essential client data such as names, addresses, etc.. 'Matter' information for the client is stored in a different table. The records are married together through a common field. When you are in the database itself, you can see all the data as if it were a seamless, continuous, huge database.
When you start placing, and then recalling, fields of related (sub-) databases into a document in Word, however, it may get confusing. With the 'indirect method' of linking described here, the process is simpler and cleaner. Use the external database to select the client and the matter information. Export all of the relevant data from all related databases into a single file in a single action. You need not worry about performing the complex linking steps to get to sub- and sub-sub-tables in your database. Once the data you need is exported to a flat file, use Pathagoras' matching table to match the exported data with the appropriate InstantDatabase variables.
3. If you create direct links, you lose the universal character of you document and your system. A document embedded with specific database fields is now inextricably married to your external database. This is not necessarily a bad thing. Many offices want that to be the case anyway. But the 'plain text' nature of Pathagoras allows your documents to be used anywhere and everywhere -- i.e., you could even sell your stuff if you were so inclined. A purchaser of your forms would only have to follow the steps below to match your plain text variables with information from his or her database. The end user could easily use the data in his or her database and not have to rewrite any of your base documents to accommodate it.
--------------
Warning: The following requires you to have a least a working knowledge of the operation and structure of your external database system. Before trying to tie Pathagoras and and an external database together, you should know where (i.e., which tables) you data is stored. For example, TimeMatters stores data in many different tables, but the primary tables for the type of personal data you likely will want for documents will be "Contacts" and "Matters." You may have to consult with your expert in your external database in order to fully implement the data sharing techniques discussed below.
Assumptions:
We must assume only three things before getting started.
1. That you have created a document or glossary that has plain text variables and that you have created an InstantDatabase mask which you can use to manually provide information for each new client.
2. That you have an external database which already contains client data with which you want to complete the InstantDatabase mask. Also assumed here is that you know, or can determine, the names of the fields in that external database that contains the desired data.
3. That, instead of manually retyping the client's name, address, etc., into the IDB mask after you have created a document for a client, you want to call in data from your external database.
Setup:
There are two aspects to the process. The first is to create the export template that contains the data. The second is to match with the names of the database fields. Each step is easy, but different depending upon which external database program you use. To the extent that we know the steps, we have laid them out for you on separate screens. Just click on the appropriate system. To the extent we don't (yet) know the steps for a specific system, the generic steps stated below should be of help. Check the documentation that came with your database system or ask your systems administrator for more guidance.
TimeMatters Act
(A little help please. If you find that the setup or matching instructions for the specific database programs are not correct, please write with the location of the erroneous step. Let me know which version of the database program you are using. If you use another database, please be so kind as to e-mail me the specific instructions for how to export data from the one you use. )
General Instructions for creating the export template.
From the external database program:
1. Find the routine that will export data. If frequently is called simply "Export." Click it.
2. Follow the prompts to create the template.
Along the way you may be asked how you want the individual records separated. Select linefeed ("LF") or carriage return ("CR") or if available "LF/CR".
Along the way you will be asked how you want the individual values within a record to be separated. Typical choices include 'tabs' and 'commas.' SELECT COMMAS. The name for this not standard across all programs, so look for the phrases "comma delimited” or "comma separated value" or "CSV."
You will be given the option of exporting the field names as the first record. Answer this "Yes." (This is a critical aspect of the routine. If the field names are not exported, the matchup cannot occur.)
And finally, you will be asked to name the file where the file should be saved. You can name it anything and put it anywhere, but you must be able to find it for later use. If you cannot think of a better place, save it in the "c:\windows\temp" folder as "PathData.txt" Therefore, the full file name would be "c:\windows\temp\PathData.txt"
Once the export template is created, you won't have to do that again. Of course, in the early stages, as you are refining the field selections (adding more fields to the exported database or removing extraneous ones), you may come back to the template, but you will rarely, if ever, have to start all over.
Exporting a specific client's record.
Again the steps may vary between programs, but the essential steps are these.
Display (or check) the record(s) you want to export. (You may export as many as you want, but 5 or 10 for a Pathagoras session is a practical maximum.)
Click the 'Export' button or item from the menu.
Select the appropriate export template (the one you created above), or keep the defaults if no template was actually created.)
If the option to "Exporting the field names as the first record" is presented, answer this "Yes." (This is a critical aspect of the routine. If the field names are not exported, the matchup cannot occur.) Many programs require you to select this each time. It is not saved as part of the template or default pattern. Do be aware of this.
Save the record anywhere you desire. Just make sure you record (or otherwise remember) what you named it and where you put it.
That's if for this 'side.'
The Mapping Screen
The 'secret' to making all of this work (i.e., matching the IDB variables with the database values) is the mapping screen.
Access the mapping screen via the <Database> button on the InstantDatabase screen. Click option #2 from the resulting menu. A seemingly complex (but in reality fairly simple) 'Mapping Screen' then appears. You need be concerned with only three sections of of the screen.
(1) At the left is the InstantDatabase mask section. Click the drop down list at the top left and select one of your existing IDB masks. The current variables are displayed in the left most column. (The second column from the left shows the current description of the variable. It is grayed out because it is for information only.)
You cannot edit any of your IDB fields from this screen. They will be locked.
(2) Right side of the screen (starting with the third column) contains, or will contain, the fields from the external database that, alone or concatenated, will be the functional equivalent of the InstantDatabase field at the far left.
(3) The top of the screen (which initially displays only instructions until you import the external data) will contain the external database field names and the concatenate buttons. The fields are displayed after the green <Import External Data> button is pressed.
The actual mapping process is as elegant as it is simple:
(1) Click the green <Import External Data> button. That will feed the information of the flat file into the large box at the top. (If you haven't previously selected the External Datafile, you will need to do that first.)
(2) Click in the appropriate box in the third column to activate it.
(3) Double click on an external database field name from the list at the top The name of the field is transferred into the active box. If you need to combine ("concatenate") two or more database fields to equal the desired result for the IDB variable, use one of the concatenate options when adding a second or third field.
Note: At the present time, no punctuation can be added to the concatenated values on this screen. However, when the data is eventually pulled up on the InstantDatabase mask, you can easily add punctuation and appropriate spacing, and store the new (fully punctuated) record in a personal InstantDatabase file.
Once you have completed mapping the variables to the corresponding database fields, save the map by clicking the blue <Save> button. The name of the matched- up file will be the same as the base InstantDatabase mask (but Pathagoras will automatically assign it a different prefix).
Once the 'map' is created, you won't have to do that again. Of course, in the early stages, as you are refining the mappings and field selections, you may have to come back to the mapping screen. Recall your work for editing from the drop down box at the top-right.
Mappings in operation:
Getting personal data from the external database into the InstantDatabase.
Okay, you have (a) created the export template, (b) created a mapping scheme, and (c) exported a record. Now how do you get it in the InstantDatabase? From here on out, it is a piece of cake. (a) Call up the IDB screen (<Alt-D> will typically do it. (b) Load the mask that has been prepared for this document. (c) Click <Database> and click option #1. The matchups are done quickly and automatically.
Multiple Records
If you exported more that one record from your external database, you will see an up and a down arrow next to the <Database> button. Use those buttons to scroll through the list of exported records..
Save the new personal IDB record in the normal fashion.