6 posts / 0 new
Last post
Elise Kimura Tittle
Need Help With Criteria

We have a report that will pick donors which meet certain requirements. We have a mandatory and 5 groups. When I run it all groups work except the last.

Select all Corebio where the Id from the Basic data page is greater than '0'
and where the Constituent from the Gifts page is not equal to 'Chapter'
and where the Amount from the Gifts page is greater than or equal to '1000.00'

I keep getting donors with less than $1,000.00. Part of them were pledge payments so I tried adding that to the criteria but they still pulled so I used the select expert in Crystal to remove Pledge Payments, however, I still get Gift Transactions from people who gave less.

Any help is most appreciated.  I've tried several things to no avail.

 

Glenn Blackshear
For starters, unless you

For starters, unless you have constituent IDs that begin with punctuation, including a space, I don't think "Select all Corebio where the Id from the Basic data page is greater than '0'" really accomplishes anything since characters values will sort as punctuation characters, digits ('0'-'9'), upper-case letters, then lower-case letters.  Therefore, except for IDs beginning with punctuation, ALL IDs will be greater than '0'.

Since IR processes all non-manditory groups as ORs relative to the mandatory group, logically: Mandatory AND (Group 1 OR Group 2 OR ...), I'd say that your unwanted rows (<1000) are coming from one of the other groups.

 Please copy/paste your full criteria (Mandatory + other 5 groups) to this thread.

Elise Kimura Tittle
I had thought that the

I had thought that the first criteria would reset the item.  I have been trying several different ways and decided to try it with this criteria thinking I might get a better result .Thanks again for any help

MANDATORY GROUP:
Select all Gifts where the Process Date from the Gifts page is between '' and ''
and where the Locator from the Address page is equal to 'Good'
and where the Mailcode 1 from the Address page is checked
and where the address from the Address page is 'valid this month'
GROUP A:
Select all Attribute where the Type from the Attributes page is equal to 'Trustee'
and where the Type from the Gifts page is in the list of:
'Bequest Transaction'
'Gift Transaction'
'Pledge Payment Transaction'
'Pledge Transaction'
GROUP B:
Select all Relation where the Whose from the Relation page is in the list of:
'Prospect Manager'
'Secondary Prospect Manager'
and where the Is from the Relation page is equal to '0000080226'
and where the Stop Date from the Relation page is null
and where the Type from the Gifts page is in the list of:
'Bequest Transaction'
'Gift Transaction'
'Pledge Payment Transaction'
'Pledge Transaction'
GROUP C:
Select all Addresses where the Region from the Address page is equal to 'Local area'
and where the Type from the Gifts page is in the list of:
'Bequest Transaction'
'Gift Transaction'
'Pledge Payment Transaction'
'Pledge Transaction'
GROUP D:
Select all Gifts where the Type from the Gifts page is in the list of:
'Bequest Transaction'
'Gift Transaction'
'Pledge Payment Transaction'
'Pledge Transaction'
and where the Id from the Basic data page is equal to '0000003712'
GROUP E:
Select all Corebio where the Id from the Basic data page is greater than '0'
and where the Constituent from the Gifts page is not equal to 'Chapter'
and where the Amount from the Gifts page is greater than or equal to '1000.00'
and where the Type from the Gifts page is in the list of:
'Bequest Transaction'
'Gift Transaction'
'Pledge Transaction'

g that my criteria for the gift type was messing me up. I appreciate your help with this.

I've run this group by group and when I enter the last group is when I get my unwanted amounts to people who do not fall in previous groups

Glenn Blackshear
According to your criteria,

According to your criteria, your less-than-1000 rows could be coming from any of the groups (A-D).

You're not setting any amount restrictions on the gift-table reference, only a date restriction (in Mandatory) and type restrictions in the Groups. 

In the mandatory group, include what criteria is common to ALL groups.  For instance, if ALL groups have the same gifttype restrictions (Bequest, Gift, Pledge Payment, Pledge), then put that block in the mandatory group.  If the giftamount >=1000 restriction applies to ALL groups, then put it there also.

A-D: Specifies gifttype, but no restrictions on amount, therefore your less-than-1000 rows may have a Trustee attribute, OR an active Prospect/Secondary Manger relation for 0000080226, OR and address region of 'Local area', OR a constituent ID of 0000003712.

E: Specifies type AND amount restrictions.

Since there is a variation in the gift type restrictions (E does not include Pledge Payment), then you cannot put the type restrictions in the mandatory group, but must repeat them for the optional groups, as you have them.  However, if your output should not contain ANY rows with gift amount < 1000, then I suggest moving the "Amount from the Gifts page is greater than or equal to '1000.00'" to the mandatory group so that it applies to the entire criteria.

 I hope I'm not talking in circles too much.

Elise Kimura Tittle
First of all, Thank you so

First of all, Thank you so much for taking a look at this.  I understand all of your points, however, we do want any gift with groups A-D, because those are the people we want on the report.

Group E is our "only if we get a gift for $1,000 or more do we want to see them on the list. So it is our catch all.  I thought if I put a corebio id of >0 it would reset it so then it would look again for only $1,000 or more.  So what would you suggest I do for that last criteria?

Thanks again, seems like I can never get a SAGE person to advise or find what I am looking for on this website.

 

Glenn Blackshear
As far as I know, IR

As far as I know, IR criteria processing works like this:

1. Query to a temporary table all of the records that meet the Mandatory Group criteria.  This is the base result set IR will use for the remainder of the processing.

2.  Of the records in the base set (from the mandatory group), query to a separate temp tables those records that match the respective Group (A-...) criteriae.

3.  Combine the non-mandatory Group temp tables to make the final result set.

There is nothing for a given Group to "reset."  Each is simply a query against the base (mandatory) intermediate result set.

Check the ID(s) on the <1000 rows that are coming out in your output.  I expect that you will find that they each have one of the following characteristics:

1.  There is a "Trustee" attribute regardless of the gift amount (in which case it came from Group A)

2.  There is an active (no stop date) [Primary]/Secondary Prospect Manager relation for '0000080226' regardless of the gift amount (Group B)

3.  There is an address region of 'Local area' regardless of the gift amount(Group C).

4.  The donor on the gifts was '0000003712' regardless of the gift amount(Group D).

If the gifts amount<1000 rows don't meet any of the above criteria (Groups A-D), if will really surprise me.  You also said that some of the rows in question were payments.  Since Group E doesn't allow payments or amounts < 1000, the only possibilities I see are:

1.  The records are coming from Group A-D

2.  Millennium is broken.

3.  I'm REALLY missing your point/problem.