search

twomice / com.joineryhq.jsumfields

Provides additional fields under the Summary Fields framework.
Other
1 stars 10 forks source link

Would it be possible to save 0 instead of null for Combined contact & related contact fields? #3

Open Paul-Tahoe opened 6 years ago

Paul-Tahoe commented 6 years ago

These additional fields are great, particularly the combined ones. We've had a tough time getting one number for households and the individuals when there is giving on both levels. This is great and saves me a ton of work basically writing the same thing.

One thing my user wants to get is hh/ind that have not given in a FY or CY. For a lot of the other summary fields totals it populates a zero when the total is zero. That way you can use advanced query to query for 0 or < 1.

Yes I can use search builder or write sql but both of those are a bit too complicated for the users.

twomice commented 6 years ago

Thanks for the suggestion; this is a great idea.

In theory I expect that if other fields are doing this way then we could make this field do the same. I'll need to look into the code. If this is a time-sensitive need for you, you may want to email me about paid support options, but at present I can at least say that I agree it would be a good improvement and hope to add it when I have some "community development" time available.

BTW, you may also be interested in the Aggregate Household Contributions Report extension for other ways to look at individual/household giving.

Paul-Tahoe commented 6 years ago 
I got curious and looked at the code.

The summary fields that populate a 0 where there are no
contributions do this

select coalesce(sum(total_amount),0)

It looks like some the related ones do this

select coalesce(sum(total_amount)) 

Most of the related ones do have the ,0.  Clearly not the ones I
tried.   I'd say somewhere along the way the ,0 got dropped and then
copied to the next ones.  

The key is the ,0.  Since coalesce returns the first non zero
results from the list, the 0 is returned if the sum is null.  Really
the coalesce isn't doing anything in some of yours.  (BTW, I had to
look up how coalesce works)

Ok, I got more curious and looked at your code.

It looks like it's missing in five places in jsumfields.php
Lines 1442, 1618, 1794, 1970, 2146

I had notice the aggregate HH report extension and have wanted to
try it as well.  I'll have to.

Thanks,

Paul

On 7/11/18 8:26 AM, Allen Shaw wrote:

  Thanks for the suggestion; this is a great idea.
  In theory I expect that if other fields are doing this way then
    we could make this field do the same. I'll need to look into the
    code. If this is a time-sensitive need for you, you may want to
    email me about paid support options, but at present I can at
    least say that I agree it would be a good improvement and hope
    to add it when I have some "community development" time
    available.
  BTW, you may also be interested in the Aggregate Household Contributions
      Report extension for other ways to look at
    individual/household giving.
  —
    You are receiving this because you authored the thread.
    Reply to this email directly, view it on GitHub, or mute the thread.
  {"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/twomice/com.joineryhq.jsumfields","title":"twomice/com.joineryhq.jsumfields","subtitle":"GitHub repository","main_image_url":"https://assets-cdn.github.com/images/email/message_cards/header.png","avatar_image_url":"https://assets-cdn.github.com/images/email/message_cards/avatar.png","action":{"name":"Open in GitHub","url":"https://github.com/twomice/com.joineryhq.jsumfields"}},"updates":{"snippets":[{"icon":"PERSON","message":"@twomice in #3: Thanks for the suggestion; this is a great idea.\r\n\r\nIn theory I expect that if other fields are doing this way then we could make this field do the same. I'll need to look into the code.  If this is a time-sensitive need for you, you may want to email me about paid support options, but at present I can at least say that I agree it would be a good improvement and hope to add it when I have some \"community development\" time available.\r\n\r\nBTW, you may also be interested in the [Aggregate Household Contributions Report extension](https://github.com/twomice/me.twomice.civicrm.aggregatehouseholdcontributions) for other ways to look at individual/household giving."}],"action":{"name":"View Issue","url":"https://github.com/twomice/com.joineryhq.jsumfields/issues/3#issuecomment-404210846"}}}
  [

{ "@context": "http://schema.org", "@type": "EmailMessage", "potentialAction": { "@type": "ViewAction", "target": "https://github.com/twomice/com.joineryhq.jsumfields/issues/3#issuecomment-404210846", "url": "https://github.com/twomice/com.joineryhq.jsumfields/issues/3#issuecomment-404210846", "name": "View Issue" }, "description": "View this Issue on GitHub", "publisher": { "@type": "Organization", "name": "GitHub", "url": "https://github.com" } }, { "@type": "MessageCard", "@context": "http://schema.org/extensions", "hideOriginalBody": "false", "originator": "AF6C5A86-E920-430C-9C59-A73278B5EFEB", "title": "Re: [twomice/com.joineryhq.jsumfields] Would it be possible to save 0 instead of null for Combined contact \u0026 related contact fields? (#3)", "sections": [ { "text": "", "activityTitle": "Allen Shaw", "activityImage": "https://assets-cdn.github.com/images/email/message_cards/avatar.png", "activitySubtitle": "@twomice", "facts": [

] } ], "potentialAction": [ { "name": "Add a comment", "@type": "ActionCard", "inputs": [ { "isMultiLine": true, "@type": "TextInput", "id": "IssueComment", "isRequired": false } ], "actions": [ { "name": "Comment", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"IssueComment\",\n\"repositoryFullName\": \"twomice/com.joineryhq.jsumfields\",\n\"issueId\": 3,\n\"IssueComment\": \"{{IssueComment.value}}\"\n}" } ] }, { "name": "Close issue", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"IssueClose\",\n\"repositoryFullName\": \"twomice/com.joineryhq.jsumfields\",\n\"issueId\": 3\n}" }, { "targets": [ { "os": "default", "uri": "https://github.com/twomice/com.joineryhq.jsumfields/issues/3#issuecomment-404210846" } ], "@type": "OpenUri", "name": "View on GitHub" }, { "name": "Unsubscribe", "@type": "HttpPOST", "target": "https://api.github.com", "body": "{\n\"commandName\": \"MuteNotification\",\n\"threadId\": 353618911\n}" } ], "themeColor": "26292E" } ]

Paul-Tahoe commented 6 years ago

One other thing I've discovered. You may know about this and there is a bug on the base summary fields, https://github.com/progressivetech/net.ourpowerbase.sumfields/issues/37. That is, the initial populating with the temp tables only fires if there is a contribution for that person. So for example, two individuals, both give, household never gave, the related totals never get reported on the hh.