Multiple lines for adjustments in query

Emily Harvie

Hello,


Our shop runs all revenue through the BBCRM post-process, including payments and pledges. However, this causes a large number of adjustments, as any changes now get recorded and reposted with an adjustment reason code. 


There are complications when we try to report on revenue that includes adjusted and non-adjusted revenue. Basically, the revenue information will display twice in the output- one row without an adjustment reason, then one row for each adjustment that generated. This causes problems for our larger reports, where it becomes tedious to manually go through and remove the duplicate lines. We need to remove duplicate lines because this could cause double-counting if the revenue amount did not change with the adjustment. 


Example: The below donor only gave $100 one time and requested a change in designation. After adjusting and reposting, the gift would pull in a report like this. This makes it look like they gave $200 if you sum up only the "Amount" field: Amount  Date Adjustment Date Adjustment Reason $100.00 Jan 10, 2020     $100.00 Jan 10, 2020 Feb 09, 2020 Donor requested change to the designation.

Donor Name
Emily Harvie
Emily Harvie

I'm reaching out today to see if anyone else has encountered this problem, and if so how you worked around it? Any suggestions are welcomed.


Thank you!

Em

Lois Smith

Are you obtaining this information from a query or a canned report?  When querying for revenue, we do not put in the adjustment date.  The query itself will give us the most up to date information.  We only include adjustment information if we are specifically looking at adjustments.  


Here, when we run reports based on revenue, we want to know what the current application and amount of the pledge or payment is.  We just dig down into adjustments if there is a problem with balancing or something like that.

Emily Harvie

Thank you Lois! This is a query. I agree with you, but our finance team really wants to see the most recent adjustment date or some other flag that an adjustment has taken place. I'm hoping we can meet their needs without having the duplicate lines.

Lori Thompson

Hi Emily,


Have you tried removing the Adjustment Reason field and doing MAX(Adjustment Date)?  Hopefully that will give you blanks where there is no Adjustment Date and show the most recent Adjustment Date otherwise.


Best of luck!


Lori

Lois Smith

If you just need a yes/no on the adjustment, create a selection of revenue that has been adjusted and then use that selection as an output field in the query.  Would that work?

Emily Harvie

We will try both methods and see which works best in the current query. Thank you both so much!