7 Replies Latest reply on Jan 11, 2012 2:46 PM by philmodjunk

    Find records from the last three Mondays?

    JonathanGourd

      Title

      Find records from the last three Mondays?

      Post

      How would I go about searching for records that include only the last three Mondays? If I put in just "Mon" I get all the Mondays but I am only interested in the last three.

       

      TNX,

      Jonathan

        • 1. Re: Find records from the last three Mondays?
          Sorbsbuster

          Add another criterion, maybe, >=Get (CurrentDate ) - 21 (if it's a script step, otherwise enter the date manually.

          • 2. Re: Find records from the last three Mondays?
            JonathanGourd

            OK, I get that it will fetch the last three weeks with that in a script step but how do I combine that with the Mon keyword? Could you give a specific example? Would it be something like: Mon, >=12/05/2011

             

            Thanks

            • 3. Re: Find records from the last three Mondays?
              philmodjunk

              You could compute the exact dates and make three find requests:

              Let ( Today = Get ( CurrentDate )  ; Today - DayOfWeek ( Today ) + 2 ) - ( DayOfWeek ( Today ) = 2 )

              Will compute the closest Monday to Today. The last term in parenthesis adjusts for when today is a Monday.

              This script will find dates for the three past Mondays closest to day.

              Enter Find Mode [] //clear the pause check box
              Set Variable [$Monday1 ; value: Let ( Today = Get ( CurrentDate )  ; Today - DayOfWeek ( Today ) + 2 ) - ( DayOfWeek ( Today ) = 2 ) ]
              Set Field [YourTable::YourDateField ; $Monday1 ]
              New Record/Request
              Set Field [YourTable::YourDateField ; $Monday1 + 7 ]
              New Record/Request
              Set Field [YourTable::YourDateField ; $Monday1 + 14 ]
              Set Error Capture [on]  //keeps no record found dialog from interrupting script if no records are found.
              Perform Find []

              • 4. Re: Find records from the last three Mondays?
                JonathanGourd

                Thanks, I ended up going with:

                Enter Find Mode [ ]
                Set Field [ PACS_Storage::Date; "Mon >=" & Get (CurrentDate ) - 21 ]
                Perform Find [ ]

                It ended up being pretty easy. 

                 

                Now for the hard part. Can I easily calculate an average delta between a value in a numeric field in the 3 Monday result set? For instance the previous find returns three records and the bold field contains:

                1/9/2012, 751.48
                1/2/2012, 705.47
                12/26/2011, 582.00

                In a spreadsheet it is fairly trivial calculate the delta between rec 1&2 and rec 2&3 and AVG() the two numbers. In FileMaker, I haven't a clue.

                • 5. Re: Find records from the last three Mondays?
                  philmodjunk

                  I'm suprised that worked, but it does. (I had to test that one for myself.)

                  The GetNthRecord function can be used to compute values that pull data from other records in the same found set. It is dependent on the current sort order, change the order or the found set and it returns different results.

                  Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; GetNthRecord ( FieldnameHere ; N - 1 ) ; 0 ) )

                  Is an expression that accesses data in the previous record if one exists, returning zero if a previous record does not exist.

                  • 6. Re: Find records from the last three Mondays?
                    Sorbsbuster

                    When you said that you had tried looking for 'Mon' I assumed that you had a calculation that showed the weekday name, and were searching for Mondays in it.  My second criterion would have restricted it to weekdays in the last 21 days whose name started with 'Mon'.  Like Phil, I am amazed that your expression worked!  Every day's a schoolday.

                    • 7. Re: Find records from the last three Mondays?
                      philmodjunk

                      We are both suprised because date fields are really number fields that store the number of days from the date entered to the date 12/31/0000.

                      All the month name, day name, month, day and year stuff you see in a date field is really special data formatting so I tend to think in terms of actual dates and their numeric equivalents. However, as shown with search criteria such as 10/*/2011, you can use some of this special formatting as search criteria when your field is of type date.

                      A quick review of the help system pulls up an example of specifying the day of the week name in an article titled:

                      Finding numbers, dates, times, and timestamps

                      So this is at least partially documented, but combining a partial day name with an inequality operator and a date, that's definitely a new one to keep in mind...