Unleashing SQL Power in ProLaw^® Documents: Calculations Beyond the Basics

Tired of document limitations in ProLaw^®? While the drag-and-drop functionality for standard fields is great, sometimes you need to perform calculations directly from your database. This is where SQL queries come in, allowing you to pull specific data and perform complex calculations right within your document types.

Starting with SQL Server Management Studio (SSMS)

First, craft and test your SQL query in SSMS. For our Work-in-Progress (WIP) example:

SELECT SUM(Transactions.ExtAmt) ExtAmt
FROM Transactions
INNER JOIN Professionals ON Professionals.Professionals = Transactions.Professionals
INNER JOIN Matters ON Matters.Matters = Transactions.Matters
WHERE Professionals.Initials = 'MPN'
AND Matters.MatterID = '1000-001'
AND Transactions.Stage = 'WIP'

Adapting for ProLaw^® Document Types

Key considerations:

• No Hard Returns: ProLaw^® calculations need to be on a single line.
• Dynamic Matter ID: Use MattersQuery.Matters to make the matter ID dynamic.

The "Load Form" Trick: Enabling Calculation Testing

To test calculations within the document type setup, you need to access it from within a matter:

1. Open a matter.
2. Add a new document.
3. Click the ellipsis button.
4. Click "Load Form" after selecting your document type.

This enables the "Check Calculation" button.

Finalizing the Calculation

Wrap your query in SELECT('…') to make it work as a ProLaw^® calculation. Use the ^ symbol as a double tick ('):

SELECT('SELECT SUM(Transactions.ExtAmt) ExtAmt FROM Transactions
INNER JOIN Professionals ON Professionals.Professionals = Transactions.Professionals
INNER JOIN Matters ON Matters.Matters = Transactions.Matters
WHERE Professionals.Initials = ^MPN^
AND Matters.Matters = ^'+MattersQuery.Matters+'^
AND Transactions.Stage = ^WIP^ ')

Displaying Multiple Records

For queries returning multiple records, use SELECTLIST (comma-separated) or SELECTLINES (line breaks).