10 Replies Latest reply on May 22, 2012 2:26 PM by marksealey

    FMP 12 date formats - is iCal possible

    marksealey

      Title

      FMP 12 date formats - is iCal possible

      Post

      I am developing an event calendaring application in FMP 12.

      iCal date formatting seems the most appropriate for us.

      This is because we have many events with entirely irregluar - some would say irrational - recurrences… every third Friday for the first month and a half; then three days in a row; then every second Sunday except for the last week of a month with an 'R' in it etc.

      [Exaggeration - but potentially not much of one.]

      Is it possible to store events' dates in FMP with the same flexibility that iCal can, please? If so, how?

      TIA!

        • 1. Re: FMP 12 date formats - is iCal possible
          philmodjunk

          Please note that I am not a mac user and am unfamiliar with iCal.

          A date field's FORMAT in FileMaker can easily be specified to bet that of any number of different formats:

          5/21/12
          5-21-12
          05/21/12
          05/21/2012
          Tuesday
          May
          Tuesday, May 21, 2012

          are a few possible date formats you could specify for displaying a date after it is entered into the field. You select the field while in layout mode, click the data tab in the Inspector and then scroll to the bottom where you can click the calendar icon and specify a custom date format or select one of the predefined options.

          If you want to enter or import data in a variety of different formats and have FileMaker successfully interpet that as a date, things get more complex. The system settings can control whether you enter the day or the month first. You can enter dates with several different delimitters such as / and -. You can enter 2 or 4 digit years.

          But if you want to type or import May 21, 2012, then you'd need to set up a script or calculation that converts the text entered into a date.

          • 2. Re: FMP 12 date formats - is iCal possible
            marksealey

            Thanks, PhilModJunk!

            In fact, it's not the actual format of the date that I need to have control over - but the storage of a sequence of dates (aka our 'events') when they occur irregularly.

            That's why I mentioned iCal, which seemed the closest model widely used for storing an irregular sequence of dates/events as a single object.

            I'm sorry that I wasn't clearer: what we need to do is find a way to store multiple events which occur at irregular intervals as a single (or at least simpler) FMP record.

            • 3. Re: FMP 12 date formats - is iCal possible
              philmodjunk

              I wouldn't store multiple events in a single record. One event per record in a related table makes for a much more flexible structure in FileMaker.

              • 4. Re: FMP 12 date formats - is iCal possible
                marksealey

                Thanks. Yes; that's my insinct too.

                But the event calendar is going to be web-published. That means it has to take the irregularity of the dates into account and display which dates, for example, a given event is offered on.

                In that sense, there isn't really a one-to-one correspondence between events and FMP records.

                Nor could we simply have a FMP field with text representations of these irregular dates, since we also need to be able to search, say, for all events on any one date!

                • 5. Re: FMP 12 date formats - is iCal possible
                  philmodjunk

                  I don't see where web publishing will affect the "irregularity" of your dates at all. I'd still use one event date per record. There are a variety of ways that you can use to display those events, both in a list sorted by date and also in a calendar like grid by using portals to the table of events that filter by day of the week.

                  • 6. Re: FMP 12 date formats - is iCal possible
                    marksealey

                    Well, that's because we expect to provide a (web) interface for non-specialist users to enter this event data of theirs.

                    They will be 'Events' staff within our organization, not very familiar with the rules and good practices of data handling.

                    They are likely to have, say, an event about which all they know is that it takes place - for example - on June 1 (at 7pm), June 2 (at 7pm), June 3 (at 7:30 pm) then every other day until the end of June; and from July 1st on alternate Saturdays.

                    For their sake, we want to represent that mess (sorry, mass!) of irregular dates as a single entity - as it might be published on paper publicity material.

                    There will be times when an event might even recur 250 times in a year. That's why we don't want to have to have busy Events staff create 250 FMP records.

                    So what I'm looking for is a way for FMP internally to represent this irregular sequence of dates (and even times) in the same elegant way that iCal does  perhaps similar to the Perl module so to do.

                    Even if it has to be a separate, related, table…

                    • 7. Re: FMP 12 date formats - is iCal possible
                      philmodjunk

                      FMP would internally represent all 250  event dates as 250 individual records. You can use a single record in one table to represent the event itself and a related table of event dates linked to that event to log the individual dates. But that does not require that your staff enter the data 250 times. That's what scripts are for.

                      This is now a scripting issue not how the data would be stored in your database. The user can set up one date, specify the recurring pattern (next 5 days, next 6 working days, same day of month for next 5 months, yearly, bi-annual, etc and a script then generates the records needed in your database.

                      • 8. Re: FMP 12 date formats - is iCal possible
                        marksealey

                        PhilModJunk,

                        Thanks so much; that sounds like a really interesting solution. And one I'd like to work on :-)

                        So, in a much simplified case, the Event record as displayed on the web might consist of data from these two tables (plus many others, of course - but irrelevant here):

                        The Event table, with:

                        • event title
                        • event name
                        • event place
                        • event dates - entered, perhaps, with some dropdown values or even a calendar date-picker
                        • etc

                        The Dates table, with its number of records corresponding to the number of dates parsed by a script from the dates field of the 'event dates' field in the Events table

                        And it's a FMP script, or set of scripts, that populates the Dates table?

                        Yes?

                        • 9. Re: FMP 12 date formats - is iCal possible
                          philmodjunk

                          The Event table would be:

                          EventID--an auto-entered serial number or UUID
                          event title
                          event name
                          event place

                          The EventDate table would have:

                          EventID--number field linked to EventID in the Event table
                          EventDate

                          The dates in the EventDate table can be created both by hand and/or via a script or scripts. It's up to you the developer to design an interface and scripts that best meets the needs of your users.

                          • 10. Re: FMP 12 date formats - is iCal possible
                            marksealey

                            Yes. Thanks. All good. As always, your help very much appreciated!