Hi all,
I'm trying to find a way to make a consortium-friendly output element for customer_last_gift_dt. Our consortium shares a great number of constituents, and for the most part the control group scheme works well to keep contributions data segregated among the organizations.
However, the current standard output set element returns the last gift date by the constituent to ANY consortium organization, which can be confusing! For example, our development associate pulled a list that shows a donor's last gift date was 12/21/2015; however the last gift he made to our organization was over a year ago.
My question is two-fold:
1. Where is last_gift_dt, as seen in the T_CUSTOMER table, coming from?
and
2. Has anyone already written a custom output element for this and would they be willing to share the code?
Thanks and Happy New Year!
Looks like it comes for t_contribution.
You can make a view from this for list and output.
SELECT customer_no, sum(cont_amt) as last_gift , max(cont_dt) as cont_dt
FROM t_contribution a WHERE a.cont_dt = (SELECT max(cont_dt) FROM t_contribution b WHERE a.customer_no = b.customer_no)GROUP BY a.customer_no
Travis
Normal 0 false false false EN-US X-NONE X-NONE
Stacey,
We have some that work well that cover first gift, most recent pledge and most recent gift (both the dates of those and the amounts), but they require creating several views in the database.
If that is something that can be done for you, try the documents that are on my profile for creating the views, and I can let you know the code for the output elements if you need that. I think I either copied or adapted it all either from the cookbook or from someone else who gave it to me, so I do not want to claim much of the credit for these.
Creating views is the best way to accomplish this task in my mind, and if you are in a consortium environment, views and controls groups should be your very good friends (or very worst enemies...). There are probably other options out there, too.
John
Thank you all!
I've had good success with views in the past; I think I was having a mental block on this one because of the column coming from T_CUSTOMER and not a contributions table. Or, just possibly, because it's that "lost week" of the year :)
I think my views are the expended, less efficient version of Tom's way, now that I look at it. Either way, they should work.
I think I understand your question. You might find this useful.
Try adding this in TR_Query_Element
ID whatever is next
Description Contribution Last Gift date
Data Select max(cont_dt) -- This finds the latest date
Data From vs_contribution -- Using a secure view should only show the contributions you are allowed to see as a member.
Data Where cont_amt>0 -- This gets rid of written off contributions
Control Group Whatever is your standard on this
Single Row [Checked]
Primary Group Default Whatever you are using else where
The restrictions to just a consortium member should be controlled by the “secure view” vs_contribution. (You can recognize a secure view by the “S” before the “_” in the view name. Remember to login as a member of a consortium member not system administrator to see the restrictions in place.
Hope that helps.
--Tom
…
718.724.8135
tbrown@BAM.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Stacey VoigtSent: Wednesday, December 30, 2015 2:33 PMTo: Thomas Brown <tbrown@bam.org>Subject: [Tessitura Technical Forum] Last_gift_dt in T_CUSTOMER
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
VS_Contribution should exist in your system already.
You might want to use the newer “vs_contribution_with_initiator” that is now being used in T_Keyword.
And you could add a record to T_Keyword to search for this criteria as well. In that case I would strongly suggest using vs_contribution_with_initiator rather than vs_contribution.
From: Thomas Brown Sent: Wednesday, December 30, 2015 3:23 PMTo: 'Tessitura Technical Forum' <forums-technical@tessituranetwork.com>Subject: RE: [Tessitura Technical Forum] Last_gift_dt in T_CUSTOMER