Calculated Fields in CiviCRM

One of CiviCRM’s strengths is the ability to add custom fields to hold specific information about your contacts. One thing it doesn’t offer (yet) is a calculated field type that will present the results of calculations of other fields.  While calculated fields are generally discouraged in relational database design, they are sometimes necessary within a user interface. One suggested method is to add custom code hooks within CiviCRM’s PHP code but as a database guy, I decided on a back-end solution.

When you add custom fields to your CiviCRM installation through the Administer >> Customize Data and Screens >> Custom Fields menu, CiviCRM adds the fields to the database by creating a new table for each set of fields. The table names are prefaced with “civicrm_value” so they’re pretty easy to find.

CiviCRM creates a new table for each custom field set defined.

CiviCRM creates a new table for each custom field set defined.

In this example, I created a small set of custom fields showing a contact’s monthly expenses such as you might use to keep budget information if that were relevant. The field set was named ‘Monthly Expenses’ and you can see the table definition above as it appears in MySQL Workbench. Most of the fields were created for direct data entry through CiviCRM except for the total_expenses_47 field which was set as View Only.

CiviCRM custom fields can be set to View Only so that they cannot be edited from within the interface.

Custom fields can be set to View Only so that they cannot be edited from within the interface.

Once you’ve created the custom field  and located it within the database, the next step is to create a database trigger that will update this field with the results from the other chosen fields whenever a contact’s information is updated through CiviCRM.

A trigger is a bit of SQL code attached to a table that fires off in response to the specified record action. You can set triggers to fire before or after record insertions, updates or deletions. These are often used to log changes within the database for tracking purposes. Triggers should not be used carelessly because too many of them can conceal the source of data changes within the system and make bugs hard to track down. Any triggers created should be well documented. The code should also be backed up and their operation verified after any update to CiviCRM.

You’ll also need to be aware of any existing triggers that CiviCRM already has in place.  In the Alter Table screen in MySQL Workbench, you can add and edit triggers under the Triggers tab.

Triggers can be easily added to tables through the Alter Table screen in MySQL Workbench.

Triggers can be easily added to tables through the Alter Table screen in MySQL Workbench.

CiviCRM uses triggers in the AFTER events of each record operation to update the date the contact record was modified and MySQL does not allow more than one trigger to fire at a time so you’ll need to use the BEFORE events

CREATE DEFINER = CURRENT_USER TRIGGER `(database name)`.`civicrm_value_monthly_expenses_6_BEFORE_INSERT` 
BEFORE INSERT ON `civicrm_value_monthly_expenses_6` FOR EACH ROW
BEGIN
 SET NEW.total_expenses_47 = NEW.rent_mortgage_43 + NEW.food_44 + NEW.clothing_45 + NEW.transportation_46;
END

In this example, I used a simple SET statement to set the value of the total_expenses_47 field to the sum of the other fields  In MySQL triggers, the OLD and NEW keywords give you access to the value of the field at different points in the process. In an INSERT triggers like this one, the NEW value is the only one available. In an UPDATE trigger, OLD would refer to the value before the update and NEW would be after the update.

Because I want this field to be updated both when the user adds information for a contact and when the existing information is updated, I add triggers for both the BEFORE INSERT and BEFORE UPDATE events. The SET statement is the same in both because I’m referencing the new values supplied at both points.

After editing each trigger, click on the Apply button to apply the new code to the table. As soon as the triggers are in place, they will start working. Of course, it’s always a good idea to do some testing to make sure things are working as desired.

As soon as the record changes are saved to the table in CiviCRM, the trigger updates the specified field.

As soon as the record changes are saved to the table in CiviCRM, the trigger updates the specified field.

Again, triggers should be used with care and well-documented, but when used correctly, they can be a quick and easy tool for storing the results you need. For more information on triggers in MySQL and their syntax, see the MySQL Reference Manual.


MySQL Explained coverNeed to know more about working with MySQL?
MySQL Explained offers a complete guide to the installation and use of MySQL and is written for people without a background in I.T. or database design. MySQL Explained takes the reader from the very basics of database theory through the installation options for MySQL and the design of custom databases based on everyday real-world examples. Find out more and get your copy on LeanPub.com and Amazon.com in paperback and a variety of electronic formats!

 


Available on Amazon.com


SanDisk Ultra II 480GB SATA III Solid State Drive (SSD)
SanDisk

Leave a Reply

Your email address will not be published. Required fields are marked *