5 Replies Latest reply on Nov 3, 2010 2:48 PM by philmodjunk

    Accumulation over previous 60 days

    KGLeidal

      Title

      Accumulation over previous 60 days

      Post

      I would like to calculate the rainfall over the last 60 days (not really, but it's a simple example) and subtract from a maximum allowable amount.  I have the rainfall amounts and dates for the past year.  The final calculation is to subtract the total from the last 60 days from 500.  Suggestions?

        • 1. Re: Accumulation over previous 60 days
          philmodjunk

          Perform a find for all records dated from 60 days ago to today's date.

          Enter Find Mode[]
          Set Field [YourTable::yourDateField ; "> " & Get ( CurrentDate ) - 60 ]
          Perform Find []

          Now a summary field that computes the Total Of your Rainfall field will return the total rainfall for your time period and

          500 - YourSummary field in a calculation field will produce the result you've specified.

          • 2. Re: Accumulation over previous 60 days
            KGLeidal

            I think you're talking over my level here.  Is the "Perform a Find" procedure a script?  

            I made this database 7-8 years ago in AppleWorks and am trying to redo it in Filemaker but the commands are not the same.  It Appleworks, I used this:

            Volume is the amount of rain on a day.  

            60DaysAgo = NOW()-60    Note - NOW is not dynamic, I have to reset it each day I print the report, unfortunately.

            DateTest = If('Date'<'60DaysAgo',0,'Volume')

            DateTestSum = 500 - sum('DateTest')

            I can't even remember how I came up with this; it's the only database I ever wrote.  It seems close to your description but the Find Mode confuses me.


            • 3. Re: Accumulation over previous 60 days
              philmodjunk

              What you've posted appear to be definitions for different calculation field.

              I've described a script. Each step matches a step you can create in the script editor for this as long as you subsitute your date field referece for "YourTable::yourDateField".

              The second step is to define two calculation fields, a summary field that computes the total "rainfall" over all the records found. The second field subtracts this total from 500.

              • 4. Re: Accumulation over previous 60 days
                KGLeidal

                I put in the script as you described but it doesn't run.  All I get is 500 minus the last recorded volume.  How do I make the script run?  I have searched all over various Filemaker training videos and manuals and all I can find is how to write scripts, never how to make them actually run.

                • 5. Re: Accumulation over previous 60 days
                  philmodjunk

                  You can select the script from the scripts menu, assign it to a button that you click, assign it to a script trigger or you can highlight the script in Manage | Scripts and click the button with the green arrow.