Count ID only once??

[raingirl]

I have a subsummary report in my gifts database that breaks down my constituents by type, alum, friend, etc. I want to be able to capture how MANY alums or friends, etc. gave based on this subsummary. I want to be able to count the constituent ID but each ID occurs several times in the gifts database and I was wondering how I would right a calculation that would only capture the Constituent ID once? I thought about trying to write a looping script but it seems with the amount of gift records I am processing that this would take a long time. Any suggestions?

Thanks,

Marcelle

[Kurt Knippel]

Basically this takes a couple of fields.

Assuming that you are talking about data similiar to the following:

ID Type

1 Friend

1 Friend

1 Friend

2 Vendor

3 Family

3 Family

4 Friend

4 Friend

5 Business

You first need to use the GetSummary or Count (Relationship::FieldName) function to determine how much of the total for each ID/Type is represented by any given record. For example there are 5 "Friend Records", but they only equal 2 Friends for your sub-summary. You'll also need a sorting or break field which will define the set that totals 1. In this case it is ID & Type.

So you'll need the following fields:

cBreakField = ID & Type

cCountFraction = 1 / Count ( Self by cBreakField::ID )

sTypeSubTotal = Total of cCountFraction

Your sorted output will look something like:

ID Type cCountFraction

5 Business 1.0

Total: 1

3 Family 0.5

3 Family 0.5

Total: 1

1 Friend 0.33

1 Friend 0.33

1 Friend 0.33

4 Friend 0.5

4 Friend 0.5

Total: 2

2 Vendor 1.0

Total: 1

[raingirl]

I understood what you did but I am having some troubling making it work right in my sub-summary (broken down by constituency). Here is what it looks like:

Constituency 2001 Dollars #who gave 2002 Dollars #who gave

Friends $$$$ ### $$$$ ###

Parents $$$$ ### $$$$ ###

etc.....

I can get the count function to work but then when I tried to say how many of those donors gave in 2001 and are alums, it doesn't work right. I tried to write several calculations like: If(Gift_date="2001", GetSummary(Sum_Count_DonorID, Constituency), ""). However, since the Summary of Sum_Count_DonorID is based on all the DonorID's, it can't separate only the ones that I want. I also tried to write it into the original calc like: If(Gift_date="2001", 1 / Count (Self_Donor::ID). I soon realized that this doesn't add up to one. I have having a real mental block with this one since there are multiple variables for the subsummary. Should I try to use a different breakfield or relationship or should I try to structure my report with a different subsummary? My $$$ calculations work well based on the constituency subsummary. You have already been a big help, any suggestions?