USING CONDITIONAL STATEMENTS IN LEGALSUITE
Introduction
LegalSuite allows advanced users to specify ‘conditions’ in numerous areas of the program. These conditions are “programming style” statements (e.g. MAT:ClaimAmount > 0) which are used to determine whether certain actions should be performed by the program or not.
For example, you can restrict an Event to occur only if the Matter belongs to a certain Document Set, in this case, FNB.
Figure 1: A conditional Event
They can be very useful to customise the operation of the program. To be used correctly, however, one must have an understanding of:
a) The Tables and Column used in LegalSuite
b) Basic programming syntax
c) Boolean logic (i.e. AND and OR)
This document will attempt to provide this information in a simple and easy to understand format.
The Tables and Fields used in LegalSuite
LegalSuite has over 200 tables which store the program data in a SQL database. Fortunately, only a handful of these are relevant to the conditional queries a typical user will require.
The most important table is the Matter table. Most of the basic information regarding a Matter is stored in this table, for example:
|
|
MATTER (MAT:) |
|||
|
RecordID |
FileRef |
Description |
ClaimAmount |
ClientID |
|
1 |
SMI1/0001 |
Smith vs Jones |
1234.56 |
5 |
|
2 |
FED2/0023 |
Transfer: 21 Main Road |
0.00 |
6 |
|
3 |
FNB1/0003 |
FNB Bond: Mr Green |
0.00 |
12 |
|
4 |
FNB1/0004 |
FNB Bond: Mrs Brown |
0.00 |
12 |
In this example, the Matter table has 4 rows of data.
Five columns (MAT:RecordID, MAT:FileRef, MAT:Description, MAT:ClaimAmount, MAT:MatterTypeID) are being displayed – although there are many, many more columns in the Matter table
Hint: To view a complete listing of the Matter table, you can use this SQL script
SELECT Column_Name, Data_Type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Matter' ORDER BY Column_Name
Each table has a prefix to distinguish its columns from other tables which may have columns with the same name. The Matter table, for example, has the prefix MAT:.
The RecordID column is normally hidden from the user’s view but is very important ‘behind the scenes’ from a database point of view. Basically, it serves to (1) uniquely identify each record and (2) create a link between tables that are related to each other.
Related Tables
In a relational database, tables are linked (or related) to each other. This is an important concept and vital to understanding the layout and relationship of the tables used by the LegalSuite program.
In the above example, you will notice that FNB1/0003 and FNB1/0004 both have the same ClientID (12). That is because they both linked to the same Client (in this case, FNB).
FNB’s details are not recorded in the Matter table – this would be highly inefficient because if FNB’s address changed, for example, we would have to change their address in every record in the Matter table! Instead, we simply record that these two Matter records have a ClientID = 12 and we keep store FNB’s details, once, in the Address Book.
The Address Book uses the Party (PAR:) table to store details about each Party in LegalSuite.
|
PARTY (PAR:) |
|||
|
RecordID |
MatterPrefix |
Name |
Default LanguageID |
|
5 |
SMI |
John Smith |
1 |
|
6 |
FED2 |
Federated Insurance |
2 |
|
12 |
FNB |
First Rand Bank |
1 |
You will notice that FNB has a PAR:RecordID = 12. This is how the Matter table is linked to the Party table: MAT:ClientID (12) = PAR:RecordID (12).
Figure 2: A many to one relationship
This is called a many-to-one relationship because many Matters can be linked to one Party record (e.g. we could have thousands of Matters with a ClientID of 12 but we will only have one Party record with a RecordID of 12). The Party table is sometimes called the Parent table and the Matter table in this case would be called a Child table.
Parent and Child Tables
Parent tables are those which have tables linked to them in a One-to many relationship. The Matter table is actually a Parent to numerous ‘children’. For example, the File Notes, Fee Notes and Reminders are all children of the Matter table.
Figure 3: Child tables of the Matter table
Notice that the link is based on the MatterID of the FeeNote (FN:MatterID) and the RecordID of the Matter (MAT:RecordID).
Here is a list of the main tables and their prefixes:
|
Table |
Prefix |
Description |
|
Control |
CTL: |
Basic Program settings |
|
Language |
LAN: |
Language specific program settings |
|
Global |
GLO: |
Global ‘memory’ variable (e.g. GLO:TodaysDate, GLO:LoginID) |
|
|
|
|
|
Matter |
MAT: |
Basic Matter information |
|
ColData |
COL: |
Litigation information for a Matter |
|
BondData |
BON: |
Conveyancing information for a Matter |
|
ConveyData |
CONV: |
More Conveyancing information for a Matter |
|
RafData |
RAF: |
RAF information for a Matter |
|
MatDocSc |
MDS: |
Extra Screens for a Matter |
|
|
|
|
|
Party |
PAR: |
Basic Address Book details |
|
ParLang |
PL: |
Language specific Address Book details |
|
ParTele |
PTE: |
Telephone Numbers of a Party |
|
ParField |
PEF: |
Extra Screens for a Party |
|
Entity |
ENT: |
The Entity of the Party |
|
ParType |
PT: |
The Type of Entity |
|
|
|
|
|
MatParty |
MP: |
The Parties linked to a Matter |
|
ParRolSc |
PRS: |
Extra Screens for a MatParty |
|
Role |
ROL: |
The Role the Party plays in a Matter |
|
RoleLang |
RLL: |
The language specific description of the Role |
|
FileNote |
FIL: |
File Notes |
|
Fee Note |
FN: |
Fee Notes |
|
ToDoNote |
TOD: |
Reminders |
|
MatActiv |
MA: |
Time Records |
|
DocLog |
DOL: |
Document Log |
|
|
|
|
|
Employee |
EMP: |
Employee the Matter belongs to (Note: Use CEM: for the Current Employee, i.e. the logged in Employee) |
|
Docgen |
DG: |
Document Set of the Matter |
|
CostCentre |
COS: |
Default Cost Centre of the Matter |
|
MatType |
MT: |
Matter Type of the Matter |
|
Grouping |
GRO: |
Groups a Matter belongs to |
|
Branch |
BRA: |
The Branch the Matter belongs to |
|
|
|
|
|
Document |
DOC: |
List of Documents |
|
DocLang |
DL: |
Language details of each Document |
|
|
|
|
Tip: To find out the name of a particular Column in LegalSuite, simply look at the tooltip.
Figure 4: Tooltip contain the Column name of each field
Basic programming syntax
When inserting a Condition into LegalSuite, you need to insert the text in a format that conforms to certain rules of structure and syntax.
The basic format is
|
Value Operator Value |
Some simple examples:
|
VALUE |
OPERATOR |
VALUE |
MEANING |
|
DG:Code |
= |
‘FNB’ |
Document Set is FNB |
|
MAT:ClaimAmount |
> |
1000 |
Claim Amount is greater than R1000 |
|
BON:CapitalAmount |
<= |
1000000 |
The capital amount of the Bonds is less than or equal to R1 million |
|
MAT:BranchID |
<> |
5 |
The BranchID of the Matter is not equal to 5 |
The program
will evaluate the Condition, i.e. determine if it is TRUE or FALSE.
Presuming that a Matter has a CaptialAmount of R500 000, then the Condition
MAT:CapitalAmount <= 1000000
will evaluate to TRUE.
Here are the operators that can be used:
Conditional Operators
= Equal sign
< Less than
> Greater than
NOT NOT
~ Tilde (shorthand for NOT)
Combined operators
<> Not equal
~= Not equal
NOT = Not equal
<= Less than or equal to
=< Less than or equal to
~> Not greater than
NOT > Not greater than
>= Greater than or equal to
=> Greater than or equal to
~< Not less than
NOT < Not less than
Any non-zero numeric value or
non-blank string value indicates a TRUE condition
Likewise, a blank string or zero numeric value indicates a FALSE condition. For
example:
·
Presuming that a
Matter has a ClaimAmount of zero, then the Condition MAT:ClaimAmount
will evaluate to FALSE
· Presuming that a Matter has a description of ‘FNB: Bond: Mrs Brown’ then the Condition MAT:Description will evaluate to TRUE.
Therefore, a quick way of saying “MAT:ClaimAmount > 0” is just to say “MAT:ClaimAmount”
Simple Boolean logic (i.e. AND and OR)
You can use AND and OR commands to expand the scope of your Conditional statements, for example:
DG:Code = ‘MAG’ AND MAT:ClaimAmount
> 50000
will evaluate to TRUE only if the Matter belongs to the Magistrates Court
Document Set and its Claim Amount is greater than R50 000.00.
CEM:LoginID = ‘RJ’ OR
BON:CapitalAmount <= 1000000
will evaluate to TRUE if the logged in Employee is ‘RJ’ or if its Bond
Amount is less than or equal to R1 million.
Complex Boolean logic
Complex Conditional statements such as
DG:Code = ‘FNB’ OR BON:CapitalAmount < 1000000 AND MAT:BranchID = 5
can be ambiguous. The result of the evaluation will be different if the program evaluates the left hand side first (OR) or the right hand side first (AND).
To avoid ambiguity, one should place parentheses (i.e. brackets) around the logic that must be evaluated first, for example
(DG:Code = ‘FNB’ OR BON:CapitalAmount
<= 1000000) AND MAT:BranchID = 5
will evaluate to TRUE if the Matter belongs to the FNB Document Set or
its Bond Amount is less than R1 million but only if it also belongs to
Branch 5.
on the other hand . . .
DG:Code = ‘FNB’ OR (BON:CapitalAmount
<= 1000000 AND MAT:BranchID = 5)
will evaluate to TRUE if the Matter belongs to the FNB Document Set or
if its Bond Amount is less than R1 million and it belongs to Branch 5.
Conclusion
Conditions can be very powerful tools to customise the workings of the LegalSuite program. You cannot “break” the program or lose any data by inserting Conditions. If you get the syntax wrong or use the wrong columns names, the program will not give an error message – the Condition will simply evaluate as FALSE. So if you are expecting a TRUE condition, carefully check your syntax and column names.
APPENDIX A: FILE RELATIONSHIPS
Figure 5: Party relationships
Figure 6: Child tables linked to a Matter