Gift queries, when some gifts include adjustments

Joe Erjavec

I've created many gift queries for our finance team.  Most of them, such as a monthly gift query, work as expected.  However, I recently entered a gift which later in the month needed adjustment.  The total dollar amount was kept the same, but 50% remained designated towards the original fund, while the other 50% was designated to a different fund.  When the monthly gift query was run, the gift appeared twice in our query, with the original gift amount, but each entry pointing towards the two different funds.

I would appreciate recommendations for how to structure my queries that would account for these types of duplicates.  (Adjustments like this are rare instances for us.)

Thank you.

 

Angela Grijalva

Joe Erjavec:

I've created many gift queries for our finance team.  Most of them, such as a monthly gift query, work as expected.  However, I recently entered a gift which later in the month needed adjustment.  The total dollar amount was kept the same, but 50% remained designated towards the original fund, while the other 50% was designated to a different fund.  When the monthly gift query was run, the gift appeared twice in our query, with the original gift amount, but each entry pointing towards the two different funds.

I would appreciate recommendations for how to structure my queries that would account for these types of duplicates.  (Adjustments like this are rare instances for us.)

Thank you.

 

I believe you could use one of the split amount fields (fund split amount, appeal split amount) to report how much went to each fund/appeal rather than the gift amount field which reports the total gift amount.

Melissa Graves 2

Joe Erjavec:

I've created many gift queries for our finance team.  Most of them, such as a monthly gift query, work as expected.  However, I recently entered a gift which later in the month needed adjustment.  The total dollar amount was kept the same, but 50% remained designated towards the original fund, while the other 50% was designated to a different fund.  When the monthly gift query was run, the gift appeared twice in our query, with the original gift amount, but each entry pointing towards the two different funds.

I would appreciate recommendations for how to structure my queries that would account for these types of duplicates.  (Adjustments like this are rare instances for us.)

Thank you.

 

I do not think this has anything to do with adjustments. It is the way a split gift appears in a query output. You must also rarely split gifts.

A) Query is a grouping tool - it is not a report. Anyone looking at this report needs to know this. If you need a report for your finance team - do not use query. What is this gift query used for? There are some really great financial reports which are probably better than this for what you need.

Query is showing what you asked. It just does not handle one to many fields well. It is showing you the [full] gift amount (that is the field you put into your output) and the fund - because there are two funds - it creates two rows and puts the same full amount in each row. If you need to see this properly in query, you need to use one of the split amount fields. If you split between funds, then you can use fund split amount to show only the amount. In the future if you split between campaigns or appeals you may need to use those split amount fields.

Joe Erjavec

Melissa Graves:

I do not think this has anything to do with adjustments. It is the way a split gift appears in a query output. You must also rarely split gifts.

A) Query is a grouping tool - it is not a report. Anyone looking at this report needs to know this. If you need a report for your finance team - do not use query. What is this gift query used for? There are some really great financial reports which are probably better than this for what you need.

Query is showing what you asked. It just does not handle one to many fields well. It is showing you the [full] gift amount (that is the field you put into your output) and the fund - because there are two funds - it creates two rows and puts the same full amount in each row. If you need to see this properly in query, you need to use one of the split amount fields. If you split between funds, then you can use fund split amount to show only the amount. In the future if you split between campaigns or appeals you may need to use those split amount fields.

Thank you both for your responses.  Although I understand that querying is a grouping tool rather than a report, I still would prefer to use queries.  With that stated, as query doesn't handle one-to-many fields well, I will create a separate gift next time that there is an adjustment like this. 

Melissa Graves 2

Joe Erjavec:

Thank you both for your responses.  Although I understand that querying is a grouping tool rather than a report, I still would prefer to use queries.  With that stated, as query doesn't handle one-to-many fields well, I will create a separate gift next time that there is an adjustment like this. 

That is a common practice. However, I say again that this has nothing to do with the adjustment and you will need to make this a policy any time you have a plit gift to more than one fund (regardless of whether or not it was adjusted). Many of us avoid splitting gifts.