3

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.

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?

  • Thanks Joe. I can go that route (and have), but it adds a lot of complication to the flow. – pmoz Aug 4 '16 at 1:55
  • Paul, got it. Definitely simpler to do within Civi. Look forward to a solution. – Joe McLaughlin Aug 4 '16 at 19:45
2

There's not a generic extension to handle this. 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.

There are three 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.

1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.