SQL Adventures – Navigating PCLaw Billing Preferences From HotDocs

The answer to the question is “MatterInfoSpareLong2”.  After several hours of searching, calls to former PCLaw product managers and tech support directors, I got the answer I was searching for.  My odyssey had started with a simple request: “Produce a report that combines File details in Amicus Attorney with billing and receivables details in PCLaw.  Having presented a seminar on integrating Amicus Attorney and HotDocs, I thought it a simple matter to integrate HotDocs and PCLaw.

On the surface, it was easy.  In PCLaw there is a MattInf table that stores the Matter information.  You can use the MatterID from within HotDocs to query the SQL database and get file information.  On the MattInf table there is a ClientID that links to information on the primary client, allowing you to pull in client data. For current statistics, there is a MattBal that contains data on each file regarding account balance, receivables, billings etc.

Where are the Billing Preferences?

The issue arose when I compared the “Amount Quoted” that was on the Matter form with the “Amount Quoted” that was on the MattInf table.  I found a series of fields on that table that had “no relation” to the fields on the actual Matter form.  A hint from a former PCLaw employee directed me to the MattBill table.  There I found another field called AmountQuoted and a whole range of billing preferences.  The problem was that there was no foreign key I could use to connect the MattInf table to the MattBill table.  Without a foreign key, there would be no way to “join” the two tables in a query.

The typical key would be one like ClientInfoClientID or MatterID or MattBillSettingsSeqID.  After pulling out my hair (the little I have left), several emails and call and hours of pouring through the SQL Manager, we found the filed.  It was called “MatterInfoSpareLong2”.  That was entirely obvious.

NOW, armed with that key, I could find all the Billing Preferences, link those to the File information, join it to the current Matter Balance, and actual report on the variance between the Amount Quoted (found on the MattBill table) and the Amount Billed, to determine the remaining amount available in the kitty for each client.