6 Replies Latest reply on Jun 17, 2014 9:49 AM by jlisburn

    Format dates for multi day events

    jlisburn

      Situation:

       

      • Quoting a client, giving possible dates to provide service.

       

      • Using a Repeating Date field for this as we may want 1-5 dates on each quote

       

      • Some of our Products are multi-day events, they have a stated DurationDays

       

      • Need to re-display these multi-day events later in the busines process: EG Jun 6-8, Aug 15-17 etc outside of FileMaker, so layout formatting cannot be used. I need to maipulate the data (idealy by calc and not script)


      Problem lies in showing events that span months - Jun30-Jul 1, get sdisplay as June 30-31

       

      Seems I'm going to need a very complex calc, or custom function but I can't determine which/how to go about it.

        • 1. Re: Format dates for multi day events
          beverly

          j, For these kinds of things, I have an "events" table with Start, End and/or Duration. These fields can be used as needed and "related" records can be created by script to link to the event. Then you have the List Items (actual related dates) and the Event fields for reporting purposes.  I would not use Repeating fields for this.

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Format dates for multi day events
            erolst

            Beverly's right; don't use repeating fields for business data. They have some great uses, but this is not one of them.

             

            Regardless of how you actually store your dates, try this calculation to get a string result; could possibly be made less complex, but seems to work:

             

            Let ( [

             

              dt1 = Date( 6 ; 12 ; 2014 ) ; // your first date

              dt2 = date ( 7 ; 13 ; 2014 ) ; // your second date …

             

              y1 = Year ( dt1 ) ;

              y2 = Year ( dt2 ) ;

              y1new = Case ( y1 ≠ y2 ; ", " & y1 ) ;

             

              m1 = MonthName ( dt1 ) ;

              m2 = MonthName ( dt2 ) ;

              m2new = Case ( y1 ≠ y2 or m1 ≠ m2 ; m2 ) ;

             

              res = List ( m1 ; day ( dt1 ) ; y1new ; "-" ; m2new ; day ( dt2 ) ; ", " & y2 )

              ] ;

             

              Substitute ( res ; ¶ ; " " )

             

            )

             

            returns for the sample data

             

            June 12 - July 13 , 2014

            • 3. Re: Format dates for multi day events
              jlisburn

              Thanks

               

              Beverly - these are just quotes so I don't want at this stage to get into creating events - I will if the quotes are accepted - I just don't want the overhead of an extra table to hold proposed dates

               

              erolst - Thanks, that's kind of what I was thinking was necessary. I have to work on the dt2 as it doesn't exist as a field but it caculated based on the duration of the product. (either dt1 +1 or dt1 + 2)

               

              I'll try it out and report back

              • 4. Re: Format dates for multi day events
                flybynight

                You should just be able to change 1 line of erolst's calc to:

                 

                dt2 = dt1 + Duration ; // your second date

                 

                HTH,

                -Shawn

                1 of 1 people found this helpful
                • 5. Re: Format dates for multi day events
                  erolst

                  jlisburn wrote:

                  Thanks, that's kind of what I was thinking was necessary.

                   

                  Really? Didn't you mention a very complex calc …?

                  • 6. Re: Format dates for multi day events
                    jlisburn

                    Hi,

                     

                    Just for the record here's what gave me what I wanted. You'll see there's a lots of extra to cope with the Repeating fields (and if they should be blank) - so Beverly gets a for the repeating fields call

                     

                    I had to break up my dates to get them in the Date function, but it got me where I wanrted to go:

                     

                    Let ( [

                     

                     

                    dur = If (DurationDays = 1.5 ; 1 ; DurationDays - 1) ;

                     

                     

                      d1 = Date( Left( GetRepetition( Dates ; 1) ; 2 ) ;

                    Middle( GetRepetition( Dates ; 1) ; 4 ; 2 ) ;

                    Right( GetRepetition( Dates ; 1) ; 4 ) ) ; // your first Rep date

                      d2 = d1 + dur ; // your second date …

                     

                     

                     

                      d3 = Date( Left( GetRepetition( QuoteItems::Dates ; 2 ) ; 2 ) ;

                    Middle( GetRepetition( Dates ; 2 ) ; 4 ; 2 ) ;

                    Right( GetRepetition( Dates ; 2 ) ; 4 ) ) ; // your second Rep date

                      d4 = d3 + dur ; // your second date …

                     

                     

                     

                      d5 = Date( Left( GetRepetition( Dates ; 3 ) ; 2 ) ;

                    Middle( GetRepetition( Dates ; 3 ) ; 4 ; 2 ) ;

                    Right( GetRepetition( Dates ; 3 ) ; 4 ) ) ; // your third Rep Date

                      d6 = d5 + dur ; // your second date …

                     

                     

                      m1 = Left( MonthName ( d1 ) ; 3) ;

                      m2 = Left( MonthName ( d2 ) ; 3) ;

                      m2new = Case ( m1 ≠ m2 ; m2 ) ;

                     

                      m3 = Left( MonthName ( d3 ) ; 3) ;

                      m4 = Left( MonthName ( d4 ) ; 3) ;

                      m4new = Case ( m3 ≠ m4 ; m4 ) ;

                     

                     

                      m5 = Left( MonthName ( d5 ) ; 3) ;

                      m6 = Left( MonthName ( d6 ) ; 3) ;

                      m6new = Case ( m5 ≠ m6 ; m6 ) ;

                     

                     

                      res = List ( m1 ; Day( d1 ) ; "-" ; m2new ; Day( d2 ) ; "|" )&

                    If( not IsEmpty ( GetRepetition( QuoteItems::Dates ; 2 )) ; List( m3 ; Day( d3 ) ; "-" ; m4new ; Day( d4 ) ; "|" ) ; "" )&

                    If( not IsEmpty ( GetRepetition( QuoteItems::Dates ; 2 )) ; List( m5 ; Day( d5 ) ; "-" ; m6new ; Day( d6 )) ; "" )

                      ] ;

                     

                      Substitute ( res ; [¶ ; " "] ;["|" ; "   "] )

                     

                    )

                     

                     

                    Thanks everybody. I've learn't a lot (about Let)

                     

                    J