3 Replies Latest reply on Jan 6, 2014 12:44 PM by MartinLange

    Finding the last 6 months (or last 12)

    MartinLange

      Title

      Finding the last 6 months (or last 12)

      Post

           I have been reading the forum and searching the FMP help section, but can't seem to find an answer to my question, so I'm posting it here.

           I am trying to write a script that allows me to find the last six months of donations our organization has received. 

           I've been using "Insert Calculated Result [Select; Donations::Date Given; Get (CurrentDate) - 180 & "…" & Get (CurrentDate)]

           But I don't want to use days, I want the search to return months. ie. If this is January, I want to see the donations from July 1 - Dec 31

           Any help would be appreciated.

        • 1. Re: Finding the last 6 months (or last 12)
          philmodjunk

               If you enter find mode and use:

               7/2013...12/2013

               You'll find all records from July through December of 2013. So the trick is to get a calculation that produces the needed criteria for that type of range criteria.

               Let ( [ m = Month ( Get ( CurrentDate ) ) ;
                          y = Year ( Get ( CurrentDate ) ) ;
                         m1 = m - 6 + If ( m < 6 ; 12 ) ;
                         y1 = y - If ( m < 6 ; 1 ) ;
                         m2 = m - 1 + If ( m = 1 ; 12 ) ;
                          y2 = y - if ( m = 1 ; 1 )
                        ];
                        m1 & "/" & y1 & "..." & m2 & "/" & y2
                      )

               PS. I'd use Set Field rather than Insert Calculated Result for this.
                        

          • 2. Re: Finding the last 6 months (or last 12)
            thong127

                 Try this link simple date range v7.

            • 3. Re: Finding the last 6 months (or last 12)
              MartinLange

                   Thanks PhilModJunk! This was exactly the solution I needed. I modified it to work for a 12 month period as well. I appreciate you help.