11 Replies Latest reply on Jan 3, 2017 4:33 PM by fmpdude

    Get Last Friday Of Year

    fmpdude

      I had a need to know when the last Friday of the year was so I could dynamically update a label.


      Below is the LET function I created and wanted to share it in case it might help someone else.

       

      The basic idea is to compute the offset from the end of the year. I realize there are other ways to do this, possibly easier, but this is how I did it.


      (Tested this logic with the following test cases, but there could still be a bug:

       

      Date(12 ; 31 ; 2013 ) - mod (DayOfWeek(Date(12 ;31 ; 2013)+1) ; 7) = 12/27/2013

      Date(12 ; 31 ; 2014 ) - mod (DayOfWeek(Date(12 ;31 ; 2014 )+1) ; 7) = 12/26/2014

      Date(12 ; 31 ; 2015 ) - mod (DayOfWeek(Date(12 ;31 ; 2015 )+1) ; 7) = 12/25/2015

      Date(12 ; 31 ; 2016 ) - mod (DayOfWeek(Date(12 ;31 ; 2016 )+1) ; 7) = 12/30/2016

      Date(12 ; 31 ; 2017 ) - mod (DayOfWeek(Date(12 ;31 ; 2017 )+1) ; 7) = 12/29/2017

      Date(12 ; 31 ; 2018 ) - mod (DayOfWeek(Date(12 ;31 ; 2018 )+1) ; 7) = 12/28/2018

      Date(12 ; 31 ; 2019 ) - mod (DayOfWeek(Date(12 ;31 ; 2019 )+1) ; 7)  = 12/27/2019

      Date(12 ; 31 ; 2021 ) - mod (DayOfWeek(Date(12 ;31 ; 2021)+1) ; 7)  = 12/31/2021

      Date(12 ; 31 ; 2022 ) - mod (DayOfWeek(Date(12 ;31 ; 2022)+1) ; 7)  = 12/30/2022

      )

       

      ------------------

       

      LET (

      [

         lastFridayOfYear = Date(12 ; 31 ; Year(checkdate) ) - Mod (DayOfWeek(Date(12 ;31 ; Year(checkdate) )+1) ; 7)

      ] ;

      lastFridayOfYear

      )

       

      Simple but useful.

        • 1. Re: Get Last Friday Of Year
          mikebeargie

          If you're only doing one line in a let statement, it's really not necessary to use a let statement.

           

          So this:

          LET (

          [

             lastFridayOfYear = Date(12 ; 31 ; Year(checkdate) ) - Mod (DayOfWeek(Date(12 ;31 ; Year(checkdate) )+1) ; 7)

          ] ;

             lastFridayOfYear

          )

          Could be much simpler as:

          Date(12 ; 31 ; Year(checkdate) ) - Mod (DayOfWeek(Date(12 ;31 ; Year(checkdate) )+1) ; 7)

           

          Just a personal preference, but after working with other coding languages, single-line functions start to make a lot more sense. Usually I'll use Let when I want to "explode" a more complicated function out into separate parts. This one makes sense to me though as single-line.

          • 2. Re: Get Last Friday Of Year
            fmpdude

            Hey Mike. Thanks for chiming in!

             

            Since I want to call this one-liner from multiple places, it made sense to make it a function even though, I agree, it's kinda silly. Wouldn't want to have to copy and paste this or remember how to do it again (a couple head banging hours).

             

            I also enhanced this to include any day in the last week of the year (yeah...two parameters). I can send you that if you would like it.

             

            Being a Java guy myself, I always try to encapsulate as much as FMP will allow.

             

            Thanks again for your posting.

            • 3. Re: Get Last Friday Of Year
              philmodjunk

              Your response to Mike has me scratching my head in puzzlement. How does enclosing your expression inside a Let function that is not actually used help you do that?

               

              If this is something you expect to use frequently, set up a custom function for it, but again, there's no obvious need for Let in the current way that you've written the expression.

              • 4. Re: Get Last Friday Of Year
                mikebeargie

                I believe he was stating that he was using the Let() function because it IS setup as a custom function (or at least I thought that was implied based on the parameter name in the function itself). No worries either way, I was just sharing my way of doing things.

                • 5. Re: Get Last Friday Of Year
                  keywords

                  Re: "after working with other coding languages, single-line functions start to make a lot more sense"—

                  That may suit you Mike, but as one who doesn't work with other coding languages I use FM's Let ( ) function extensively to help me break down the FM logic I am trying to implement into human-readable elements. In this example, the addition of the internal variable name, lastFridayOfTheYear, helps my poor old head by adding a name to the result the calc (which is by its nature expressed in somewhat mystifying terms) is delivering.

                  Furthermore, one of my guiding principles when I use the Let ( ) function is to avoid making multiple calls to the same field in a database, so if I were implementing this myself I would add as the first variable the term checkdate, referencing it to a date field or to Get ( CurrentDate ), thus:

                  Let (

                  [

                      checkdate = <dateField>  

                    ; lastFridayOfYear = Date(12 ; 31 ; Year(checkdate) ) - Mod (DayOfWeek(Date(12 ;31 ; Year(checkdate) )+1) ; 7)

                  ] ;

                     lastFridayOfYear

                  )

                  • 6. Re: Get Last Friday Of Year
                    erolst

                    keywords wrote:

                    Furthermore, one of my guiding principles when I use the Let ( ) function is to avoid making multiple calls to the same field in a database, so if I were implementing this myself I would add as the first variable the term checkdate, referencing it to a date field or to Get ( CurrentDate ), thus:

                    Let (

                    [

                    checkdate = <dateField>

                    ; lastFridayOfYear = Date(12 ; 31 ; Year(checkdate) ) - Mod (DayOfWeek(Date(12 ;31 ; Year(checkdate) )+1) ; 7)

                    ] ;

                    lastFridayOfYear

                    )

                    But you don't have to stop there:

                     

                    Let (

                      cd = Date ( 12 ; 31 ; Year ( checkDate ) ) ;

                      cd - Mod ( DayOfWeek ( cd + 1 ) ; 7 )

                    )

                    • 7. Re: Get Last Friday Of Year
                      fmpdude

                      There you are...

                       

                      The "checkdate" field is on a layout so sending it to a Let function means a "variable" from the interface, not a trip to the database, right? That's how it works in Web apps, but I'm really not sure about FMP.  Even in Visual FoxPro, you have  buffered form values. Thus, these values are not database values until you commit them one way or another (depending on which of the 5 buffering methods you're using.).

                       

                      Again, I learn a lot from you guys on this forum, and you are serious FMP experts, so I'll wait to hear back on how FMP buffers, if at all, layout field values.

                       

                      ------------------

                       

                      In any case, since I now realize that I have a couple FMP solutions that require this type of logic, rather than copying the CF to the two solutions and trying to keep them in sync if any changes, I just wrote a quick REST service in about 30 minutes this afternoon after Mike's posting reminded me. Plus, I will keep that code in GitHub and share it, via invitation, with other devs who might want it or even change it.

                       

                      FMP's INSERT FROM URL to the rescue!

                       

                      Java's support for dates, starting in JDK8, is beyond amazing.

                       

                      You can basically do anything needed with dates, including building custom Temporal adjusters, with the built-in API.  http://docs.oracle.com/javase/tutorial/datetime/iso/adjusters.html

                       

                      For a relevant example, assuming you have a "renewalDate" in a variable (say, 12/31/16), you can easily get the last day of the year (or any month, just as easily) using something built in like this:

                       

                      System.out.println("Last Friday in Month: " +renewalDate.with(TemporalAdjusters.lastInMonth(DayOfWeek.FRIDAY)))

                       

                      --

                      Prints: Last Friday in Month: 2016-12-30

                       

                      --

                       

                      Since I'm an API type person, I usually let others create the front end (be it FMP -- or whatever front end from whatever "client" that  wants to use my API). My front-ends are OK, but real front-end developers have better-honed skills for that work.

                       

                      FMP rocks for the current use I have for it.

                       

                      ---

                       

                      Thanks very much for your reply!

                      • 8. Re: Get Last Friday Of Year
                        fmpdude

                        Thanks again.

                         

                        I appreciated your thoughtful reply.

                        • 9. Re: Get Last Friday Of Year
                          philmodjunk
                          The "checkdate" field is on a layout so sending it to a Let function means a "variable" from the interface, not a trip to the database, right?

                          Not necessarily. The problem is that in order to have something where the user can enter or edit the data used by such a function, FileMaker requires you to use a field. "buffered form values", if I understand the term aren't really something that currently exists in a FileMaker layout. So unless you have a system such as a script or Let inside a calculation on the forum that automatically assigns the desired value, you have to declare a field in a table--might be a global field, but a field all the same.

                          • 10. Re: Get Last Friday Of Year
                            fmpdude

                            Ahhh, I see what you're saying. Yes, you're right. A LET isn't required.

                             

                            I had started with more lines in the LET, but ended up removing all but that one.

                             

                            Since I don't use LETs that often, I left in in the CF just for practice. Hope there isn't much overhead.

                             

                            Thanks Phil.

                            • 11. Re: Get Last Friday Of Year
                              fmpdude

                              OK, sounds good. That is what I'm doing. 

                               

                              The actual check_date that comes into the CF is a field on a layout with the date.

                               

                              Thanks Phil.