6 Replies Latest reply on Dec 8, 2015 4:00 PM by Malcolm

    Auto Create Repeating Records Based On Day Of Week

    skashanchi

      I need to allow users to create repeating trips in our system (referred to as Calendar Trips). So when a trip record is created users can click on a button that opens up a dialog and displays checkboxes for the all 7 days of the week. So if the user checks Monday, Wednesday and Friday, for example, and saves the record, I need the system to automatically create duplicate records based on the info on this trip record for every Monday, Wednesday and Friday for the foreseeable future.

       

      1. I understand how to use DayOfWeek (date) function to get the day of the week for a given date, but I am not sure how to do the reverse, i.e. find the date for next Monday, etc. What would be the best approach to this?

       

      2. I also don't think that I want 1000's of these records created automatically, so I think a) I will add a field where the user can enter an end date for these auto creations, or b) better yet have these auto created may be every Monday at 12 AM for the following week? I just need a way to not create 1000's of records at once.

       

      Any suggestions would be appreciated.

        • 1. Re: Auto Create Repeating Records Based On Day Of Week
          Malcolm

          1. The date/time functions are very flexible. Dates have a default value of one day. So, Get(CurrentDate) + 7 will give you the date for next week.

           

          2. This may be a situation in which you want to simulate the existence of many records, when in fact there are only a few records. Here are a couple of ideas, I've used each of them at different times.

           

          • Ask user to provide an end date, or a number of repetitions, then create records. Works, but it can be slow to create records, can have a lot of overhead.
          • use a set of rules that form the basis of a relationship with a table of days of the year. Let's say the rule is "every wednesday". The days that have a valid relationship can be hilited to give the appearance that they are booked. If the user actually does something on that day a record is created when it is needed. Works well, it can be hard to get the rules right ( "last wed of month" etc ) and requires a good UI to support it.
          • Use a "month" table ( a table with 42 records ). Same idea as above, but less scope. Use it to create a view of any month/year. Again, use calcs to determine which dates being displayed in the calendar have events.

           

          You might also consider implementing one of the web based calendars in a web viewer.

           

          The problem with calendars, if you get close to a good design, is that your users will never stop sending feature requests.

          • 2. Re: Auto Create Repeating Records Based On Day Of Week
            siplus

            1) a custom function: NextWeekDate(date; day)

             

            2) ask for how many weeks - it feels natural. User will say for example 6 weeks, so you will create 6 x 3 records (if he selected 3 week days)

            • 3. Re: Auto Create Repeating Records Based On Day Of Week
              skashanchi

              Thanks everyone for the responses.

               

              Siplus, can you please expand a little bit on creating this custom function? I have never actually done that. I understand where to go in FileMaker to create the function, but I am unclear as to the set up of the parameters. For example in your example what exactly are (dat; day)?

              • 4. Re: Auto Create Repeating Records Based On Day Of Week
                siplus

                in my example the function should be called with (date(12; 8; 2015); monday) and get as answer 2015-12-14, i.e. "give me the date of the next monday after the date 2015-12-8"

                • 5. Re: Auto Create Repeating Records Based On Day Of Week
                  skashanchi

                  Thanks. I understand this conceptually, but as I said I have never actually created a custom function. Could you please show me a screenshot of what the Edit Custom Function would look like?

                  • 6. Re: Auto Create Repeating Records Based On Day Of Week
                    Malcolm

                    If you haven't created a custom function yet, look around at what others have done. You may find that someone else has created a function that meets your needs. Visit a few of the sites that aggregate custom functions such as FileMaker Custom Functions or Brian Dunning's collection