6 Replies Latest reply on Sep 30, 2009 8:35 AM by kopiikat

    Integrating Non-Profit Client Database w/ Budget?

    kopiikat

      Title

      Integrating Non-Profit Client Database w/ Budget?

      Post

      I am a new user of FileMaker Pro, and am currently using the 30 day trial of 10.0v3 on Windows XP Pro.  I have no previous database experience and very limited programming experience.  I would like to have a working multi-fuction database at the end of the trial so that I can present it to the directors and hopefully purchase the software.

       

      I am a non-profit employee.  Currently, the process of helping a client is very time-intensive, and the same data is being crunched over and over again unnecessarily. 

       

      I have already been successful and creating a client database that I can share with my social worker, but I am stuck on how I can use FileMaker to keep track of our balances (in approximately 20 different funds) automatically.  Here's an example:

      A client is being assisted with $150.  $100 is being paid out of FundABC and $50 is being paid out of FundDEF.  Both funds have a starting balance of $1000.  On my input sheet, I have the following fields for each client: check#1 (#00001), date1 (9/28/09), check1amount ($100), fund1 (FundABC), check#2 (#00002), date2 (9/28/09), check2amount ($50), fund2 (FundDEF).

       

      How can I do the following?

       

      If fund1 or fund2 = FundABC, I would like to add this client's data to a list of FundABC recipients and subtract $100 from the FundABC balance, keeping a running total.

       

      If I can't do this directly in FileMaker, can I somehow integrate it with Excel?

      Thank you so much for all of your help in advance, and I hope I will be able to convince my supervisor to purchase FileMaker for us.

       

        • 1. Re: Integrating Non-Profit Client Database w/ Budget?
          comment_1
            

          You need to use three tables: Clients, Funds and Payments. The Payments table is a join table between Clients and Funds, and it will have (at least) fields for:

           

          • ClientID (link to the Clients table)

          • FundID (link to the Funds table)

          • Check#

          • Date

           

          This way each fund is related to all payments made out of it, and each Client is related to all payments received.

           

          • 2. Re: Integrating Non-Profit Client Database w/ Budget?
            RayMentor
               Don't forget you get a reduced price on purchasing Filemaker Pro as a non-profit.
            • 3. Re: Integrating Non-Profit Client Database w/ Budget?
              kopiikat
                

              Comment, Thank you for your help.  I understand more what I should do now, but I'm having trouble implementing it because I have no database experience.

               

              Say I have funds FundABC, FundDEF, and FundGHI, each with $1000.  Client John Smith will be receiving $100 from FundABC and FundDEF, but not FundGHI.  How do I set this up so that it shows on his client profile how much money he recieved, and from which funds, and also automatically subtracts the correct amounts from each fund?

               

              What are the fields under the Funds table?

               

              Sorry I didn't understand very well last time, and thank you again.

              • 4. Re: Integrating Non-Profit Client Database w/ Budget?
                kopiikat
                   RayMentor, I was under the impression that you could only get a discount on or more copies of FileMaker as a non-profit.  Is this true, or could we still get a discount on 3-4?  Thanks for your help.
                • 5. Re: Integrating Non-Profit Client Database w/ Budget?
                  comment_1
                    

                  kopiikat wrote:
                  What are the fields under the Funds table?

                  Well, to keep it very simple, the fields could be:

                   

                  • FundID (auto-entered serial number)

                  • FundName

                  • InitialAmount

                  • cBalance, calculation =  InitialAmount - Sum ( Payments::Amount )

                   

                   

                  In a somewhat smarter arrangement, deposits into Funds would be entered into the same table as payments so it would effectively become a Transactions table, with positive or negative amounts.

                  • 6. Re: Integrating Non-Profit Client Database w/ Budget?
                    kopiikat
                      

                    Thanks so much for your help.  I really have no experience with databases, and I'm extremely unfamiliar with both the capabilities of FileMaker and the terminology used to describe it.  Still, with your help, I'm starting to feel like this project may actually work out after all.  I think I will eventually want a transaction sheet, as you mention, but if I have further questions, I'll post them in a new, clearer message.

                     

                    Thank you again!