We had a request to create output elements for Creditee ID and Name, so the first thing I did was look at the Output Set Recipe book. Lo and behold, there was an output element recipe for just what we were looking for. Or so we thought.
Use Case and Relevant Info
To give a little background, we are on v15 and are hosted. The use case for this request was to be able to output a list that could be used with creating acknowledgement letters. The current List output used by those who prepare the ack letters identifies who gave the gift, date and amount of the gift, contribution type, if the gift was in honor or memory and the address details of the contributor. What is missing is the In Honor or In Memory Creditee name/salutation and constituent ID.
We wanted to have both the Contributor and the Creditee salutations on the same line in the output. We thought that we could use the Creditee Name recipe from the Output Sets Recipe book. We followed the Creditee Name recipe and made small changes to it - specifically, we wanted only to output a specific salutation. To do this we edited the Query Element Group Data from query adding an "AND e.[signor] = 0 (Default salutation}."
When this List is run with the Output elements for the Creditee Name and ID, the output that results shows that any contribution that appears in the list has the Creditee Name and ID regardless if the contribution had a Creditee or not. Here is an example (hopefully viewable):
The example shows that the gift given for the Purpose "Florescense" is credited to Creditee No 1141265 and 1175845. However, this should only output the lines for the Rienze Garden Endowment. For some reason, this Creditee is coming through on the output to both which is incorrect.
We're curious to know if anyone else is doing this same thing and if so, how? We are filtering by gift dates - in this case the filter is a one week (7 day) filter. Both gifts were given during this time however only one should have the Creditee's listed. The Florescense does not have a Creditee.
I can provide more detail if needed. Just hoping the Tessitura hive mind has already tackled this or can explain why the gift without a creditee is being shown in the output as having a creditee.
Thanks.
Hi Phillip,
The standard CONTRIBUTION elements include creditee rows, and the output you're seeing is showing the 2 rows for each contribution in the CONTRIBUTION elements, each joined to the same CREDITEE NAME output elements. To resolve this, I recommend adding an Output Parameter in the CONTRIBUTIONS elements for Creditee Type so that we can filter the output to only the CONTRIBUTION rows that are associated with a creditee.
In TR_QUERY_ELEMENT_PARAMETER, add a parameter for
Then filter your output to CONTRIBUTION Creditee Type IN and then SELECT ALL creditee types.
Chris –
It turns out that this filter already existed (like I mentioned in the previous message) but it doesn't remove the duplicates. I had said previously that I had created a Creditee Type element within the Creditee Details element that came from the recipe book. When I created an element parameter for this and used it, ALL of the output from the elements vanishes, leaving blank fields.
So, really so far I've made no progress on this (even though I thought I had at first).
Going to pull in a resource from support to help with this.
BTW - I also found that the recipe doesn't work if a contribution has more than one creditee because of the sub-query that is used to pull the creditee type. I rewrote the "Data from" query so that it could pull all values. A simple JOIN now instead of a sub-query. Here is that query for anyone who might want to use these elements with contributions that have multiple creditees:
SELECT b.customer_no, a.creditee_no, e.esal1_desc as 'creditee', a.ref_no, b.cont_dt, ISNULL(x.creditee_type_desc,'') as 'creditee_type'FROM T_CREDITEE a JOIN VS_ELEMENTS_CONTRIBUTION b on a.ref_no = b.ref_no JOIN TX_CUST_SAL e (NOLOCK) on a.creditee_no = e.customer_no AND e.[signor] = 0 JOIN VS_ELEMENTS_CONTRIBUTION x on a.creditee_no = x.customer_no and x.ref_no = b.ref_noWHERE b.role IN (1,3) and x.ref_no = 2151247
FYI - I accidentally included the WHERE clause at the end. The proper query should be:
SELECT b.customer_no, a.creditee_no, e.esal1_desc as 'creditee', a.ref_no, b.cont_dt, ISNULL(x.creditee_type_desc,'') as 'creditee_type'FROM T_CREDITEE a JOIN VS_ELEMENTS_CONTRIBUTION b on a.ref_no = b.ref_no JOIN TX_CUST_SAL e (NOLOCK) on a.creditee_no = e.customer_noJOIN VS_ELEMENTS_CONTRIBUTION x on a.creditee_no = x.customer_no and x.ref_no = b.ref_noWHERE b.role IN (1,3)