7 Replies Latest reply on Dec 12, 2008 8:07 AM by TSGal

    Adding Record navigation To Add more donations



      Adding Record navigation To Add more donations



      I have 3 tabled linked.  


      Donor = name address etc

      Donation = donation amount etc

      Contact = date of donation, date thank you letter etc.


      How do I create multiple records of donations for a donor? That is 1 donor gives 8 donations. When I erase the donation last given my record only records what I put into the field and my number of donations stays at 1.



      HELP AGAIN!!! 

        • 1. Re: Adding Record navigation To Add more donations



          Thank you for your post.


          Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab so we can view the relationship between Donor table and Donations table.  Most likely, there is a line between the two tables that links them together via a "key" field.  Double-click the icon on that line, and an "Edit relationship" dialog box appears.  One side displays the fields from Donor, and the other side display the fields from Donations.  Below the side that has the Donations fields, put a checkmark in the option "Allow creation of records in this table via this relationship".  This allows you to add records to the Donations table while viewing Donors.  Click OK and return to your layout.


          Pull down the View menu and select "Layout Mode".  You are now in a mode where you don't see data, but you can set up your form/layout the way you want to view information.  On the left side of the screen, click on the icon just below the oval tool.  This is the portal tool, and it allows you to see records in a related table.  Use this tool to draw a portal on the layout by clicking down on the upper left corner, and dragging it down to the bottom right corner, and let go.  A "Portal Setup" dialog box appears prompting you to "Show related records from:".  Click on the pop-up to the right of that and select the Donations table.  Click OK, and another dialog box appears asking you for which fields you want to display in the portal.  Select the fields you want for adding the donation (date, amount).  Click OK, and you are set.  Go to Browse mode, and you should see a related donation for this donor.  However, notice that you can now click on the row below it to enter another donation.


          What you were probably doing was entering information over the old data, thereby overwriting the previous information.


          This should get you pointed in the right direction.  If you need clarification for any of the above steps, please contact me.



          FileMaker, Inc. 

          • 2. Re: Adding Record navigation To Add more donations


            I followed your instructions and they worked as you said. The field I already have on my form did not total the donations or did it add the number of transactions. I am obviously missing something here.


            My struggle is I want to be able to add more than one donation for someone and know how many times they have donated, their average donation, their largest donation and their latest donation.


            Am I in the ball park or out in the middle of traffic looking for my car?



            • 3. Re: Adding Record navigation To Add more donations



              You're definitely still in the ball park, the game isn't over, and I can't think of any more analogies...  :-)


              In the Donor table, we need to create some additional calculation fields.  Let's start with "TotalDonations", a calculation field resulting in type number with the calculation:


              Sum (Donations::Amount )


              What this does is sums (totals) the Amounts from the Donations table associated with the Donor record.


              Other functions that can be used in calculation fields with portals include:

              Count (Donations::Amount )

              Max (Donations::Amount )

              Min (Donations::Amount )

              Average (Donations::Amount )


              These four functions can give you the number of times a person has donated, the maximum donation amount, the minimum donation amount, and the average donation amount, respectively.


              Try these out.



              Filemaker, Inc. 

              • 4. Re: Adding Record navigation To Add more donations


                How frustrating this is.


                I want to be able to add more donations to a donor record and see a grand total donated as well as the number of donations made.


                The Portal thing gives me some information on each donation but doesn't total the amounts or the number of donations. 


                I have followed what you have said and it works but doesn't total.


                HELP!!!!!!  I'm drowning!



                • 5. Re: Adding Record navigation To Add more donations



                  Have you included the calculation field on the layout?  If not, go into Layout Mode (View menu), click on the field tool on the left side of the screen and drag it onto the layout below the portal.  Let go, and you will be prompted for the field name.  Select the newly created calculation field.  Pull down the View menu and select "Browse Mode".  You should now have a value in that field that reflects what appears in the portal.  If you used the field that totals, then you may want to return to Layout mode and format the number to two decimals and display currency.



                  FileMaker, Inc. 

                  • 6. Re: Adding Record navigation To Add more donations

                    Well, I am now DESPERATE for a solution because nothing seems to work. In fact, I beginning to think a calculator and paper might be the best database yet.


                    Anyway, here is what I have done so far.


                    I've created portal after portal - I get some history with numbers partly cut off.

                    I have a total donation field and a number of donation field on my layout.


                    I have played with:  sum (donations) no luck.

                    I have played with: count(donations) no luck.

                    I have played with: summary(donations) no luck. 


                    Cand this really be that difficult.






                    • 7. Re: Adding Record navigation To Add more donations



                      1. Pull down the File menu and select "Manage -> Database"


                      2. Click the "Tables" tab and select the "Donor" table.


                      3. Click the "Fields" tab and the fields for "Donor" table should now appear.  If not, then at the top left of the screen, there is a pop-up menu next to "Tables".  Select "Donor".


                      4. In the "Field Name" box at the bottom of the screen, type:   TOTAL DONATIONS


                      5. On the same line, next to "Type", there is a pop-up.  Select Calculation.


                      6. In the bottom left, click the "Create" button.


                      7. A new dialog box appears entitled "Specify Calculation".  The field names from "Donor" are currently listed on the left half of this window, and the available functions are listed on the right half of this window.


                      8. Scroll down the functions on the right side of the screen until you come to "Sum ( field (; field...) ) "


                      9. Double-click on "Sum ( field (; field...) ) "


                      10. In the large area below the two lists (fields list and functions lists), you should see:

                      Sum ( field (; field...) )

                      ... with "field 9; field...) "  highlighted.  Leave it as is.


                      11. At the top of the field list on the left side, change this pop-up from "Current table ("Donor") " to the Donation table listed under Related Tables.


                      12. Now, on the left side, are the listing of fields for "Donation".


                      13. Double-click on the Amount field (or whatever the name of field that contains the donation amount).


                      14. In the large area below the fields list and function lists, it should now say:


                      Sum ( Donation::Amount )


                      15. At the bottom of this window, make sure it says "Calculation result is Number".


                      16. Click OK to leave the Specify Calculation dialog box, and click OK again to leave "Manage Database".


                      17. Go to the layout that contains your portal into Donation table.


                      18. Pull down the View menu and select "Layout Mode"


                      19. Grab the Field tool from the left side of the screen onto the layout below the portal and let go.  Select the newly created TOTAL DONATIONS field.


                      20. Pull down the View menu and select "Browse Mode".


                      You will now see totals for the portal records displayed below the portal.


                      Let me know if you run into any difficulties with the above steps.



                      FileMaker, Inc.