5 Replies Latest reply on Nov 10, 2014 3:16 PM by philmodjunk

    Previous and Next Working Day Calculations

    RickDearden

      Title

      Previous and Next Working Day Calculations

      Post

      Hi guys,

      I have a layout with 3 portals on it.

      Portal 1 - Shows Tomorrows Jobs  which are Filtered By Calc Jobs_Logins::Date = ( Get ( CurrentDate ) + 1 )

      Portal 2 - Shows Todays Jobs which are Filtered by Jobs_Logins::Date = Get ( CurrentDate )

      Portal 3 - Shows Yesterdays Jobs which are Filtered by Jobs_Logins::Date = Get ( CurrentDate ) - 1
       

      This is all fine except on a Monday and Friday when the weekend days prevent Friday's and Monday's jobs from being displayed.

      What I'd like to do (on Friday only) is for Portal 1 show Saturday/Sunday/Monday's jobs (as though we're looking ahead to the next proper working day) and the reverse on Portal 3 for Monday only where it will show Sunday/Saturday/Friday's jobs. 

      I've had a play around with the DayOfWeek ( date ) calculation but can't get it to work. I think it may need a case calculation but they're beyond my grasp at the minute!!

      Any help would be much appreciated.

      Cheers,

      Rick.

        • 1. Re: Previous and Next Working Day Calculations
          philmodjunk

          And what if the previous, or next day is a holiday?

          You can adjust your calculation to compute the preceding Friday or following Monday, but this will not allow for all possible situations. You may want to set up a table where you have one record for every date that you are closed open. That approach can adjust for both weekend and holiday closures. And a Calendar style interface can be used to log the days that you are closed.

          Here's the calculations that you requested:

          Previous week day:

          Let ( T = Get ( CurrentDate ) ;
                   IF ( Dayof Week ( T ) = 2 ; T - 3 ; T - 1 )
                 ) // Let

          Next week day:

          Let ( T = Get ( CurrentDate ) ;
                   IF ( Dayof Week ( T ) = 6 ; T + 3 ; T + 1 )
                 ) // Let

          • 2. Re: Previous and Next Working Day Calculations
            RickDearden

            Many thanks Phil...

             

            The issue about holidays/etc isn't important as we're operating all year round. It's just for a summary view we need this feature (so we can see what's happening over the weekend and on Monday in one case and what happened on Friday and over the weekend in the other case).

            I've pasted those calcs into the portal Filter field but they don't give the results expected.

            (I had to get rid of the space in-between Dayof and Week too for it to validate the calculation.)

            It now looks like the calc is excluding anything that happens on Saturday/Sunday as well. What would be the calculation to include those days too?

            • 3. Re: Previous and Next Working Day Calculations
              philmodjunk

              I misread the requirements in your original post. I thought you were closed on the week end and needed to skip those days.

              Portal 1:

              Let ( [ T = Get ( CurrentDate ) ;
                         Dw = DayofWeek ( T )
                       ];
                       If ( Dw = 6 ; List ( T + 1 ; T + 2 ; T + 3 )  // if day is Friday, compute dates for Saturday/Sunday/Monday in a list
                            Dw = T + 1
                           ) // if
                      ) // Let

              P3:

              Let ( [ T = Get ( CurrentDate ) ;
                         Dw = DayofWeek ( T )
                       ];
                       If ( Dw = 2 ; List ( T - 3 ; T - 2 ; T - 1 ) ;  // if day is Monday , compute List of dates for Friday thru Sunday
                            Dw = T - 1
                           ) // if
                      ) // Let

              If you put these into unstored calculation fields, they can be used as match fields in the portal relationship. If you use them as part of a portal filter expression, you'll need to put them inside a FilterValues function call such as:

              Not IsEmpty ( FilterValues ( above calculaiton goes here ; PortalTable::DateField ) )

              • 4. Re: Previous and Next Working Day Calculations
                RickDearden

                Hi Phil,

                Thanks again for taking the time to look at this for me...

                I can't get the above calcs to work - just get an error "There are too few parameters in this calculation".

                I'm using it within the Filter Portal Calc Option Within 'Portal Setup' menu.

                There's a screenshot of my calculation in case I've done it wrong - hope you can see what's wrong!

                • 5. Re: Previous and Next Working Day Calculations
                  philmodjunk

                  // is a comment symbol that tells filemaker to ignore all text to the right of it on the same line. You need to move the text after //Let to its own row.