2 Replies Latest reply on Feb 6, 2013 2:38 PM by FileMakerNovice

    Creating new dates given a date range.

    FileMakerNovice

      Title

      Creating new dates given a date range.

      Post

           I have an Employee table and a Date table.  They are related by EmployeeID. (more detail below, if needed)  I have a layout that uses the Employee table and displays a portal of the Date table.  I am having difficulty developing a script:

           Script:
           In this layout, I'd like the user to enter a reason, as well as a start and end date.  Once this is committed, a date from start to end with the reason would be populated.
           Input:  1/1/13, 1/3/13, Training
           Result:  The following records would be created in Dates:  1/1/13, Training; 1/2/13, Training; 1/3/13 Training.

           Table details:
           Employee-Dates

           Relationship:
           Employee::ID=Dates::ID

           Table Fields:
           Employee:  Name, email, ID
           Dates:  Date, ID, Reason.

        • 1. Re: Creating new dates given a date range.
          philmodjunk

               The first and last dates can be entered into a pair of global fields: globals::gDate1; globals::gDate2. (Global fields not used as match fields in relationships can be defined in any table in your databas.) The same can be true for your reason.

               Set Variable [$ID ; value: Employee::ID]
               Go to Layout [Dates ( Dates) ]
               Set Variable [ $Date ; value: Globals::gDate1]
               Loop
                  Exit Loop If [$Date > Globals::gDate2 ]
                  New Record/Request
                  Set Field [Dates::Date ; $Date ]
                  Set Field [Dates::Reason ; Globals::Reason]
                  Set Field [Dates::ID ; $ID ]
                  Set Variable [$Date ; value: $Date + 1 ]
               End Loop
               Go to Layout [original layout]

                

          • 2. Re: Creating new dates given a date range.
            FileMakerNovice

                 Awesome!  Thanks again Mister PhilModJunk.