3 Replies Latest reply on Apr 2, 2015 7:39 AM by philmodjunk

    Loop through Date Range



      Loop through Date Range


      I'm working on a leave request form where the user puts in a date range and submits the request for approval. If approved the supervisor would click a button that will go through each date in the date range... check to see if 1) it's a work day and if 2). There is a holiday within the date range.

      What's the best way to loop through a range of dates. DateStart and DateEnd?

      I have a variable $next (DateStart + 1) and that gives me the next day but then I'm unsure how to account for the remaining days in the range. 



        • 1. Re: Loop through Date Range

          For reasons of teaching you how to construct a loop, I will provide an example script for what you want at the end, but there is no need to use a looping script for what you want. It's possible to use a relationship or ExecuteSQL to count the number of work and holiday dates in a given range without any looping at all.

          If $DateStart is the first date to check and $DateEnd is the last date:

             Exit loop if $DateStart > $DateEnd ]
             #Put steps to check your dates here
             Set Variable [$DateStart ; value: $DateStart + 1 ]
          End Loop

          • 2. Re: Loop through Date Range

            Hey Thanks!!! Yeah I figured it out pretty similarly the way you describe. I'm not at all well versed in ExecuteSQL and I'm not sure how I would have done it through a relationship.

            I should clarify that when I said Holidays, I meant the days the office observes the holiday. For example, we would have July 3 off because July 4 is a Saturday.

            So basically what I did was create a holiday table and put all of our office holidays in it for the year. (we'll update yearly).
            At the start of the loop I look to see if DateStart is a weekday with $next being the variable for DateStart

            DayName($next)  = "Monday";1; 
            DayName($next)  = "Tuesday";1;
            DayName($next)  = "Wednesday";1; 
            DayName($next)  = "Thursday";1; 
            DayName($next)  = "Friday";1

            And if so then it continues on and looks to see if that matches a date in the holidays table.

            If not... it proceeds to create a new record in the time sheets and drops all the information from the request form into the new record via variables. I still have to add a few more steps to capture for certain errors but in it's current form it's doing what I want it to do. This is what I came up with.

            Allow User Abort [ Off ]

            Set Error Capture [ On ]



            Set Variable [ $start; Value:LeaveRequest::DateStart ]

            Set Variable [ $next; Value:$start ]

            Set Variable [ $end; Value:LeaveRequest::DateEnd ]

            Set Variable [ $leaveRequestID; Value:LeaveRequest::_pkLeaveRequestID ]

            Set Variable [ $employeeID; Value:LeaveRequest::_fkEmployeeID ]

            Set Variable [ $type; Value:LeaveRequest::Type ]

            Set Variable [ $status; Value:"Approved Leave" ]

            Set Variable [ $sig; Value:LeaveRequest::Approved SIgnature ]

            Set Variable [ $sigdate; Value:LeaveRequest::Approved Date ]

            Set Field [ LeaveRequest::Status; "Approved" ]



            Freeze Window



            If [ Case(

            DayName($next) = "Monday";1;

            DayName($next) = "Tuesday";1;

            DayName($next) = "Wednesday";1;

            DayName($next) = "Thursday";1;

            DayName($next) = "Friday";1

            ) ]


            Enter Find Mode [ ]


            Go to Layout [ “Holidays” (Holidays) ]

            Set Field [ Holidays::Date; $next ]

            Perform Find [ ]


            If [ Get(FoundCount) < 1 ]


            Go to Layout [ “Timesheets_ENTRY” (Timesheets) ]


            New Record/Request

            Set Field [ Timesheets::_fkEmployeeID; $employeeID ]

            Set Field [ Timesheets::DateEntry; $next ]

            Set Field [ Timesheets::TimeSlipCategory; "TIME OFF" ]

            Set Field [ Timesheets::TimeslipTimeOff; $type ]

            Set Field [ Timesheets::_fkLeaveRequestID; $leaveRequestID ]

            Set Field [ Timesheets::DescriptionEntry; "Approved Leave" ]

            Set Field [ Timesheets::BillingStatus; "Not Billable" ]

            Set Field [ Timesheets::Hours; "8" ]

            Set Field [ Timesheets::Approved Signature; $sig ]

            Set Field [ Timesheets::TimeStatus; "Approved" ]


            End If


            End If

            Set Variable [ $previous; Value:$next ]

            Set Variable [ $next; Value:$previous + 1 ]

            Exit Loop If [ $next > $end ]

            End Loop

            Set Variable [ $userName; Value:leave_EMPLOYEES::First Name ]

            Set Variable [ $userEmail; Value:leave_EMPLOYEES::Email Address ]

            Set Variable [ $superName; Value:leave_employees_SUPERVISORS::c_FullName ]

            Set Variable [ $superEmail; Value:leave_employees_SUPERVISORS::Email ]

            Set Variable [ $pdf; Value:Get(TemporaryPath) & "LeaveRequestApproved.pdf" ]

            Save Records as PDF [ File Name: “$pdf”; Current record ]

            Send Mail [ Send via SMTP Server; To: $userEmail; Subject: $superName & " has approved your Leave Request"; 

            Show Custom Dialog [ Message: "These dates have been added to Timesheets and the user has been notified of your approval.";
            Exit Script

            • 3. Re: Loop through Date Range

              Instead of your case statement, you can identify a week day date like this:

              DayOfWeek ( YourDate ) > 1 and DayOfWeek ( YourDate ) < 7

              DayOfweek returns 1 for Sunday and 7 for Saturday.

              I once set up a table of dates with one record for each day of the year. A field in the record marked it as either a date the business was "open" or "Closed". And it was possible to select a specific day of the week (only Sunday for this particular business) and have them be automatically marked "closed". These records were also presented in a typical calendar format via one row portals in a list view to make it easy to review what dates where "open" and "closed" for a given month.

              Then this relationship only matched to open dates on the specified date range:

              MainTable::Date1 > Schedule|Open::Date AND
              MainTable::Date2 < Schedule|Open::Date AND
              MainTable::constOpen = Schedule|Open::Status

              Using a second Tutorial: What are Table Occurrences? of the same table, but named Schedule|Closed:

              MainTable::Date1 > Schedule|Closed::Date AND
              MainTable::Date2 < Schedule|Closed::Date AND
              MainTable::constClosed = Schedule|Closed::Status

              This was in FileMaker 10 where ExecuteSQL was not an option

              Count ( Schedule|Open::Date ) would count the number of dates from Date1 to Date2 where the business was open. Count ( Schedule|Closed::Date ) counted the number of closed dates over the same range. constOpen and constClosed are very simple calculation fields that return the same text constant in every record of MainTable. constOpen, for example could have "Open" in quotes as its sole calculation term.

              The same type of matching logic can be created via executeSQL.