Calem Integration 3: Custom Triggers

Calem Enterprise comes with integration capabilities including:

• Email Integration: service request submission, service request and work order interaction via emails.
• Application Programming Interface (API): the APIs to read, update and delete records in Calem. It is ideal for third party applications to integrate with Calem via APIs.
• Custom Triggers: application code in Calem server. The triggers are fired when database records are inserted, updated or deleted. 

Custom triggers are discussed in this blog. They can be used to perform business logic required in customers' businesses. For instance, a customer likes to display vendor contract information in purchase orders. The requirement is that if a vendor has a contract with the customer, the contract's start date should be listed. Here are the steps to implement the feature with the help of customer triggers in Calem:

• The customer added two custom fields in purchase order table: "Has Contract?" and "Contract Date". If "Has Contract?" is true, the "Contract Date" should have a valid start date. This step is done through Calem Data Designer at menu: Admin module | Data Design | Tables.
• The customer then customizes the purchase order forms including new, edit and read forms to add the two custom fields. This is done through Calem Designer from Admin module | Group list | Group Design (for a selected group).
• The last step is to enforce the business logic. If a user checked "Has Contract?" and didn't set a valid "Contract Date", Calem should display a dialog and tells the user to set a valid date. The user has to set a valid date to save the changes, or have the "Has Contract?" unchecked. This can be achieved through a custom trigger to be discussed below.

The screenshot with two custom fields added in PO: 

The PO creation form is customized to have the two custom fields: "Has Contract?" and "Contract Date".

Customers need to white list their IPs with Calem for access to their Cloud services to experiment and deploy custom triggers. Programming skills of PHP are required to develop custom triggers. There are plenty of triggers shipped in Calem Enterprise in plain PHP scripts. They can be used as examples.

The following triggers are supported in Calem:

Triggers are seeded in Calem for many tables. Customers can add custom triggers which are fired after the seeded triggers.

Customer triggers are configured in your server configuration file at Calem_Home/server/conf/calem.custom.php. For instance, the following defines a custom trigger for purchase order table:

$_CALEM_dist['dbo_conf']['cm_po']='CmPoDbo_Ct';

Here are the rules about the custom triggers:

• "CmPoDbo_Ct" is a database object (DBO) for table "cm_po" which is the purchase order table.
• The custom trigger ends with "_Ct" indicating custom triggers.
• The custom trigger file "CmPoTrigger_Ct.php" should be placed in Calem_Home/custom/dev/dbo/CmPoDbo_Ct.php

Here is the sample trigger source code:

• The "Has Contract?" and "Contract Date" fields are checked in before insertion trigger (line 21 to line 30) and before update trigger (line 48 to 56). If a valid contract date is not filled while the "Has Contract?" is checked, the trigger raised a custom error "A valid contract date is required".

1     
2    /** 
3     *  Copyright (c) 2006-Present 
4     *  CalemEAM Inc. All Rights Reserved. 
5     *  The contents of this file are confidential and proprietary information of CalemEAM Inc. 
6     *  You shall use the file only in accordance with the terms of the CalemEAM Software License Agreement. 
7     */ 
8     
9    //Checking basic initialization 
10   if (!defined('_CALEM_DIR_')) die("Access denied at ".__FILE__); 
11    
12   require_once _CALEM_DIR_ . 'server/core/database/CmDbo.php'; 
13    
14   class CmPoDbo_Ct extends CmDbo { 
15    
16      // before insert 
17      // $baseTable base table name 
18      // $baseData an array of key/value data to be inserted 
19      // $customTable custom table name 
20      // $customData an array of custom key/value data 
21      public function beforeInsert($baseTable, $baseData, $customTable, $customData) { 
22         //Throw an exception if PO is closed. 
23         $hasContract= ($customData ? $customData['has_contract'] : null); 
24         $contractDate = ($customData ? $customData['contract_date'] : null); 
25         if ($hasContract && !$contractDate) { 
26            $this->onSvrExceptionByMsg('A valid contract date is required.'); 
27         } 
28         //You can update $baseData by filling more info 
29         return $baseData; 
30      } 
31    
32      // after insert 
33      // $id the record id for created record 
34      // $baseTable base table name 
35      // $baseData an array of key/value data inserted 
36      // $customTable custom table name 
37      // $customData an array of custom key/value data 
38      // $batch batch process 
39      // $sync offline sync 
40      public function onDataInserted($id, $baseTable, $baseData, $customTable, $customData, $batch=false, $sync=false) { 
41         //You may update other related tables after data is inserted. 
42      } 
43    
44      // before update 
45      // $baseTable base table name 
46      // $baseCurrent an array of current record key/value data 
47      // $baseUpdate an array of key/value data updated 
48      public function beforeUpdate2($baseTable, $baseCurrent, $baseUpdate, $customTable, $customCurrent, $customUpdate) { 
49         // You may add/update fields to be updated if needed. 
50         $hasContract=$this->getValueByUpd('has_contract', $customUpdate, $customCurrent); 
51         $contractDate=$this->getValueByUpd('contract_date', $customUpdate, $customCurrent); 
52         if ($hasContract && !$contractDate) { 
53            $this->onSvrExceptionByMsg('A valid contract date is required.'); 
54         } 
55         return $baseUpdate; 
56      } 
57    
58      // after update 
59      // $baseTable base table name 
60      // $baseCurrent an array of current record data 
61      // $baseUpdate an array of fields to be updated 
62      // $customTable custom table name 
63      // $customCurrent an array of current record data 
64      // $customUpdate an array of custom key/value data to be updated 
65      // $batch batch process 
66      // $sync offline sync 
67      public function onDataUpdated($baseTable, $baseCurrent, $baseUpdate, $customTable, $customCurrent, $customUpdate, $batch=false, $sync=false) { 
68         //You may update other related tables after data is updated. 
69      } 
70       
71      // before delete 
72      public function beforeDelete() { 
73         //Load the data row for use in post deletion 
74         $this->loadRecord(); 
75      } 
76       
77      // after delete 
78      // @table the table name 
79      // @id the id of the record deleted 
80      // $batch batch process 
81      // $sync offline sync 
82      public function onDataDeleted($table, $id, $batch=false, $sync=false) { 
83         //You may reference the deleted row as "$this->row" and process it. 
84      } 
85    
86   } 
87   

Additional resources

• Calem Enterprise Customization
• Custom Fields and Forms for User Skills
• List View Customization
• User Guide and Admin Guide (customer account required)
• Calem Enterprise Training Site
  
• Calem Enterprise Blogs
  
• Calem Enterprise demo