7 Replies Latest reply on Apr 14, 2017 9:41 AM by jbrown

    List of Dates In Between Two Dates

    scottganderson

      I have searched and found questions similar to mine but nothing that exactly fits my situation.  I have an events table in which each event has both a start date and an end date.  I'm trying to make a list of every date that an event passes through (start date to end date, inclusive). I added a calculated repeating field in my events table, hoping to populate it with one entry for each date that includes a given event, but I can't quite figure out how to get the data into the field.  I was hoping to do it with a simple Boolean calculation, but if that's possible, I can't seem to do it.  Could be that it's achievable through a short script that runs each time a new event is created, but again, I'm unsure if that's the right way to go.  I'm hoping for some guidance in this matter if anyone has a suggestions.  Thanks in advance.

        • 2. Re: List of Dates In Between Two Dates
          scottganderson

               Ah, right. Thanks for your reply. Saw these but I guess I should have mentioned in my original post that I'm not running FileMaker Pro Advanced and therefore not able to create custom functions.  Wondering if it's still possible.  Or simply time to upgrade.

          • 3. Re: List of Dates In Between Two Dates
            TomHays

            The primary advantage to the custom function is that it is not limited in the distance between the start date and end date.  (There is a fundamental limit, but it is very high.)

             

            If the maximum number of dates you need isn't very high (e.g. 31 for an event lasting up to 1 month), and FileMaker Pro Advanced is not available, you could write a single calculation that returns the list of dates.

             

            Here is an example of such a calculation to handle events that last up to 7 days.  You can modify it for longer durations just by adding more lines in the same pattern.

             

            -Tom

             

            Let(

            [startDate = Date(3;1;2016);

            endDate = Date(3;4;2016);

            d = endDate - startDate

            ];

            startDate &

            If(d>1; "¶" & GetAsDate(startDate+1);"") &

            If(d>2; "¶" & GetAsDate(startDate+2);"") &

            If(d>3; "¶" & GetAsDate(startDate+3);"") &

            If(d>4; "¶" & GetAsDate(startDate+4);"") &

            If(d>5; "¶" & GetAsDate(startDate+5);"") &

            If(d>6; "¶" & GetAsDate(startDate+6);"") &

            "¶" & endDate

            )

            • 4. Re: List of Dates In Between Two Dates
              TomHays

              The CustomList() function by Agnès Barouh does not require recursion and can be used as a regular calculation to generate an arbitrarily long list of dates.

               

              To convert it to a non-Custom Function calculation, wrap it with a Let() and define the three parameter values as Let() variables.

               

              Let(

              [

              Start = 1;

              End = 25;

              Function = "Date(3;1;2016) -1 + [n]"

              ];

              ... the full original CustomList() definition goes here

              )

              • 5. Re: List of Dates In Between Two Dates
                jbrown

                Evening. It is time to upgrade to Pro Advanced. It contains tools that a developer simply needs. The data viewer is one. In my first years of development I never used that. Now it is essential.

                Custom functions, the debugger, copy/paste fields are essentials.

                 

                Are you going to be able to come to DevCon? Historically a copy of FileMaker Pro Advanced is given to every attendee.

                 

                For your question without Advanced, you could create a script that loops. begin the script with a  variable called theDate and set it to your event start date. Then set each repetition in the loop. Each trip thru the loop, set a rep with the value of the variable, then add one day to your theDate variable. Exit this loop when this value in the variable is is greater than your end date.

                 

                Without advanced  you can't see the variable incrementing but you will see the results

                 

                in the am I can provide a small demo

                • 6. Re: List of Dates In Between Two Dates
                  scottganderson

                  Thank you, Jeremy. I will give that a try 

                  • 7. Re: List of Dates In Between Two Dates
                    jbrown

                    Another thought:

                    Variables are stored as text, so using GetAsDate (theDate)+ 1 is a good idea.

                    At the end if your loop:

                     

                    Exit Loop If [ GetAsDate (theDate) > YourTable::YourEndDate.