This article is based on a design specification from an actual customer project in Agiloft.

Design Requirement

The customer KB should be able to compile and product a blank contract file from a clause library based on a number of parameters such as the selected contract type in the contract and solicitation records. The user only needs to set the metadata in the record correctly, then click the Generate Blank Contract button. The system then assembles the contract file and gives a browser pop-up once the contract generation is complete. The total assembly process takes about 30-40 seconds. 

High Level System Design

There are four tables needed for the design:

• Solicitation Table (SOL) – Major processing table where the blank contract is generated. 
• Clause Library (CL) – Background table holding all of the available clauses in the system. Clauses are composed in the HTML editor.
• Contract Type (CT) – Each Contract Type is loaded with a set of Contract Type Clauses and form templates.
• Contract Type Clauses (CTC) – Intermediate table which links the Contract Type with clauses from the Clause Library. A CTC record has the form Contract Type - Clause Name, for example Maintenance Contract - Premises/Assigned Area.

In addition, the following Excel sheets and scripts can be used to import clauses and clean Word formatting from clause text: 

• Contract Language Matrix
• Contract Type Clause Import Template
• Example CTC Import Template
• Cleanse HTML Script

System Design Example

Assume that we have overall 100 clauses stored in the CL table with ID 1-100; and we have two Contract Types, TypeA and TypeB. Contract TypeA uses CL ID 1-80; and Contract TypeB uses CL ID 50-100. Each CTC record is related to one contract type and one clause library record. For CL ID 1-80, we generate 80 records in the CTC table related to Contract TypeA; and for CL ID 50-100, we generate 50 records in the CTC table related to contract TypeB.

The first differentiator is the contract type. Each contract type has a pool of clauses it can use, stored in the CTC table. When the system generates the blank contracts, it is checking the CTC records instead of the CL records based on the selected Contract Type in the SOL record.

CTC records can have one of three types of CTC records:

• Required CTC: The CTC clause is always required for the related Contract Type.
  • CTC Parameter: Usage = Required
• Conditional CTC, System Defines: The CTC clause is optional, but the system determines whether to include the clause or not based on the metadata in the solicitation record. For example, clause "ACDBE – Version 1" will be included if, in the Solicitation record, we have "Minority Business = ACDBE" and "Minority Business Goal Applicable = No Goal".
  • CTC Parameter: Usage=Conditional, Determination Method=System Defines. The Condition formula field holds the conditions using the metadata in the Solicitation record.
    Note: "Usage" and "Determination Method" could be combined into one parameter. 
• Conditional CTC, User Picks: The CTC clause is optional, but the system does not hold the information to determine whether to include clauses. Instead, users need to select the clauses they want to include in the solicitation record before generating the blank contract.
  • CTC Parameter: Usage=Conditional, Determination Method=User Picks. All records with these parameters are included in the Solicitation "Include conditional clauses" field for users to pick.
    

Each CTC record must belong to one of the three types above.

The second layer of differentiation between CTC records is based on the metadata in the Solicitation record.

Blank Contract Generation Workflow

Each CTC record has a Sequence value. The Sequence defines the workflow and so must be unique for a specific Contract Type. For example, for Contract TypeA, we cannot have two CTC records with "Sequence = 1". Instead, we can make one CTC sequence "1.0", and the other sequence "1.1".

When the system generates a blank contract, it goes to the first CTC record. After judging and converting the first CTC, the system goes to the next CTC with the next highest sequence. The process goes on until all the related CTC are judged and appended to the Solicitation "File Content" field. At the end, the system takes the file content and compiles it into a file to generate the blank contract.

Features

• System generates the file completely using the Clause Library.
• Contracts can have "Required" and "Conditional" clauses
• The generated file makes use of dynamic numbering grouped by article number. For more information, see Dynamic Numbering.

Limitations

• The contract must be generated completely from the Clause Library. This design is not good at inserting clauses into certain places in a contract template.
• Clauses must follow a determined sequence, distinguished by the Contract Type. Please see the Contract Language Matrix for further examples.
  To work with conditional sequences, see the example below for a possible solution.
• It does not have an approval mechanism for contract clause changes, so if approvals are needed for a specific contract's language, it cannot be done directly. Instead, users have to work with the generated Word document.

Possible Enhancements

• It is possible to insert variables into the generated contract. To do so, use the generated "Blank Contract" as an intermediate file, and generate the final contract with variables. In this case, the variables should be included in the Clause Library/CTC Clauses fields.
• We could include the ability to approve changes at the CL and CTC levels.

Conditional Sequencing Suggestion

As an example, we have ClauseA and ClauseB. Under Condition A, Clause A goes in front of Clause B; Under Condition B, Clause A goes after Clause B; and only one of Condition A or Condition B will be met under any circumstance. In this case, we could set up three CTC records to work with conditional sequencing. Note that this approach is not recommended because it could make the system hard to maintain. 

System Maintenance

The sections below will help with managing certain aspects of the clause library system design. 

Dynamic Numbering

Dynamic numbering of the contract articles can be set up by the system, to a limited degree. For this, you will need to store the clause text differently than how it appears in the contract. For example, a contract contains clause numbering as below:

All the "3"s in the text can by dynamically numbered, so that if the section is generated as the fourth section in the contract, it will display "Article 4", and "4.01 Effective Date".
To set up this capability, when storing the clause into the system just need to replace the heading numbers with _num, so the data stored in the Clause Library looks like:

Update Existing Clauses for all Contract Types in the Clause Library

To update the language in an existing clause, go to the Clause Library table and edit the target record.  This process must be done manually and one at a time, because attempting to update clauses using Excel will cause Word formatting to be lost.

1. Turn related CTC auto-update on or off
   In the CTC record, the "Should System Auto-Update Clause" field determines whether the system will update the clause in the CTC record if the underlying Clause Library's wording changes. Modify all CTC records to hold the correct Yes or No value for this field before updating the Clause Library record. 
   
2. Update the target Clause Library record
   Go to the Clause Library record and update the Clause field by either editing it directly or copying the updated clause from a Word document.
3. Click the Cleanse HTML button then Save
   The Cleanse HTML button causes the formatting to be retained in the generated contract file. Once this has been done, click Save to save the update. 
   
4. Once saved, the system updates the clause language in all related Contract Type Clauses where the auto-update field was set to Yes, and and leave the ones with auto-update set to No untouched. 

Customize a Clause for a Specific Contract Type

For a specific article (clause), each contract type can hold a different version of a clause.

1. Go to the Contract Type Clause record, and modify the Clause directly in the record.
2. Remember to set Should System Auto-Update Clause to No if you want to turn off the auto-update for this CTC in the future.

Add a new Clause to the Clause Library

If we have new articles (clauses) you want to add into the system, you firstly need to create the Clause Library record for it. There are two ways of creating background clauses:

Method 1: Manually add a Clause

1. If you are adding a small number of clauses into the system, go to the Clause Library table and create a new record. 
2. Give the new record a title for future reference, and copy the clause text into the Clause field.
3. Click Cleanse HTML button.
4. Save the record.
5. Continue creating records as needed.

Method 2: Batch Import a Clause

If you are adding a lot of background clauses into the system, you can use a batch import in the Clause Library table to create the records. However, there is normally formatting in the clause text, the import function can only be used to create the records, and the custom formatting must be done manually.

1. Add new Clause Library records via an imported spreadsheet.
   1. Create a spreadsheet with Column A - Clause Title. 
   2. In the table, select Actions > Import to import it to create the clause library records. 
      
2. Manually update the clause language in the Clause Library records.
   Go through the clause library records and paste the text in the Clause field, generally with the Word formatting included.
3. Click the Cleanse HTML button
4. Save the record. 

Add New Contract Type Clause

CTC records hold a pool of clauses that can be used for certain contract type. If you need to create a CTC record, you must make sure the target clause already exists in the Clause Library table, and the contract type already exists in the Contract Type table.

There are two ways of generating the CTC records:

Method 1: Manually create new CTC records

1. If you are adding a small number of CTC records, go to the Contract Type Clauses table and create a new record. 
2. Enter the basic information in the record including:
   1. Related Contract Type
   2. Sequence number
      Note: the sequence needs to be unique for certain Contract Types. For example, for Goods contracts there should only be one CTC records where the sequence=3. 
      For conditional CTCs with multiple versions, you can assign a decimal number to the sequence. For example, ACDBE - Version 1 can have sequence 18.1, while ACDBE - Version 2 can have sequence 18.2.
   3. Related Clause Title
   4. Usage, Determination Method and Condition. 
3. Click Save. You will get an error message if the sequence you entered already exists. Modify the sequence and save again.

Method 2: Batch Import Contract Type Clauses

If you need to generate a large amount of CTC records - i.e, when you create a new Contract Type - you can use an Excel spreadsheet to create multiple records via import. Please pay attention to all the steps in this section.
You can use this Contract Type Clause import template.xlsx file for the import. 

1. Export all records in the Clause Library table.
   When exporting the Clause Library records, only include the Clause Title and ID fields. 
   1. Copy the Excel spreadsheet into the ClauseLibrary sheet in the import template. 
   2. Column A should be Clause Title and Column B should be ID.
2. Add values to the CTC spreadsheet.
   When importing the spreadsheet, each row in the CTC sheet becomes a record in the CTC table. 
   1. Copy Clause Title into Column C and Sequence into Column F
   2. Put Contract Type ID into Column A - Related Contract Type ID
   3. Modify the sequence, ensuring that it is unique for a given contract type. 
      The All Conditional System Clause sheet in the spreadsheet contains an example with the ACDBE and WMBE clauses. 

   4. Determine values for Column B - Related Clause ID
      Use this function in Column B to get the system ID for the clause: 

      =VLOOKUP(C2,ClauseLibrary!$A$2:$B$500,2,FALSE)

      Clean up all N/A values by updating the records in Column C matching the Clause Title in spreadsheet Column A. 

   5. Determine values for Column D (Usage) and Column E (Determination Method).
      1. For all CTC records that are required for the specific Contract Type use Requried for Column D and leave Column E empty.
      2. For all CTC records that are conditional and the system makes the decision based on certain values of the SOL metadata, use Conditional for Column D and System Defines for Column E.
      3. For all CTC recor ds that are conditional and the user picks clauses every time, use Conditional for Colu mn D and User Picks for Column E. 
   6. Save the Excel file.
3. Import the spreadsheet. 
   1. In the CTC table, select Actions > Import to open the Import wizard. 
   2. In the Data Source tab, select Local hard drive. 
   3. In the File Name tab, choose and upload the file. 
   4. In the Data Format tab, select Excel spreadsheet. 
   5. In the Table tab...
      1. Select CTC for the Sheet
      2.  For Table, select Contract Type Clauses
      3. Choose to Skip first row
      4. Do not create missing choice values. 
      5. Ignore "failed to convert field" errors.
      6. In Workflow/Rules select Execute workflow actions and/or guards, and Run rules after import. 
   6. In the Records tab, leave the default settings.
   7. In the Fields tab, see the following screenshot.
      
   8. Click Finish to perform the import. 
4. Update the Condition fields in the new records.
   When the import finishes, all CTC records are created in the table. For records with Usage=Conditional and Determination=System Defines, we need to update the Condition field in the CTC records. 
   1. Edit the target record in the CTC table. In the Condition field, add the relevant clauses. For more help on possible values for this field, the Formula column of the All Conditional (System) Clause tab of the spreadsheet contains examples of possible values for each clause type. Locate the associated clauses and copy the value into the Condition field. 
      
   2. Save the records. 
5. Refresh the Contract Type records.
   Note: This step is critical for the system to run. 
   1. In the Contract Type table, open each record, select the Clauses tab, and click Refresh Contract Type Clauses. 
      

Remove Clauses

1. To remove a CL record, simply change the Status value to Inactive. When this happens all associated CTC records with Auto-update=Yes will also be deactivated. 
   
2. After changing the CL status to Inactive, go into every contract type record and click the Refresh Contract Type Clauses button in the Clause tab. The system will then exclude any deactivated CTC when future blank contracts are generated.

Reactivate Clauses

To reactivate an inactive clause, change the Status value to Active. Note that associated inactive CTCs will not be reactivated automatically.

Remove CTC Records

To remove a CTC record, change the Status to Inactive. The CTC will not be included in future blank contracts, but nothing else will be affected.

Specification - Set Up and Update

This section contains implementation steps for common tasks relating to setting up and updating items, with the actions or rules that happen in each step and the purpose for each. 

Note: the Sequence column should be viewed as a numbered list. 

Table and Field Specification

This section contains a description of the tables, fields and rules that were used in this design.  

Note: If a rule level is LV1 - Major Function, then it is critical for the functionality. If a rule is LV2 - Assisting Function, then the rule is not critical for the functionality, but improves the user experience.

Clause Library

The Clause Library table is the major background table holding all the clauses for the customer.

Fields

Rules and Actions

The rules and actions are listed below:

Edit: When Clause changes

• Description - When clause changes, all associated CTC with auto-update on will get updated. 
• Rule level - LV1
• Condition - Edit, Web/API (if they are importing)
• Search - Clause changed
• Priority - 1
• Action - L: Auto-updated Linked CTC with Auto Update On. 
  • Goes down to CTC, filter to Active + Auto-Update On
    
  • Run:
    • U: Refresh Clause Library linked set
      
    • U: Pull CTC Clause from Clause Library without numbering
      

Edit: Status changed to Inactive

• Description - If status changed to Inactive, system needs to make sure there aren't any Active CTCs still linking to the Clause Library. If status changed to Active, system notifies user to reactivate the related CTC if necessary.
• Rule Level - LV2
• Condition - Edit, Web/API
• Search - R: Status changed during last user modification
• Action - I: Handle status change
  

Contract Type

The Contract Type table creates a list of contract types which each contain a selection of available clauses from the Clause Library.

Fields

Rules and Actions

The Contract Type table contains a rule and several actions which are described here.

Button - First Contract Type Clause ID

The default setting for the first Contract Type Clause ID button takes the Set First Contract Type Clause from CTC record action.

U: Set First Contract Type Clause from CTC record

Rule Level: LV1

This search has the following filters and sorting order:

Rule - Edit: Refresh CTC Sequence

• Description - LV1. Refreshes all related active CTC records, sets the Next CTC record ID in all CTC records. The Trigger Refreshment of Clause field is changed to Yes by either the edit rule in CTC table, or by pressing the Refresh Contract Type Clause button.
• Condition - Edit, Web/API
• Search - Trigger refreshment of Clause changed from No to Yes.
• Priority - 1
• Actions
  • L: Refresh Next CTC ID in all associated CTC
    
    
    • U: Set Next Contract Type Clause
      
      
      
  • U: Set Trigger CTC Refresh back to No
    • For 1/11 - Generate Template: If first is empty, or CTC status is inactive > broadcase, abandon. 

Contract Type Clauses

For a specific contract type, the sequence must be unique, so if you have two different clauses with the same sequence but different versions, you also need to make sure the sequence is different. It can be something like 3.1 and 3.2. When generating the contract, the system works like the following:

• Sequence 2 (required) – generated
• Sequence 3.1 (conditional) – check – condition not met – goes to the next directly without pushing data into SOL
• Sequence 3.2 (conditional) – check – condition met – push clause into SOL – move to sequence 4.

In addition, there are three types of CTC:

1. Required CTC
2. Conditional CTC determined by metadata in the Solicitation record
3. Conditional CTC select by user manually.

The difference between 2 and 3 is that although both have Usage=Conditional, 2 has Determination=System Defines; while 3 has Determination=User Picks.

For CTC type 3, in the Solicitation record there is a field called Conditional Clause to Include. This field holds all the CTCs that are related to the same Contract Type with Conditional and User Picks, and the user picks the ones they would like to include.

When generating the CTC, the system checks whether the Condition meets, or if the conditional clause is included in the field in Solicitation. If either of them is true, it generates the clause.

Fields

Rules and Actions

Below are the rules and actions included in the Contract Type Clauses table: 

Create: All Create Actions Web/API

• Description - LV1. This rule sets several default values as well as the Contract Type Clause - not the running clause. It just copies the CL Clause into the CTC Clause, without numbering updates, so the _num remains. 
• Condition - Create, Web/API
• Search - None
• Priority - 1
• Actions:
  • U: Set Record Created to Yes
  • U: Set Sequence Int field value
    Sequence Int=Sequence*1000
  • U: Pulls CTC from Clause Library without numbering
    

Create: All Create Actions Web Only - Trigger CT Sequence refresh

• Description - LV1. This rule goes to the related CT and triggers a refresh of the next CTC in all associated CTC records. This rule is web only, so that if the user is importing CTCs from Excel the refresh is not triggered every time, which takes a longer time to import.
• Condition - Create, Web
•  Search - None
• Priority - 2
• Actions:
  • U: Set Related Contract Type's trigger refresh of Clauses to Yes
    Contract Type Clause/Related Contract Type ID > Contract Type. Not a CTC local field, but a link to the CT record. 
    

Edit: If Sequence changed, or status changed

• Description - LV1. Handle the CTC record if the CTC parameter changes. 
• Condition - Edit, Web
• Search - Status or Sequence changed during last modification
• Priority - 3
• Actions:
  • U: Set Sequence Int field value
  • Trigger Related Contract Type to refresh the sequence
    Important: the user cannot change the related Contract Type after the CTC record is created, because there is not a field and rule to trigger a refresh of the previous contract type. 

Edit: If Clause or Related CL record changed

• Description - LV2. Handle the action if the CTC clause or the Related CL change. Web only because of the validation rule. Also prevents the Warn User about Auto-Updating action from running if the user changes the Related Clause.
• Condition - Edit, Web
• Search - CTC Clause or Related CL changes
• Priority - 4
• Actions:
  • I: Handle Clause changes 
    

Edit: Generation of the Blank Contract in Solicitation

• Description - LV1. Major action used to generate the blank contract
• Condition - Edit, Web/API
  Important: this rule runs in background, with looping protection disable. Check the two boxes under the Apply this rule section on the Rule Type tab.
• Search - Trigger Generation of Blank Contract change from No to Yes during Last Modification
• Priority - 5
• Actions:
  • I: Actions Generating Blank Contract
    
    
  • U: Set Running CTC - this rule sets the running CTC field that will be pushed to Solicitation. It replaces the _num with the Current Article Number value.
    
  • L: Push Running CTC Clause into Latest Solicitation. This action pushes the updated Running CTC value with updated numbering into the latest Solicitation ID field.
    
  • L: New: Set Next CTC Article Number to Plus1. Because the current CTC is included in the blank contract, the next article number should be increased by 1. 
    
  • U: Set Article Number to Article Number Plus 1
    
  • L: New: Set Next CTC Article Number to Same. Because the current CTC is skipped, the article number should remain the same for the next CTC. 
    
  • L: Push Latest Solicitation ID to Next CTC and Set Trigger to Yes in Next CTC. If there is a next CTC, trigger the blank contract generation of the next CTC record. 
    
  • U: Set Trigger Generation of Contract File to Yes in Solicitation. If there is no next CTC it means the current CTC is the last clause that needs to be checked. As a result, we can notify the Solicitation record to generate the file from the file content. 
    Contract Type Clause/Latest Solicitation ID > Solicitation - not local CTC field 
     

Solicitation

The Solicitation table is the main processing table where contracts are generated from the the clauses.

Fields

Rules and Actions

Below are the rules, actions and action buttons included in the Solicitation table: 

Button: Generate Contract Template

• Description - LV1 
  • If the user clicks on this button, the sytem will push the current Solicitation ID to the Solicitation > Contract Type > First CTC's latest Solicitation ID field. This initiates the blank file generation. The first CTC will push the file content to Solicitation. 
  • Also, it checks whether the next CTC ID is set. If it is, the CTC will trigger the next CTC; if empty, meaning it is the latest CTC, the process stops. 
  • The latest CTC will update the trigger in Solicitation that copies the file content into a blank print template. 
  • If the first CTC in Contract Type is null, nothing happens. 
• Action:
  • L: Clause Library - Trigger First Clause 
    

Edit: Generation of the Blank Contract File

• Description - LV1. The Trigger Generation of Blank Contract field in the Solicitation record is set to Yes by the last CTC - by the very end of the process, when all clauses are in the File Content Field. When this field is set to Yes, it triggers this rule. 
• Condition - Edit, Web/API
• Search - R: Trigger Blank File Field set from No to Yes during last modification
• Priority - 12
• Actions:
  • A: Generate Blank Contract
  • U: Set Trigger Generation of Contract File to No and Blank out file content
  • B: Notify User the Blank Contract is ready