1 2 Previous Next 17 Replies Latest reply on Nov 21, 2012 4:51 AM by davehob

    Dates, split by month

    davehob

      Given a list of dates (comma-separated), I need to create a separate list for each month. So, if the list contains (UK format) "17/9/12, 24/9/12, 23/10/12, 30/10/12", I need "17/9/12, 24/9/12" and "23/10/12, 30/10/12". There can be any number of dates.

       

      Any suggestions much appreciated. (I'm sure it must be simpler than I'm making it...)

       

      Thanks,

      Dave.

        • 1. Re: Dates, split by month
          comment

          Filemaker has an arsenal of tools for processing structured data. A comma separated list of dates is, as far Filemaker is concerned, just a meaningless string of text.

           

          OTOH, if your dates were individual records in a related table, it would be very easy to calculate the month of each, then sort and group them by the result.

           

           

          ---

          I should add that Filemaker has a limited toolbox for processing return-separated lists. However, what you require here exceeds that limit.

          • 2. Re: Dates, split by month
            Mike_Mitchell

            Perhaps a virtual list technique might be of some use.

             

            Mike

            • 3. Re: Dates, split by month
              RubenVanDenBoogaard

              Hi Dave,

               

              Herewith a sample file with a simple method, using a repeating global field to store the different month lists in.

               

              Best regards,

               

              Ruben van den Boogaard

              Infomatics Software

              ruben@infomatics.nl

              1 of 1 people found this helpful
              • 4. Re: Dates, split by month
                beverly

                While not directly a "function" to loop a list of values (return-separated), one would think you could still "loop" the list and push to variables ($mo1, $mo2, $mo3, etc.) Then when finished convert each variable back to comma. We don't know what Dave Hobson wants to do with all the lists, but once they are in variables...

                not tested, but something like:

                Set Variable ( $comma_list ; Substitute ( mylist ; [ " " ; "" ] ; [ "," ; "¶" ] ) )
                     // remove spaces, if any, then convert comma to return )
                Set Variable ( $i ; 1 // "index" for GetValue of $comma_list )
                Loop
                     Set Variable ( $month = Let ( 
                          [ $this_date = GetValue ( $comma_list ; $i )
                          ; $first_slash = Position ( $this_date ; "/" ; 1 ; 1 )
                          ; $second_slash = Position ( $this_date ; "/" ; 1 ; 2 )
                          ; $result = Middle ( $this_date ; $first_slash + 1 ; $second_slash - $first_slash - 1 ) // for dd/mm/yyyy
                          ; $US_result = Left ( $this_date; $first_slash - 1 ) // alternate for those who use mm/dd/yyyy
                          ]; $result 
                          ) )
                     Set Variable ( $the_months = Let ( 
                          [ $this_month = GetAsNumber ( $month ) // from above 
                          ; $mo1 = If ( $this_month = 1 ; $mo1 & $this_date & "¶" ; $mo1 ) 
                          ; $mo2 = If ( $this_month = 2 ; $mo2 & $this_date & "¶" ; $mo2 ) 
                          ; $mo3 = If ( $this_month = 3 ; $mo3 & $this_date & "¶" ; $mo3 ) 
                          ; $mo4 = If ( $this_month = 4 ; $mo4 & $this_date & "¶" ; $mo4 ) 
                          ; $mo5 = If ( $this_month = 5 ; $mo5 & $this_date & "¶" ; $mo5 ) 
                          ; $mo6 = If ( $this_month = 6 ; $mo6 & $this_date & "¶" ; $mo6 ) 
                          ; $mo7 = If ( $this_month = 7 ; $mo7 & $this_date & "¶" ; $mo7 ) 
                          ; $mo8 = If ( $this_month = 8 ; $mo8 & $this_date & "¶" ; $mo8 ) 
                          ; $mo9 = If ( $this_month = 9 ; $mo9 & $this_date & "¶" ; $mo9 ) 
                          ; $mo10 = If ( $this_month = 10 ; $mo10 & $this_date & "¶" ; $mo10 ) 
                          ; $mo11 = If ( $this_month = 11 ; $mo11 & $this_date & "¶" ; $mo11 ) 
                          ; $mo12 = If ( $this_month = 12 ; $mo12 & $this_date & "¶" ; $mo12 ) ;
                           $result2 = 1 // just a way to set the variable so we can see it in data viewer 
                          ]; $result2 
                          ) )     
                     Set Variable ( $i ; $i + 1 // increment )
                     Exit Loop if  ( $i > ValueCount ( $comma_list ) )
                End Loop
                # now do *something* with the variables just set. change the "¶" back to ", " & ??? or ???
                

                Beverly

                 

                I should add that Filemaker has a limited toolbox for processing return-separated lists. However, what you require here exceeds that limit.\

                 

                1 of 1 people found this helpful
                • 5. Re: Dates, split by month
                  comment

                  Mike, Ruben and Beverly:

                   

                  Your valiant efforts notwithstanding, I believe it would be much better for Dave to fix the leg than build a crutch.

                  • 6. Re: Dates, split by month
                    beverly

                    Mike, Ruben and Beverly:

                    Your valiant efforts notwithstanding, I believe it would be much better for Dave to fix the leg than build a crutch.

                     

                    +1 on that, but sometimes we cannot change the way we GET the data and must build many crutches.

                    I've built my business on being able to "parse" in FileMaker because there are so many ways to send and receive the data that just are not "normalized" in any sense of the word! You can have two systems that cannot talk to each other, but can use FileMaker as a "bridge" between them & do data manipulation (even pre-variable & pre-XML days). In this example need, the UK_date <> US_date can be a reason to build a crutch.

                     

                    Beverly

                    • 7. Re: Dates, split by month
                      davehob

                      Thanks, everyone, for your input. I wasn't actually aware that my leg was broken (!), but even now I'm beginning to feel twinges of pain. I think Beverley or Ruben's suggestions will work, but I don't really want to use crutches if i don't have to. Maybe I need to re-think things.

                       

                      The situation is a resource booking application for the charity where I work. The user enters a start date and end date, plus an indication whether they want the recurrence to be "same day each week", etc.  I then use a custom function (DatesInRange, from Brian Dunning's site, i think) to get the relevant dates into a list.  A script then creates the booking, i.e. a record in the "bookings" table, plus a set of related records in the "resource bookings" table, one for each date/resource.  It's been working fine like this for a while, but now the user wants to be able to say "split this big booking into more manageable chunks", i.e. a separate "booking" for each month.  Hence my decision to split out the dates by month before doing the writing of the records.

                       

                      So if I want to do this "properly" (and I do), maybe I need to be writing all the records first, and then manipulating the records (where the dates are dates, rather than text), as opposed to fiddling about with the text strings before writing?

                       

                      Thanks again for your help.

                       

                      Dave.

                      • 8. Re: Dates, split by month
                        comment

                        Dave Hobson wrote:

                         

                        maybe I need to be writing all the records first, and then manipulating the records (where the dates are dates, rather than text), as opposed to fiddling about with the text strings before writing?

                         

                        That would be my opinion, yes. Not only are the dates dates, there is also only one date per record to deal with.

                         

                         

                         

                        Dave Hobson wrote:

                         

                        now the user wants to be able to say "split this big booking into more manageable chunks", i.e. a separate "booking" for each month. 

                         

                        What exactly does "split" mean in this context? It should be very easy calculate the month (actually, month/year) of each date, and either find only records in a given month or group the records by month.

                        • 9. Re: Dates, split by month
                          davehob

                          Michael,

                           

                          Re. the "split", if the user selects a range of, say, 12 dates spanning 3 months, s/he may want to create 3 separate bookings, I.e. one for each month, rather than one booking for all 12 dates.  So booking no. 1 will have 4 related "resource booking" recs, likewise bookings 2 and 3.  The reason they want to have it this way is because the finance people want to invoice the bookers on a monthly basis, which is hard for them to do when bookings span more than one month.

                           

                          Thanks again for your input.

                           

                          Dave.

                          • 10. Re: Dates, split by month
                            comment

                            I am not sure I fully understand the distinction between a "booking"  and a "resource booking". It seems like "resource booking" is the one with an actual date, while "booking" is merely a wrapper for several "resource bookings" created at the same time - is that correct?

                             

                            If so, I don't see why the finance people couldn't invoice the bookers on the basis of "resource bookings" falling in a given month (and ignore the "bookings" altogether).

                            • 11. Re: Dates, split by month
                              davehob

                              Michael,

                               

                              I think you have uncovered a flaw in the data structure.  A BOOKING actually consists of one or more SESSIONS.  A session has a date, start time, end time, etc.  Each SESSION has one or more RESOURCE BOOKINGS, ie Resource Bookings is the join table between sessions and resources.   This structure was kind of imposed on me (am I sounding defensive yet?...) by the fact that the system evolved from a "training course" system, in which a "course" could consist of one or many sessions.

                               

                              I would be very please to be advised how it should be done, as I'm seriously considering re-writing the thing, and would love to get it right next time around.

                               

                              Dave.

                              • 12. Re: Dates, split by month
                                comment

                                Could you give us a brief description of the real-life things being tracked here? Those terms are not exactly self-explanatory.

                                • 13. Re: Dates, split by month
                                  davehob

                                  Michael,

                                   

                                  Thanks for sticking with this.

                                   

                                  A RESOURCE is, for example, a room within the organisation, or an equipment item (laptops, data projectors, etc.), ie things that can't be double-booked.

                                   

                                  So a BOOKING (or "programme") may have one or many Sessions, and a SESSION (with a date and time range) may have many RESOURCE BOOKINGS.

                                   

                                  So, back to the start, when user creates a BOOKING, s/he specifies the range of dates (with each date becoming a Session within the Booking), and the various rooms/equipment items required. The "Add Booking" script then creates a Session record for each date, and, for each Session,  a Resource Booking record for each resource specified (as long as the resource is available - there's availability checking each time a Resource Booking rec is created).

                                   

                                  And it's the date range at the start that I wanted to break down into a subset of dates, by month.

                                   

                                  I hope that makes it clearer?

                                   

                                  Dave.

                                  • 14. Re: Dates, split by month
                                    comment

                                    I see (hopefully). Although strictly speaking, the Sessions table is redundant, it could make sense to have such wrapper - for example, when you want to modify the date of a single session.

                                     

                                    Now, since both Sessions and ResourceBookings have a date attribute (if I understand correctly), what's stopping you from using either one of them as the basis for monthly invoicing?

                                    1 2 Previous Next