I am building a custom report using SQL to better report on the finances around our contributions. Is there an easy identifier between T_CONTRIBUTION and gl_account_no other than using the T_FUND and then using a CASE statement to select the correct GL Code from there based on other factors?
Hi Robert,
I may be wrong (someone feel free to correct me!) but I believe the Fund associated with the Contribution is the only link for GLs.
Martin
Yep the contribution GLs are fund based ... there can be 5 per GL (future, current, restricted etc)
Ah, that's what I was afraid of. So basically, I just need to figure out the "rules" (when a contribution should be assigned each one) and employ that logic in the SQL code itself so it assigns the correct GL code to each contribution.
Martin Keen (Past Member) said:I believe the Fund associated with the Contribution is the only link for GLs.
Yes. Can't we get to the exact GL via T_TRANSACTION -> Fund + pmt method?
So essentially, the payment method would be the deciding factor as to what GL the contribution should be assigned? Excuse me as I only know the SQL part of this, but not the accounting side, so wasn't sure how those 5 codes get assigned.
Robert Brannen said:So essentially, the payment method would be the deciding factor as to what GL the contribution should be assigned?
Sorry, I was incorrect above (never try to post from memory :-) Taking a look at T_TRANSACTION again I see it appears that Fund + trn_type is what I was thinking of. Several of the lowest ID, "required" transaction types in TR_TRANSACTION_TYPE correspond directly to the various Fund GLs.
And it depends whether or not you are looking for the GL reporting on the payment or GL reporting on the contribution. E.g. a $500 contribution, when posted, will post a +$500 to the payment method GL and a -$500 to the contribution GL. (Unless I flipped them.)
For the contribution side of things, I wrote a view for our organization that links Funds and Appeals to make reporting easier since Funds are assigned to Campaigns with start and end dates.