Is there an extension for adding a custom field type calculated field?

Question

Civi 4.6.9
Drupal 7.44

On many occasions it would be very handy to calculate the values of custom field sets and store that calculated value within Civi.

I'll use a survey for example.

I'd like to create a scored survey activity type tied to a custom field set. The field set would include multiple fields, each with select options. The final field within the set would be "Score." I'd like this field to add the values of all answers. Once we calculate the survey score we can do all kinds of fancy stuff.

I've done my due diligence but didn't find anything out there.

If there isn't one, is the extension route the most efficient method?

Ideally, I'd love to have "Calculated Field" be an option for data type when creating custom fields but that may be a pipe dream.

Thanks.

Answer 1

[UPDATED FOR 2023]

The closest equivalent is using SearchKit. You can use either Data Segmentation or text rewrites with Smarty.

At the bottom of this answer is a screenshot of a Data Segment to calculate the donor level based on amount donated. There's also a screenshot of using text rewrites to calculate the amount of time elapsed in minutes between two date/time custom fields.

What you're asking for is very similar to Computed Field for Drupal - but the nature of calculated fields is that you're writing code (or some pseudo-code formula). At that point, it's not much further to writing the code in an extension.

Furthermore, there are basic transformations available on SearchKit without rewrites (e.g. concatenate text, sum numbers).

The limitation of the approaches above is that they're only available in SearchKit. If you'd like them available as a custom field/token, there are three extensions you can investigate - Summary Fields, Synopsis and SQL Tasks.

All three extensions rely on writing SQL. Summary Fields has some options for using triggers, but is much harder to use, and you have to build a sub-extension. Synopsis and SQL Tasks let you write your SQL inside a UI. Synopsis is built for the purpose and is a better experience, but SQL Tasks can also be used in many instances.

[2016 answer] There are several approaches you can take:

You can write an extension and use hook_civicrm_pre to grab the fields in question and write to the custom field when your survey is created/updated. This is relatively quick and easy.

You can write a MySQL trigger in your database. This is good if you need very high performance. However, you will need to re-add your trigger on every upgrade - unless you put it in an extension.

Data Segmentation

Text Rewrites

Answer 2

Paul, very interested in this idea as well. Don't have a great answer, but here's an idea - since you're on Drupal, I wonder if this would work - using Views, Views Calc and Views Aggregator Plus (perhaps along with webform-civicrm) to do the calculation - add the survey answers to get a score - then saving that score back into Civi in a custom field?

Answer 3

Would it be useful to have a general extension that would allow arbitrary PHP to be associated with a View only custom field, and executed on save of the associated object, eg contact or contribution. The extension would provide a new permission that by default would be turned off for all roles. As a vector for security vulnerabilities and performance problems, it should be available only to sysadmin type users. https://www.drupal.org/documentation/modules/php

An alternative design that would have reduced usefulness, cost more to develop, yet be safer and less subject to performance concerns would make it possible to do specific arithmetic or string operations on the core or custom fields of the object associated with the view only field. Like tokens, this could include access to the core and custom fields of the associated contact if the object was something like a contribution or event registration or activity.

Answer 4

I had the impression that the Summary Fields extension would be a good starting point for adding additional calculated fields. To quote

This extension provides hook_civicrm_sumfields_definitions which allows you to add additional summary fields of your own. Example:

/**
 * Implements hook_civicrm_sumfields_definitions()
 *
 * Change "mycustom" to the name of your own extension.
 */
function mycustom_civicrm_sumfields_definitions(&$custom) {
  $custom['fields']['hard_and_soft'] = array(
    'label' => 'All contributions + soft credits',
    'data_type' => 'Money',
    'html_type' => 'Text',
    'weight' => '15',
    'text_length' => '32',
    'trigger_sql' => '(
      SELECT COALESCE(SUM(cont1.total_amount), 0)
      FROM civicrm_contribution cont1
      LEFT JOIN civicrm_contribution_soft soft
        ON soft.contribution_id = cont1.id
      WHERE (cont1.contact_id = NEW.contact_id OR soft.contact_id = NEW.contact_id)
        AND cont1.contribution_status_id = 1 AND cont1.financial_type_id IN (%financial_type_ids)
      )',
    'trigger_table' => 'civicrm_contribution',
    'optgroup' => 'mycustom', // could just add this to the existing "fundraising" optgroup
  );
  // If we don't want to add our fields to the existing optgroups or fieldsets on the admin form, we can make new ones
  $custom['optgroups']['mycustom'] = array(
    'title' => 'My group of checkboxes',
    'fieldset' => 'Custom summary fields', // Could add this to an existing fieldset by naming it here
    'component' => 'CiviContribute',
  );
}

Also seems a fairly solid recipe here