10 Replies Latest reply on Aug 9, 2011 10:57 AM by brianquillin

    Calculation for Combining Two Number Fields from Separate Tables

    brianquillin

      Title

      Calculation for Combining Two Number Fields from Separate Tables

      Post

      Very basic question but I cannot find a direct answer in any thread.

      I am trying to track TOTAL CONTRIBUTIONS for my non-profit organization.

      I would like to combine, for each CONTACT, Invoice::invoice_total AND Contributions::contribution_total.

      Brief Explanation:

      We have contacts (approx 400+) that over a several year period will sponsor various things for a specific dollar amount (which invoices are created for); they will also donate/contribute items to be sold via auction; they will also purchase items at the auction donated by other contacts.

      I would also like to create a HISTORY table (if necessary) so that I can create a report by contact that will show their TOTAL CONTRIBUTION for a one year period (I've already found information about sorting the date range).

      Lastly, and separate issue: any suggestions on how to have FM PRO 11 generate SPONSOR CATEGORIES based on this TOTAL CONTRIBUTION (ie.  $1 - $99 = Donor; $100 - $249 = Sponsor; $250 - $499 = Angel, etc.)

        • 1. Re: Calculation for Combining Two Number Fields from Separate Tables
          ClayTarver

          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. 

          • 2. Re: Calculation for Combining Two Number Fields from Separate Tables
            brianquillin

            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?

            • 3. Re: Calculation for Combining Two Number Fields from Separate Tables
              philmodjunk

              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.

              • 4. Re: Calculation for Combining Two Number Fields from Separate Tables
                brianquillin

                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?

                Thanks...

                • 5. Re: Calculation for Combining Two Number Fields from Separate Tables
                  philmodjunk

                  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.

                  • 6. Re: Calculation for Combining Two Number Fields from Separate Tables
                    brianquillin

                    PhilModJunk,

                    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).

                    • 7. Re: Calculation for Combining Two Number Fields from Separate Tables
                      philmodjunk

                      You can upload a clone to the file sharing site of your choice and post the download link here.

                      • 8. Re: Calculation for Combining Two Number Fields from Separate Tables
                        brianquillin

                        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)

                        http://dl.dropbox.com/u/9855171/Silent%20Auction%20Blank%20Clone.fp7

                        • 9. Re: Calculation for Combining Two Number Fields from Separate Tables
                          philmodjunk

                          @Brian, sorry to not get back to you sooner. I just tried to download your file and got a 404 error.

                          • 10. Re: Calculation for Combining Two Number Fields from Separate Tables
                            brianquillin

                            I pulled the file.  I had changed too many things for it to be relevant after I uploaded it.