An Elite Database as a data source

XpressDox can retrieve data from just about any data base, but in particular, data from the Elite practice management system can be made available to your templates.

The Elite data base is a Microsoft® SQL Server database, and so can be configured as such in the XpressDox configuration dialog.  Below follows a step-by-step look at configuring a data source to make clients and their matters available to XpressDox.  In some ways this is just the beginning, because all data in the Elite database can be used in Xpressdox, not just client and matter data.

Configure the Data Source

1. Open the XpressDox configuration form – in the Manage Settings group in the Word 2007 Ribbon (or in the main XpressDox toolbar in Word 2003).
2. In the drop-down box at the top of the form, choose the folder for which you want this Data Source configured.
3. Click on the Data Sources tab, and then click the “New” button on the Data Sources toolbar (the button on the left with a green + sign on it).
4. Choose the SQL Server Database radio button and press Next.You will need to supply the correct Server Address and Database– speak to your Data Base Administrator (DBA) about this.Sometimes it is necessary for the DBA to create a special user name (and password) to enable access from XpressDox to the database.  If this is done, then just enter the user name and password into the spaces provided on the dialog and XpressDox will construct a connection  string for you.Most times the connection string constructed by XpressDox is correct, but if it needs modification (at the behest of the DBA who knows about these things) then it can be modified in place.
5. Once the connection string has been entered, press Next.
6. You now need to choose the table or view in the database that this Data Source is intended for.  Choose this from the drop-down box.  (If the drop-down shows  list of tables and views, then you know you have configured the connection string correctly).  For this recipe, choose dbo.client, which is the table containing the information for all the clients.XpressDox will suggest a data element name for the table itself – in this case it will suggest the name of the table, i.e. dbo.client. If you were to ever refer to a collection of these data elements (i.e. when the data elements are repeated) you would probably want to say something like «ForEach(client)», not «ForEach(dbo.client)», and so you now have the chance to indicate this by changing the data element name to client.Press Next.
7. XpressDox needs to be provided with an “ID” for the table.  This is a column, or group of columns, whose value(s) uniquely identifies a row within the table.  This could be the Primary Key in the database, and it would probably make sense to choose this.  The Data Source wizard takes a stab at suggesting an ID column.  If it can’t find one column defined as unique, then it presents some columns for you do choose from.  In the case of the Elite client table, the value to select here is clnum, as this is the client number.Press Next
8. At this point XpressDox needs to know the primary usage of this Data Source in terms of the number of database rows to be returned in the Data Source data set.  For purposes of this recipe, the first option is appropriate,  i.e. “The user must be able to choose a row from this table”.

   Note that it will still be possible for a template author to select all the rows, or a subset of rows from the table for a specific purpose, but if you choose the option to enable to the user to select a row, then XpressDox will automatically construct a Search dialog which will be presented to the user (you will be able to see what this Search dialog looks like when the wizard is complete and you use the Test Data Source feature).

   

   In order to construct this search dialog, XpressDox needs to know a number of things, which are presented to you in the wizard.

9. Firstly you are asked to choose the columns which are presented in the Search Dialog.  The wizard will suggest a few, but in the end you will know best how to present the data so that the user will easily identify the row of the client they would like to select. The columns in the screenshot above have been suggested, but in fact you can include any columns that you think will be helpful.
10. Part of the Search dialog is a filter, which enables the user to narrow down the search and the amount of data displayed.  The filter works on one column, and for the data source which we are dealing with, it is probably best to choose the clname1column from the “Filter Column” drop down.Press Finish.
11. The Data Source will now be displayed in the main Data Sources configuration form, with the name “New SQL Server database”.  You need to give the Data Source a better name than this – just choose something meaningful like “EliteClientMatter”, type it in and press TAB and then press the Save button.
12. The Configuration should now be saved.  Then you can use the Test Data Source button on the Data Sources toolbar (the one with the green check mark in it).  XpressDox will present the Search dialog to you, and you can search for a client. When you have chosen a client you will be shown, in a separate window, what the XML dataset for your choice looks like.
13. You will now be given the option of creating a schema for this data source. Don’t do this yet, as the data source needs a bit more work until it’s ready.

Linking Supplementary Tables to the Main Table

1. It is now necessary to link the matters which belong to each client to the client, so that when the user chooses a client in a template, then the matters for that client also become available to the template. This is where the really powerful data base features of XpressDox come into play. To achieve this linking, you will need to select the “Edit Definition String” feature by pressing either the big “Use Data Source’s Editor” above the Definition Stringfield on the right of the configuration dialog, or else press the button in the toolbar, as shown:
2. The screenshot below shows what the Edit Definition String form looks like. (There is a lot of inline help on how to use this interface, and this help appears in the yellow are on the bottom right of the screen. The help text changes depending on where the cursor is hovering). Initially, the Collection entry for Matter will not be there – this is what you need to add now.
3. The way to get new entries into the treeview on the left is to <right click> on the relevant Collection node in the treeview and then select New and select the type of entry you want. In this example you will only ever need to add new Collections. On the node, press <right click> and add a new Collection.
4. Type Matter into the Name field, and also a description for this collection. Choose the table dbo.matter from the drop down list, and make sure the answer to the question “Only one row to be retrieved for this collection?” has “No” checked (you can read up in the in-line help what all these fields mean).
5. Now click the IdColumn node under the Matter Collection in the treeview.
6. Choose the column “mmatter” for the ID Column Name. “mmatter” is the name of the column that Elite has chosen for the matter number (so it is column “mmatter” in the table “mmatter”). And give it the caption that you would like.
7. Click the Range node in the treeview under the Matter Collection. This is where the collection of matters for a specific client is linked to that client. This is done by indicating that the mclient column in the Matter table must have the value of the the client which is in the client row’s clnum column.
8. The mmatter table in the database has references to a number of attorneys: the Billing Attorney (referred to by code mbillaty), the Originating Attorney (code morgaty) and Supervising Attorney (code msupatty). In order to get the attorney’s details other than the code, such as first name, last name, etc., we need to link in the table dbo.timekeep for the attorney(s) that we are interested in. For this example, we will link to the Supervising Attorney (and the same mechanism is used for the other attorneys). <right click> the Matter Collection in the treeview, and select a new collection.
9. As you did for the Matter collection, give this collection a name (e.g. SupervisingAttorney) and a description, and choose the table from the drop down as dbo.timekeep. Click the “Yes” in answer to the question “Only one row to be retrieved from this collection?” (it must be Yes because there is only one Supervising Attorney, by definition). And set that the Prefix on column names must be “SuperAtty” (this is so that the sample template will work – in general you can give this any suitable prefix, so that if you add links to get the data for the Originating and/or Billing attorneys then they can be given unique prefixes which help to distinguish them in the data set).
10. Click on the “IDColumn” node under the new collection (for the Supervising Attorney).
11. Set the ID Column Name to tkinit and give the collection a caption.
12. Now, to link the correct dbo.timekeep row with a row from the mmatter table, select the Range node for the Supervising Attorney collection in the treeview.
13. Link the tkinit column in the timekeep table to the msupaty column in the mmatter table, as shown in the screenshot.
14. Save the data source, and test it to make sure it works.
15. Create a schema for the data source (using the right hand most toolbar button) and once you have saved it it will automatically populate the Command Editor with the data element names from the configured data source.

Using the Data Source in a Template

In the template that is to use this Data Source, use the The Command Editor to include the command:

«ChooseFromDataSource(EliteClientMatters,Choose the Client)»

This will create a control in the interview for the template which will give the user the chance to choose the client for this template.

If you created a schema, you will then be able to choose data element names from that Data Source just by double-clicking the name in the Command Editor.

Once you have downloaded the template, make sure it is saved into the folder where you configured that data source, so that the ChooseFromDataSource command in the template will have access to the data source configuration.