General
What this extension does is to do calculations based on user-defined MySQL queries and then store the results on user-defined customfields attached to a contact. Those customfields have no logic on their own and act as value placeholders only. As they are CiviCRM customfields, they are accessible via reports and searches.
The idea is similar (but not identical) to the SummaryFields extension. As with SummaryFields, you can define dynamically created CustomFields through the UI for contacts along with their associated MySQL query that will be calculating their values. The main difference between this extension and SummaryFields is the fact that SummaryFields is following a different logic (contribution-centered) while Synopsis is using a contact-centered approach.
Another similar (up to a point) module is the SQL Tasks Extension although it doesn't serve the same purpose. See the Similar or related extensions that are worth mentioning
Features
- Add/remove on-the-fly new fields via the UI
- Can use Optionvalues via selector data-types as displaying results (for example First Contribution Campaign)
- Can refresh contact stats for a specific contact (via button in the UI or via the API) on request
- Can import an initial configuration via the JSON importer (3 sample configurations are included in this extension)
- Created fields are also accessible via the advanced search and on reports instantly (as they are native CiviCRM customfields).
- You can repurpose an existing customfield (will act as a calculated field)
Comes with the following available fields/statistics:
- Total Lifetime Contributions
- Total Contributions in the Last 12 Months
- Total Contributions this Fiscal Year
- Total Deductible Contributions this Fiscal Year
- Total Contributions last Fiscal Year
- Total Deductible Contributions last Fiscal Year
- Total Contributions Fiscal Year Before Last
- Total Deductible Contributions Fiscal Year Before Last
- Amount of first contribution
- Date of First Contribution
- Campaign of first contribution
- Amount of last contribution
- Date of Last Contribution
- Campaign of last contribution
- Largest Contribution
- Total count of contributions
- Count of contributions this year
- Count of contributions last year
- Count of contributions year before last year
- Average of total Contributions
- Average Annual (Calendar Year) Contribution
- Campaign of largest contribution
- Date of Last Membership payment
- Amount of last Membership payment
Important information
As this extension uses MySQL snippets that are user-configurable and as there is no filter, it is highly recommended to be configured by an administrator. It is quite easy to break your system if you don't know what you're doing !
The extension is licensed under AGPL-3.0.
Screenshots
Field configuration
Configuration importer
Token configuration
Requirements
- PHP v7+
- CiviCRM 5.x
Installation (Web UI)
This extension has not yet been published for installation via the web UI.
Installation (CLI, Git)
Sysadmins and developers may clone the Git repo for this extension and install it with the command-line tool cv.
git clone https://lab.civicrm.org/extensions/synopsis.git
cv en synopsis
Usage
Upon a successful installation of this extension, go to the permissions page and assign the proper permissions to administer the settings. Once done, go to "Administer" > "Synopsis fields" to declare your field configuration.
Prebuilt configurations
If you get overwhelmed by the configuration settings, there is an alternative: you can import one of the three(3) initial configurations that can be seen here and are being described on the table above (Features):
by navigating to /civicrm/admin/settings/synopsis/configimport. Once imported, page will redirect back to the Synopsis Fields. Save the form and you're done!
Configuration translations
The above configurations are in English language.
If you would like to have them in different languages, translate the labels of the source JSON files or try the already translated configurations in Spanish or Catalan located under the folder /resources/configuration of this git repository.
Buttons explained
- CustomField ID is the internal numeric ID that is being given when a CustomField is being created. Initially, this field is 0 but as soon as the customfield is being created, it will inherit the actual ID that is being stored in the DB. This field is disabled from editing.
- Machine name is the internal name that is being given to a CustomField. Initially, this field is empty but as soon as the CustomField is being created, it will show up the machine name. This field is disabled from editing.
- Label is the label of your CustomField.
- DataType is the type of CustomField you want to create/use.
- Select Reference works only if you have picked up 'Selector' as Datatype above. This field will be ignored if datatype is not of type 'Selector'.
- Existing field repurposes already existing fields to store the calculated value(s). Please see notes below.
- Query The MySQL query that will be used to calculate this field.
- Entity If you are using the contribution table, use 'Fundraising'. If this relates to memberships, use 'Membership'.
- Remove By checking this checkbox and saving the form, this specific entry (and associated CustomField) will be completely removed from the system. Be careful as there is no way to restore the data!
- Hide Renders the customfield as invisible to the user. Note: Setting this will not delete the customfield, it will hide it.
Queries
When writing your SQL queries, always remember to add the token of contact_id by adding {contact_id} to your query.
While you might be having many customfields appearing, until you save the form, nothing is actually being stored in the database (unless done previously). Saving the form will create (or remove, if selected) the fields into CiviCRM as customfields and with the properties/queries defined (the only exception is if using the 'existing fields' column)
Further tokens include:
- {financial_types} : Financial type IDs to include. Configurable under:
/civicrm/admin/settings/synopsis/config - {current_fiscal_year_begin}
- {current_fiscal_year_end}
- {last_fiscal_year_begin}
- {last_fiscal_year_end}
- {year_before_last_fiscal_year_begin}
- {year_before_last_fiscal_year_end}
Additional options
You can save your custom configuration by clicking the button "Expose JSON configuration" (as seen below) on the synopsis configuration page (under /civicrm/admin/settings/synopsis)
Copy and paste the contents of the newly shown textarea into a notepad and save it as a JSON file to re-use it on another instance.
Please note that the columns CFID & column_name will be removed from the exportable JSON file, in order to avoid potential issues on any future imports, as they are being considered to be site-specific.
Contact merging
When a user is trying to merge 2 contacts, it is somewhat pointless to render the Synopsis fields of both contacts during the merge screen as the new merged contact will have his/her fields recalculated.
That's why we have included an option under /civicrm/admin/settings/synopsis/config to be able to remove the Synopsis fields from the contact merge screen. However, as the label states "EXPERIMENTAL", there are some caveats:
If any of the Synopsis field's label is containing any sort of Parenthesis or brackets, this option might produce a WSOD (White screen of death) during merge. Please disable the option if you face such an issue.
Notes on option 'existing fields'
What's the reason for it?
There are many instances where we wanted to install the extension and replace an existing set of customfields. Normally, we would simply remove/delete the old customfields and add our own via this extension. However, this approach has a huge drawback: If the old customfields where already being used in saved smartgroups, those smartgroups will be broken when the old customfields are being removed.
Here is where the 'existing_fields' gets handy: By implementing this approach of the 'existing fields', we are in fact repurposing the old customfields for our own benefits.
When the extension processes a field that has prepopulated the 'existing field' option, no customfield will be created, the extension will re-use that specific customfield instead of recreating it, calculate the value and store it.
API
Synopsis is coming with its own APIv3.
As there is an API along with the extension, you can also call it via drush.
Drush example:
drush cvapi Synopsis.calculate
PHP Example:
$result = civicrm_api3('Synopsis', 'calculate');
If you add the parameter contact_id=<contact_id> to the API call above, it will calculate only for that contact ID supplied. contact_id needs to be an integer. Currently and array is NOT being accepted.
Drush example:
drush cvapi Synopsis.calculate contact_id=1234
PHP Example:
$result = civicrm_api3('Synopsis', 'calculate', [
'contact_id' => <contact_id>,
]);
If you want to debug the query, add the parameter debug_only=1. Debugging will show the full query set being executed but nothing will be stored in the database. Note that this command will output the data in the console.
Example:
drush cvapi Synopsis.calculate contact_id=1234 debug_only=1
Scheduled jobs
When installing this extension, a new scheduled job will be created that will be executed hourly (configurable by the user as usual)
Known Issues / Todo list
Always, just follow: https://lab.civicrm.org/extensions/synopsis/issues
Frequently asked questions
Find the most current FAQ here: https://lab.civicrm.org/extensions/synopsis/-/wikis/FAQ
Sharing MySQL Snippets/ideas
Feel free to share and/or suggest additions or fixes in the supplied MySQL snippets. If they can be reused, we would love to include them!
Technical information
If you want to know where to write more queries and how to structure them, read here
Credits
Many thanks to Jamie McClelland and his excellent SummaryFields extension (Git repo link). A part of his source code and plenty of his ideas have being implemented into this extension.
Also many thanks to Bjorn Endres from Systopia for showing me the JSONEditor library implementation through CiviBanking ;)
Special thanks also goes to:
- Artfulrobot for the improvements and MRs
- MattWire for the improvements and MRs
- ananelson for the improvement suggestions
- marti_ixiam for the Spanish and Catalan translations
Similar or related extensions that are worth mentioning
-
SummaryFields extension - Summary Fields make it easier to search for major donors, recent donors, lapsed donors as well as to show a synopsis of a donor’s history. Summary Fields extends your CiviCRM data by creating a tab of fields that total up and summarize donation history.
-
SQL Tasks Extension - Configurable recurring tasks - The "SQL Tasks" extension allows you simply configure any number of scheduled jobs via the UI. It is essentially a SQL driven, configurable execution of any of the following generic CiviCRM actions: Create activities, Call any CiviCRM API3 action, Export to CSV, including zipping, uploading, emailing the results, Synchronize a SQL result contact ID list with a given tag or group and more ..
Support and Maintenance
This extension is supported and maintained by
