Unleashing SQL Power in ProLaw Documents: Calculations Beyond the Basics

Tired of document limitations in ProLaw? You’re not alone. While the drag-and-drop functionality for standard fields is great, sometimes you need to dig deeper and 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.

Let’s walk through how to write a SQL calculation in a ProLaw document type, using the example of calculating Work-in-Progress (WIP) for a specific professional.

Starting with SQL Server Management Studio (SSMS)

First, craft your SQL query in SSMS. This allows you to test and refine your logic before bringing it into ProLaw. For our WIP example, we might start with something like this:

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’

This query calculates the total Extended Amount (ExtAmt) from the Transactions table, filtering by professional initials (‘MPN’), matter ID (‘1000-001’), and stage (‘WIP’).

Adapting for ProLaw Document Types

Now, let’s translate this to a ProLaw document type. Key considerations:

• No Hard Returns: ProLaw calculations need to be on a single line. Remove any line breaks from your query.
• Dynamic Matter ID: We need to make the matter ID dynamic, so the query works for any matter the document is generated from.

Here’s our modified query, initially without the dynamic matter ID:

SQL

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’

The “Load Form” Trick: Enabling Calculation Testing

Here’s a crucial tip: to test your 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 special access enables the “Check Calculation” button, which is normally grayed out.

Dynamically Referencing Matter ID

Now, let’s make the matter ID dynamic. In the document type editor, add the “Matter ID” field from the “Document” folder. Inspect the calculation. You’ll see it’s formatted like MattersQuery.MatterID. This tells us how to access matter fields.

We need to use the primary key of the matter, so change the calculation to MattersQuery.Matters.

Now, we will modify the SQL query to include the dynamic matter ID. Here are two examples of how to do that:

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’

or

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^

Notice the ”’+MattersQuery.Matters+”’ or ^’+MattersQuery.Matters+’^ part. This dynamically inserts the matter’s primary key into the query. The ^ symbol acts as a double tick (‘).

 

Finalizing the Calculation

To make the query work as a ProLaw calculation, we need to wrap it in SELECT(‘…’).

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’ ‘)

Test the calculation using the “Check Calculation” button. You should see the correct WIP amount.

 

Displaying Multiple Records

For queries that return multiple records, use SELECTLIST (comma-separated) or SELECTLINES (line breaks). For example, to display the units from WIP transactions:

SQL

SELECTLIST(‘SELECT Units 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’ ‘)

Conclusion

By leveraging SQL queries within ProLaw document types, you can unlock powerful data manipulation and reporting capabilities. Start with simple queries and gradually explore more complex calculations. This guide provides a solid foundation for creating dynamic and informative ProLaw documents.

#ProLaw, #ProLawFrontOffice, #ProLawDocuments, #ProLawDocumentTypes, #SQL, #ProLawSQL