Hello everyone,
I have just run an output set that includes the Last Donation Date element. The last dates that are appearing in the set results are June 2008 even though Constituents in the list have contributed since then. Is there a procedure or table that needs to be updated?
In TR_QUERY_ELEMENT:
Data Select - !.lgpfdt
Data From - lv_conv_last_gift_hist
Thanking you in advance,
Duane
Since your query element is coming from a local view, I'd check that to make sure the view itself is not in some way limited to a specific fyear or daterange.
Thanks Amanda. I'll check into that.
Hello Amanda,
Here is the code in this view:
CREATE VIEW lv_conv_last_gift_hist (customer_no,LGPFDT,LGPLAM) AS SELECT DISTINCT c.customer_no, d.LGPFDT, d.LGPLAMFROM (SELECT MAX(LGPFDT) AS donation_dt, ATPATN FROM dbo.cw_donation GROUP BY ATPATN) AS b INNER JOIN dbo.cw_donation AS d ON b.ATPATN = d.ATPATN AND b.donation_dt = d.LGPFDT INNER JOIN dbo.T_CUSTOMER AS c ON d.ATPATN = c.customer_noWHERE (CONVERT(decimal, d.LGPLAM) > 0)
Pardon my ignorance in this regard, but do you see anything here that needs to be updated to reflect current dates?
Duane, I’m not sure exactly without knowing more about the system, but cw_donation is not a standard Tessitura database table, so it may be pulling data from old conversion history. This probably explains why you’re not seeing more current information.
The source table for at the moment contribution data would be T_CONTRIBUTION. Most elements would be using VS_CONTRIBUTION (control group security) in the table TR_QUERY_ELEMENT.
You’ll probably have to construct a local view, however, to handle the logic of last gift, prior to using the columns for output sets. I may be a bit off, but I believe Sandra’s T-Cast on Output Sets and corresponding recipe book might have the info you need for this tasty little treat!
Point the old browseroo to this little nugget of gold:
http://www.tessituranetwork.com/network/Learning/Webinars/Webinars%20Archive/IT/Intro%20to%20Output%20Set%20Builder%20T-Cast.aspx
J
-Ryan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Duane Woods Sent: Friday, April 16, 2010 10:07 AM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] Last Donation Date - output set element
CREATE VIEW lv_conv_last_gift_hist (customer_no,LGPFDT,LGPLAM) AS SELECT DISTINCT c.customer_no, d.LGPFDT, d.LGPLAM FROM (SELECT MAX(LGPFDT) AS donation_dt, ATPATN FROM dbo.cw_donation GROUP BY ATPATN) AS b INNER JOIN dbo.cw_donation AS d ON b.ATPATN = d.ATPATN AND b.donation_dt = d.LGPFDT INNER JOIN dbo.T_CUSTOMER AS c ON d.ATPATN = c.customer_no WHERE (CONVERT(decimal, d.LGPLAM) > 0)
From: Amanda Freeman <bounce-amandafreeman5080@tessituranetwork.com> Sent: 4/16/2010 11:48:43 AM
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!
I might be mistaken, but I thought everything starting with "c" was a conversion table, and your view appears to be pointed at that rather than what I'd expect to see something like vs_contribution. So I'm guessing it's only dealing with the conversion data and not anything that's happened live since... But I've not familiar with that cw_donation in your from, so I'm not sure what's really going on there.
Hello Ryan,
Thanks for this. I will indeed check out the Recipe Book.
And it sounds like you would be correct regarding cw_donation for pulling pre-Tessitura inputted data, as this was built during our implementation two years ago, which hmm seems pretty coincidental with the most current date appearing on the output set being June 2008.
Hi Duane!
If all you're looking for is most recent gift date, try this:
DATA SELECT: MAX(cont_dt)
DATA FROM: vs_contribution
That might be a bit easier than recreating or editing your view.
Nick
Thanks for this Nick. That did the trick in this situation.