4 Replies Latest reply on May 19, 2010 5:02 PM by buck_5648798798

    Calculating within a date range

    buck_5648798798

      Title

      Calculating within a date range

      Post

      I have a simple calculation that adds up dues and donations. I want to modify it so it displays the total but in the current fiscal year July 1 - June 30. I'm something of a noob so I have no idea.

       

      This is the calculation that I have:

       

      Sum(GIVING_HISTORY::amount)  + Sum(MEMBERSHIP_HISTORY::dues)

       

      Thanks!

       

      OSX 10.6.3 FM 10

        • 1. Re: Calculating within a date range
          philmodjunk

          Clearly you have a relationship linking a parent record to Giving_History and another linking it to Membership_History.

           

          What do those relationship look like?

           

          You can modify these relationships or define new ones that include date fields that you can use to specify a date range such as 7/1/2009...6/30/2010.

           

          Post your relationships and we can give you a more detailed example of how to do it.

          • 2. Re: Calculating within a date range
            buck_5648798798

            Hi. I have 3 tables:

             

            Membership_History <--- Membership ---> Giving_History

             

            all 3 tables have a field called "member_ID". The "member_ID" in "Membership" is a serial number.  They are all linked through this field.

             

            I hope my description makes sense...

             

            Thanks!!

            • 3. Re: Calculating within a date range
              philmodjunk

              In otherwords, you have:

               

              Membership::member_ID = Giving_History::member_ID

               

              Membership::member_ID = Membership_History::member_ID

               

              In your membership table, define two new date fields, gYearStart, gYearEnd and select the Global Storage option in field options.

               

              Now a multiple field relationship like this:

               

              Membership::member_ID = Giving_History::member_ID AND

              Membership::gYearStart < Giving_History::date AND

              Membership::gYearEnd > Giving_History::date

               

              Will give you what you need. You simply have to enter or select the desired dates for the beginning and end of your fiscal year.

               

              Repeat this same set up for membership_History.

               

              You may need to these set up as separate relationships if you need the above relationships unchaged for other uses. In that case, select your two history tables in Manage | Database | Relationships (actually they're table occurrences) and press the button with two plus signs to make new table occurrences. Use them to define your relationship and refer to these new table occurrences in your calculation.

              • 4. Re: Calculating within a date range
                buck_5648798798

                Thanks! That worked perfectly. Thanks for showing me how to post relationships too. I'm sure that will come in handy.