6 Replies Latest reply on Feb 25, 2010 12:29 PM by Xaqtly

    How to exclude weekend days from a 5-day report?

    Xaqtly

      Title

      How to exclude weekend days from a 5-day report?

      Post

      I'm trying to create a script that goes to a specific layout, does a search using today's date then exports the results to an email. Then I want it to do the same thing for the next 4 working days, so if I run it on Tuesday I want it to get results for Tuesday, Wednesday, Thursday, Friday and then next Monday. The line I'm using to do the search goes like this:

       

       

       

      Insert Calculated Result [ Elements05:: Date 3 Item Due; Get ( CurrentDate ) ]

       

      So I enter Find Mode, that line inserts the current date into the date field, and then it performs the find and emails the results. That part works fine. Then I can duplicate that line and change it so that it says this:

       

       

      Insert Calculated Result [ Elements05:: Date 3 Item Due; Get ( CurrentDate ) + 1 ]

       

      to get the results for the next day, and that works fine too. And same with adding + 2, and so on. What I haven't been able to figure out is how to get it to skip weekend days. If it's Thursday and I run the + 2 line, it will return results for Saturday, but what I want is for it to return results from the next Monday. I'm not good at calculations in FMP, so I was hoping somebody could point me in the right direction. Looking through help hasn't really given me any ideas, only explanations of other functions like DayOfWeek that might come in handy. Any ideas?  Thanks for any advice.

       

       I forgot to add my details; I'm using Filemaker Pro 10 in OS X 10.6.2.

        • 1. Re: How to exclude weekend days from a 5-day report?
          philmodjunk
            

          You can use variables in a loop for this and set field is a better option than Insert calculated result.

           

          Loop

            Enter Find Mode[]

            Set Field [ Elements05:: Date 3 Item Due ; Let ( today = Get ( CurrentDate ) ; today + case ( DayofWeek (today) = 1 ; 1 ; DayofWeek (today) = 7 ; 2 ) ) + $Day  ]

            Set Error capture [on]

            Perform find []

            #do the stuff you need to do with this found set here...

           

           

            Set variable [ $Day ; $Day + 1 ]

            Exit Loop If [ $Day = 5 ]

          End Loop

           

          • 2. Re: How to exclude weekend days from a 5-day report?
            Xaqtly
              

            Thanks very much for that. I've plugged it into my script, and it appears to be doing the job. I need to do a bit more testing and I'll come back and post again but for now that really seems to have worked so thanks very much.

             

            • 3. Re: How to exclude weekend days from a 5-day report?
              Xaqtly
                

              Okay, I've found something that isn't working and I was hoping to get your help again. So the looping part works and it stops after 5 iterations, but it doesn't appear to be skipping the weekend days. The script just sends an email for each day's worth of data, and when I run it I get 5 emails, but two of them have no date in the subject line and contain no data - and the three that do are from today (Thursday), tomorrow and Monday. The five should be today, tomorrow, Monday, Tuesday and Wednesday. Here's the whole script I'm using.

               

               

              Show All Records

              Loop

              Enter Find Mode [ ] 

              Clear [ Elements05:: Date 1 Item Due ] [ Select ]

              Set Field [ Elements05:: Date 3 Item Due; Let ( today = Get ( CurrentDate ) ; today + Case ( DayOfWeek (today) = 1 ; 1 ; DayOfWeek (today) = 7 ; 2 ) ) + $Day ]

              Set Error Capture [ On ]

              Perform Find [ ]

              Send Mail [ Send via E-mail Client; Subject: "Daily Report Show for " & Elements05:: Date 3 Item Due; Message: "SHOW: " & Elements05:: Total Ads BB Coll Summ & " - " & Elements05::Total Ads & " ads, " & Elements05::Total BB & " BBs, " & Elements05::Total Coll & " Coll" & ¶ & "Image Reviews: " & Elements05::Total IR & ¶ & "Graphic Requests: " & Elements05::Total GR & ¶ & ¶ & "TOTAL: " & Elements05::Total Daily ]

              Set Variable [ $Day; Value:$Day + 1 ]

              Exit Loop If [ $Day = 5 ]

              End Loop

               

               Thanks for any help!

               

              • 4. Re: How to exclude weekend days from a 5-day report?
                philmodjunk
                  

                Yeah, that's a script I should have tested before posting it. :smileysad:

                 

                Unless someone beats me to it with a correctly working script, I'll post back later with a different script.

                • 5. Re: How to exclude weekend days from a 5-day report?
                  philmodjunk
                    

                  Try this:

                   

                  Set Variable [ $Date; Value:Get ( CurrentDate ) ]

                  Loop

                    Enter Find Mode [ ]

                    Set Error Capture [ On ]

                    Set Field [ SkipDateDemo:: Date; $Date ]

                    Perform Find [ ]

                    #Do what you need here to send your email with this found set

                   

                   

                    Set Variable [ $Day; Value:$Day + 1 ]

                    Exit Loop If [ $Day = 5 ]

                    Set Variable [ $Date; Value:Let ( day = DayOfWeek ( $Date ) ; $Date + Case ( day = 7 ; 1 ; day = 6 ; 2 ; 0 ) ) + 1 ]

                  End Loop

                  • 6. Re: How to exclude weekend days from a 5-day report?
                    Xaqtly
                       Perfect. Thanks so much for helping me out, I really appreciate it.