6 Replies Latest reply on Apr 1, 2014 11:52 AM by philmodjunk

    Calculation Fields in Main Layout not Working

    NancyatUCM

      Title

      Calculation Fields in Main Layout not Working

      Post

           Hi - I hope somebody can help:  I have several fields in my main layout (Mailing List database) in which I'd like to show calculated sub totals from a related table (gifts/donations).  The fields are:

           2013 Gifts (ie. the total amount of any gifts received between 1/1/13 and 12/31/13)

           2014 Gifts

           Total Gifts to date (total amount given since I re-vamped the database - this one is working)

           I had gotten the 2013 gifts field working so that it always totaled up gifts received within that year, but when I added the 2014 field with the same wording but different dates, the 2013 gifts field went blank.  I deleted the 2014 field and re-created it, but that didn't help.  Also the 2014 field is only showing the most recent 2014 gift, not totaling them if multiple gifts have been given.

           The screen shots below show my field descriptions, my main layout, and a sample record in my main layout.

           Any ideas on how to solve this? I'd really appreciate it!

      Screen_shot_Sample_record.png

        • 1. Re: Calculation Fields in Main Layout not Working
          philmodjunk

               Posting the actual calculation expression--something that you can copy from the Specify Calculation dialog and paste into Post a New Answer--would be helpful.

               And what is the relationship between Mailing List Database and Gifts/Donations? What match fields are used?

          • 2. Re: Calculation Fields in Main Layout not Working
            NancyatUCM

                 Thanks - here's the actual calculation expression for the 2013 field:

                 If ( Gift Date ≥ Date ( 1 ; 1 ; 2013 ) and Gift Date  ≤ Date ( 12 ; 31 ; 2013 ) 
                 ; Sum (Gift Amt.) )
                  
                 and for 2014 field:
                  
                      If ( Gift Date ≥ Date ( 1 ; 1 ; 2014 ) and Gift Date  ≤ Date ( 12 ; 31 ; 2014 ) 
                      ; Sum (Gift Amt.) )

                 and the database and gifts table are liked w/ a key (ID) field. I'm including a screenshot of the relationships table in case that helps. (the 'Gifts 2', Gifts 3', etc. are an old attempt to solve this problem another way I believe, but I've been afraid to get rid of them in case they're doing something I have forgotten about (lame, I know!)

            • 3. Re: Calculation Fields in Main Layout not Working
              philmodjunk

                   Ah yes, another "Sum If" request.

                   Are you sure that the actual calculation isn't this?

                   If ( Gifts::Gift Date ≥ Date ( 1 ; 1 ; 2013 ) and Gifts:Gift Date  ≤ Date ( 12 ; 31 ; 2013 ) 
                   ; Sum (Gifts:Gift Amt.) )
                    
                   Either form doesn't work but the reason why it doesn't work will differ.
                    
                   What you have posted:
                        If ( Gift Date ≥ Date ( 1 ; 1 ; 2013 ) and Gift Date  ≤ Date ( 12 ; 31 ; 2013 ) 
                        ; Sum (Gift Amt.) )
                         
                        doesn't use Sum correctly and won't work at all where the version I have posted will seem to work correctly in some situations and fail in others--which is more consistent with what you described in your first post. What you just posted fails because it is the syntax you would use if you defined it in the Gifts table and Sum ( Gift Amt. ) could be replaced by Gift Amt. and you'd see the same result because the syntax uses is the same as writing Sum ( 5 ) and the some of the single term 5 is 5.
                         
                        The other version fails because Gift::Gift Date will only refer to the date of the first related record and either return the sum of all gifts record or no sum--which still fails to return the sum of just the records for a specific year.

                    

              • 4. Re: Calculation Fields in Main Layout not Working
                philmodjunk

                     Computing totals from selected groups of related records, such as all gifts linked to a record in UCM Mailing list made in a specific year, can be done through one of several different approaches.

                     1) You can define relationships that only match to a specific  year. Then the Sum function or a summary field defined in Gifts can compute the total for that year.

                     UCM Mailing List::Key = Gifts|SelectedYear::Key AND
                     UCM Mailing List::gSelectedYear = Gifts|SelectedYear::cYear

                     This relationship matches to a new occurrence of Gifts  by Key and by year. gSelectedYear would be a number (possibly with global storage) and cYear would be a calculation field defined with a Number result type and this expression: Year ( Gift Date ).

                     Then you can define a calculation field in UCM Mailing List as: Sum ( Gifts|SelectedYear::Gift Amt. ) to get the total for that donor for that year. By editing/selecting different years in gSelectedYear, you can see totals for different years. You can also use this as a pattern for several relationships to different occurrences of Gifts with different fields in place of gSelectedYear to see different totals for different years at the same time.

                     2) Another option is to use a summary report on a layout based on Gifts. You perform a find or do a Go To Related Records to pull up the Gifts records for one or more Donors for a specified range of dates and then sort the records by Donor and by Year. Summary fields placed in Sub Summary layout parts will show the subtotals and a grand summary layout part can use the same summary field to show a grand total.

                     It's also possible to set up a filtered one row portal to Gifts that filters for a specific year. If you place a summary field defined in Gifts that computes the total of Gift Amt. in this portal row, it will show the total for the year specified in the portal's filter.

                     3) And for those using FileMaker 12 or newer, a single Calculation field can use ExecuteSQL to show the total for one year or list of totals for several years of donations....

                • 5. Re: Calculation Fields in Main Layout not Working
                  NancyatUCM

                       In spite of reading over options 1) and 2) from PhilModJunk more times than I like to admit, and numerous false starts, I still haven't been able to solve my problem, I'm afraid. (Option 3 is out because I have Filemaker Pro 11).  I am relatively new  to Filemaker Pro and am finding it difficult to understand, let alone implement one of the proposed solutions.  Could someone provide a more step-by-step answer?  I'm wondering if I asked the question in a misleading way - the bottom line is that, when I look at a person's record in our database, I'd like to see a total of what they gave in each year, in addition to details on each gift. Thanks for any help you can provide.

                  • 6. Re: Calculation Fields in Main Layout not Working
                    philmodjunk

                         One thing that is very difficult to determine is the level of expertise of the original poster. Often, my suggestions are in the form of a "wedge". I post a general description of how to solve the problem first (the tip of the wedge). If the user has sufficient skill and experience, that may be all that they need to apply the solution.

                         But if it isn't, they can keep posting follow up questions and I can keep supplying more and more detailed descriptions until the solution makes sense.

                         Go to Manage | Database | relationships

                         Select the table occurrence box for Gifts.

                         Click the duplicate button (two green plus signs).

                         You can double click this new table occurrence, named Gifts 2, to open a dialog box where you can rename it gifts|selected year if you wish.

                         Click over to the fields tab and add a new number field, gSelectedYear. Open field options (double click the new field definition or click the options button), select the storage tab and select the option for global storage. (I put a small g at the beginning of all fields that have global storage specified.)

                         Then add a calculation field cYear to your Gifts table and define it as: Year ( Gift Date ). Select Number as the result type.

                         Now return to the relationships graph and drag from UCM Mailing List::Key to Gifts|SelectedYear::Key. Then drag from UCM Mailing List::gSelectedYear to Gifts|SelectedYear::cYear

                         You now have the relationship specified in 1) of my previous post. You can define the specified calculation field: Sum ( Gifts|SelectedYear::Gift Amt. )

                         and get different totals for different years by entering different years into the gYear field.

                         For more on table occurrences, see: Tutorial: What are Table Occurrences?