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.

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.

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.

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.

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.