You might consider having one table for Contacts and one for Contributions, linked on the contact ID field. The Contributions table would have a field specifying the type, i.e. Cash, Item, and Purchase, and a field for the amount either contributed directly or paid for a donated item. This would be your history table if you add a date field. You can create a summary field to display each Contact's total for invoices and contributions. This field would provide the basis for the Category definition, in a Case statement.
This may not be the most efficient way of structuring my solution, but I have the following tables: Contacts, Items, Line Items, Contribution (for creating donation receipts), Invoices (for creating invoices when we auction items), Payments, InvoicePayments (join table).
I would like to create a summary field for each contact totaling their invoice totals AND contribution receipt totals. How do I do that?
I didn't follow the last comment: "in a Case statement". Is there a way to have a category automatically created by the summary field's amount?
Summary fields can only summarize a single field defined in the same table as the summary field.
Calculation fields, however, can combine the totals of two or more summary fields to produce a combined total.
Recording donations in the payments table with an added field to tell invoice payments from donations would eliminate the need for such a calculation field. This would allow your payments table to function as your "history table" as it now records all contributions in a single table. Summary reports on this layout can produce subtotals for each year and/or any other useful time interval such as by month or quarter. The individual contributions (payments) can be listed or just the subtotals.
The additional field in Payments would be "Type" (for example)?
This could become very tanlged; but here's my major delimma/decision? Is a Contribution more like an ITEM or a PAYMENT?
For the Silent Auction Tracking portion of the db, I need to receipt people for the Items they donate indicating the Item value. So that person's Item is a Contribution. HOWEVER, I don't want contributed Items to be considered Payments that can be used for an Invoice (for when they purchase another person's auction item).
So would I set up a Contributions Table with a Type field that would include: Invoice payment, Donation?
Furthermore, I will have different types of Donations (Cash, Auction Item, Costume Sponsor, Orchestra Sponsor, Materials, Services, etc).
This is my greatest barrier in moving forward on this solution. I cannot decide on the most efficient structure.
clay_tarver provided me with a great sample db of how to create categories using Case statement. His example included the use of global fields for contribution types. What would be the reason/benefit to incorporate the use of these global fields?
I'm suggesting you use your exsiting payments table to log contributions by adding such a "type" field where payments applied to pay off your auction invoices are labeled with a different "type" than your direct contributions. (That might be as simple as adding "invoice payment" to the above list of contribution types.)
You can add more fields to this payment table to document the donations or you can just log the cash value and use one other field as a link to the existing donations table to log the dontation details.
I can see where, if you have donations that are not monetary, you may need to use the donation type field to filter out all non-monetary contributions in order to get the results you need in some types of reports you might choose to create from this data. That can be done by via performing a find to exclude them, through a relationship, a filtered portal or all of them in combination.
How may I upload a clone of my solution for you to review? I have been stuck in distress for several days now and really cannot proceed until I feel confident with the underlying structure and organization of this solution.
Last year, I completed a fully functional (for our purposes) db for tracking silent auction items and the ability to receipt donors and invoice buyers. NOW, I'm working to unify our db to also include contribution tracking (because our silent auction donors and buyers are also our perennial contributors). When I began, I simply wanted to add the ability to track an individual's contributions.
Current needs/problems: Tracking different types of contributions (cash, services, materials); paying off multiple invoices with single payment (I think I can do that one but haven't gotten there yet); Using conditional value list in Line Items on Invoice Layout (to allow invoicing for either sponsorship OR silent auction purposes).
You can upload a clone to the file sharing site of your choice and post the download link here.
Thanks. Here it is. It's a mess right now. I'm also working to implement a new layout design (currently on Contacts and Contributions)
@Brian, sorry to not get back to you sooner. I just tried to download your file and got a 404 error.
I pulled the file. I had changed too many things for it to be relevant after I uploaded it.