Derivative Database

Top  Previous  Next

  The problem: The data maintained by most databases contain much more data that what are needed to produce a typical document.  It would be cumbersome to directly link to them and then have to scroll though dozens, if not many hundreds, of fields to select the ones that are appropriate for the document under construction.

  The solution: create sub-set of the database, selecting only relevant fields and culling out the useless one. The selected fields could be used to create a more useable 'derivative' database.

  This derivative concept actually is a necessity with some programs. Many databases do not allow you directly access to their data from any external program. This promotes security of the data (so that it cannot be stolen by others) and the integrity of the source data. (These concepts are not identical. 'Security' refers to the ability of a 3rd party to abscond with the data maintained by the parent program. 'Integrity' refers to controlling the ability of others to change the raw data.)

   All major (and probably all minor) databases can create such derivative databases. They can export them into 'comma separated value' files, a type of database with which Pathagoras works especially well. The files can be created every morning (or at every relevant time, maybe two or three times a day). And the process of creating the derivative database can be automated in such a way that it takes no more time to create a new (updated) CSV file than it takes to open the parent program.

  Creating a Derivative Database:


   The following requires you to have a solid working knowledge of the operation and structure of your external database system. Before trying to tie Pathagoras and an external database together, you should know where (i.e., which databases and 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."

   If you are not totally confident about working with your external database, you should consult with the expert in your external database before trying to implement the data sharing techniques discussed below.


   We assume only two things before getting started in creating a derivative CSV database.

   1. That you have an external database which already contains data which you want to use to complete documents created using Pathagoras.

   2. That you know, or can determine, the names of the fields in that external database that contains the desired data.

  The process:

 The first step is to create and save a "export guidelines" within the external data source. This is a one time step.

 The second step is to export the records identified by the export guidelines into a CSV file. This step is repeated as often as necessary (daily, weekly, etc.) to make sure that the derivative CSV file is 'up to date.'

 The following is general guidance on how to create the "export template." Check the documentation that came with your database system or ask your systems administrator for guidance on the specific steps you must take.

 General Instructions for creating the export guidelines.

1.Activate your the external database program.
2.Find the routine that will export data. If frequently is called simply "Export." Click it.
3.Follow the prompts to create the template, using the following concepts to guide you in structuring the final results.
4.You likely will be asked how you want the individual records separated. Select linefeed ("LF") or carriage return ("CR") or if available "LF/CR".
5.You likely 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."
6.You will be asked something along the lines of "Do you want to Export 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 field names and data matchups cannot occur.)
7.You likely will be asked if you want all or a selection of the available records. We'll leave that up to you. If you can sort by date and just want the records from the last one or two months (or years or decades), make the appropriate selection. If you want to create separate CSV files for each attorney or agent or whatever in the office, make that selection.
8.You will be asked to name the file into which the derivative data will be saved. You can name it anything and put it anywhere, you want, but you must be able to find it for later use. If you cannot think of a better place, save it into a folder on your desktop that you call "Database." Call the file "ClientData.csv".
9.You will be asked if you want to save the export guidelines. Say 'Yes' and give it a name that you will remember.

 Once the export guidelines are 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.

   Updating the derivative database:

   As new records are added to, corrections made in, and records deleted from, the main database, you will want to regenerate  the derivative database. As with creating the original database, the steps vary, but essentially you will activate the database and call up and 'run' the Export Guidelines you created earlier. Everything else should be quite automatic. Indeed, to the extent that the database has Automation or Macro features, you should be able to create a one-touch approach to regenerating the derivative database.