search

progressivetech / net.ourpowerbase.sumfields

The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.
Other
8 stars 29 forks source link

"Count of Contributions" including line items as Contributions #69

Closed alifrumin closed 4 years ago

alifrumin commented 4 years ago

Using the most recent version of master for net.ourpowerbase.sumfields and CiviCRM 5.19.2

we are seeing the Count of Contributions include line items as contributions

Screenshot of The Summary Fields tab that shows the Count of Contributions as 3 AND the Contributions tab as 1: count3contrib1

Screenshot of the one contribution with three line items threeLineitems

jmcclelland commented 4 years ago

Thanks for the report! It looks like I was missing a "DISTINCT" in that query. I just pushed a commit to master. Can you try it out and let us know if that solves the problem?

Here's the diff if you want to do it by hand:

diff --git a/custom.php b/custom.php
index 5633191..f51aed0 100644
--- a/custom.php
+++ b/custom.php
@@ -418,7 +418,7 @@ $custom = array(
       'html_type' => 'Text',
       'weight' => '45',
       'text_length' => '32',
-      'trigger_sql' => '(SELECT COALESCE(COUNT(t1.id), 0) FROM civicrm_contribution t1
+      'trigger_sql' => '(SELECT COALESCE(COUNT(DISTINCT t1.id), 0) FROM civicrm_contribution t1
       JOIN civicrm_line_item t2 ON t1.id = t2.contribution_id
       WHERE t1.contact_id = (SELECT contact_id FROM civicrm_contribution cc WHERE cc.id = NEW.contribution_id) AND t1.contribution_status_id = 1 AND
       t2.financial_type_id IN (%financial_type_ids) AND t1.is_test = 0)',
alifrumin commented 4 years ago

Yes!! This fixed things for us. THANK YOU