Will you be my 2nd set of eyes on this list criteria for a donor wall?

I modified all tiers and then found my first issue. At the $250-999 level, there was a household that exceeded the level and I can't figure out why they appear on the Tier 1 list (instead of a higher tier).

This screenshot shows total giving at $2800 because there were tickets that we converted to contributions. I think this means I have to add ticket criteria to all tiers. What should I choose from the Ticketing criteria dropdown?

And it appears that paid off pledges are not appearing the the totals.

I go to the criteria search bar but get no results when I type "pledge." Hmm...

That's a weird one.  Cam you copy/paste the "show query" for the list?

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
INNER JOIN
(SELECT a1.customer_no
FROM vs_contribution_with_initiator AS a1 WITH (NOLOCK)
WHERE a1.cont_dt BETWEEN '2022/02/01' AND '2023/03/01 23:59:59.997'
GROUP BY a1.customer_no
HAVING sum(a1.cont_amt) BETWEEN 250.00 AND 999.00) AS e
ON e.customer_no = a.customer_no
WHERE a.inactive = 1
UNION
SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
INNER JOIN
(SELECT a1.customer_no
FROM VS_CONTRIBUTION_WITH_INITIATOR AS a1 WITH (NOLOCK)
WHERE a1.cont_dt BETWEEN '2022/02/01' AND '2023/03/01 23:59:59.997'
AND a1.creditee_type IN (7, 4, 1, 9)
GROUP BY a1.customer_no
HAVING sum(a1.cont_amt) BETWEEN 250.00 AND 999.00) AS e
ON e.customer_no = a.customer_no
WHERE a.inactive = 1

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
INNER JOIN
(SELECT a1.customer_no
FROM vs_contribution_with_initiator AS a1 WITH (NOLOCK)
WHERE a1.cont_dt BETWEEN '2022/02/01' AND '2023/03/01 23:59:59.997'
GROUP BY a1.customer_no
HAVING sum(a1.cont_amt) BETWEEN 250.00 AND 999.00) AS e
ON e.customer_no = a.customer_no
WHERE a.inactive = 1
UNION
SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
INNER JOIN
(SELECT a1.customer_no
FROM VS_CONTRIBUTION_WITH_INITIATOR AS a1 WITH (NOLOCK)
WHERE a1.cont_dt BETWEEN '2022/02/01' AND '2023/03/01 23:59:59.997'
AND a1.creditee_type IN (7, 4, 1, 9)
GROUP BY a1.customer_no
HAVING sum(a1.cont_amt) BETWEEN 250.00 AND 999.00) AS e
ON e.customer_no = a.customer_no
WHERE a.inactive = 1

Hi JM,

One reason that patron pulled into your list is that you are using cont_amt and they have one contribution at $800, which matches your criteria of cont_amt between 299 and 900.  Instead use Total Giving so you are looking all giving for the range instead of each separate contribution.

Hope that helps.

T.C.

Hi TC.

That's inactive on the graphical side.

I missed the sum before the const_amt, so it is totaling.  You have contributions and a separate section for soft credits.  Would one of those contributions be a soft credit?  With those in two separate sections, it won't total soft credits with other donations, so that might be why the $800 pulled in to this list.

Hi again, TC.

I believe "O" is for owner and "G" is for gift. 

Soft credits should have "Cr" for credit instead of owner.

Hi JM,

I looked at some of the list criteria and instead of two separate parts using creditee type for one, try using contribution role in just one part, with creditee, owner and owner initiator selected.  Here is how i would recreate your list criteria using roles:

Thank you. This appears right on over 100 records I've checked, so I think we'll go with it.