1 2 Previous Next 20 Replies Latest reply on Jun 19, 2016 9:26 PM by greatgrey

    Calculation Help

    PaulCarroll

      Hi

       

      Can anyone help with a calculation to return the date for the Monday of the current week?

       

      I have a field that needs to alway show the date for the Monday of each week.

       

      Many thanks.

       

      Paul.

        • 1. Re: Calculation Help
          mikebeargie

          FileMaker Custom Function:FirstDayThisWeek ( StartDay )

           

          Use would be FirstDayThisWeek(2) to get the date of Monday this week.

          • 2. Re: Calculation Help
            PaulCarroll

            Hi Mike

             

            Many thanks for this.

             

            My level of understanding is still a way off where I would like it to be - I copied the formula, pasted into a custom function and referenced the function from the field calculation and it returns 0 December 0000.

             

            Obviously doing something wrong!!

             

            Do I copy all the contained formula as below;

             

            /* FirstDayThisWeek ( StartDay ) */

             

             

            /*StartDay is 1 for Sunday, 2 for Monday, etc.*/

             

             

            Get ( CurrentDate ) - ( DayOfWeek ( Get ( CurrentDate ) ) - StartDay )

             

            And if so, where would I put the 2 for Monday?

             

            Sorry, this makes me feel quite dim!!

             

            Thanks

            • 3. Re: Calculation Help
              erolst

              If you want a bit more style:

               

              // DateOfWeekdayThisWeek ( nameOfDay )

              // argument in the format "Monday", "Mon" or "Mo"

              Let ( [

                listLong = "Monday¶Tuesday¶Wednesday¶Thursday¶Friday¶Saturday¶Sunday" ;

                listMedium = "Mon¶Tue¶Wed¶Thu¶Fri¶Sat¶Sun" ;

                listShort = "Mo¶Tu¶We¶Th¶Fr¶Sa¶Su" ;

               

                l = Length ( nameOfDay ) ;

                theList = Case ( l = 2 ; listShort ; l = 3 ; listMedium ; listLong ) ;

               

                pos = ValueCount ( Left ( theList ; Position ( ¶ & theList & ¶ ; ¶ & nameOfDay & ¶ ; 1 ; 1 ) ) ) ;

                cd = Get ( CurrentDate ) ;

                cPos = ValueCount ( Left ( listLong ; Position ( ¶ & listLong & ¶ ; ¶ & DayName ( cd ) & ¶ ; 1 ; 1 ) ) )

                ] ;

                Case ( pos ; cd - ( cPos - pos ) )

              )

              • 4. Re: Calculation Help
                PaulCarroll

                erolst,

                 

                Now I am in genuine pain!!

                 

                Just makes me realise how far I have to go in getting a deeper understanding of all this!!

                 

                If only I knew what to do with all that!

                 

                Mike,

                 

                I removed the /* sections of the function and it now seems to be working.

                 

                Thanks guys.

                • 5. Re: Calculation Help
                  mikebeargie

                  It's a custom function, as in File > Manage > Custom Functions. You can only manage custom functions in filemaker pro advanced.

                   

                  You would add "startDate" as your parameter. Then you would have that function available anywhere you want inside filemaker.

                   

                  Here's a webinar that has full training on custom functions. It's a powerful feature of filemaker that's a must-learn for good development. That site I linked to opens up to hundreds of custom functions that augment the functionality of filemaker.

                  https://www.youtube.com/watch?v=CiwSYJAYOkg

                  • 6. Re: Calculation Help
                    erolst

                    Mike Beargie wrote:

                     

                    It's a custom function, as in File > Manage > Custom Functions. You can only manage custom functions in filemaker pro advanced.

                     

                    You would add "startDate" as your parameter. Then you would have that function available anywhere you want inside filemaker.

                    Paul –

                     

                    It is correct what Mike says, but if the Custom Function in question isn't recursive, you can still utilize the function's logic.

                     

                    Put it into a calculation field and de-parametrize it, i.e. replace the argument(s) with hard-coded values.

                     

                    e.g. in my function, simply replace the two occurrences of nameOfDay with "Monday".

                    • 7. Re: Calculation Help
                      dtcgnet

                      Not as elegant, but workable:

                       

                      Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 2

                       

                      That would always get you to the date for the 2nd day of the week in the current week.

                      • 8. Re: Calculation Help
                        PaulCarroll

                        Thanks erolst

                         

                        Think I have it working OK now - at least I am now seeing the date for Monday showing in the relevant field - suppose I will have to wait and see if it updates next week?

                         

                        And thanks to Mike and dtcgnet.

                         

                        Just hope this fixes my second issue now......

                         

                        Thanks guys

                        • 9. Re: Calculation Help
                          mikebeargie

                          you can test for future dates by using the data viewer under the tools menu in FMP advanced.

                           

                          get(currentdate) + 7

                          will let you test for next week.

                          • 10. Re: Calculation Help
                            erolst

                            PaulCarroll wrote:

                            suppose I will have to wait and see if it updates next week?

                            On that topic: you need to set the calculation field to unstored, or it won't update (ever).

                             

                            Also on that note: just for fun, change

                             

                              cd = Get ( CurrentDate ) ; to: cd = Get ( CurrentDate ) + 7 ;

                             

                            so you can save yourself the thrill of waiting …

                             

                            EDIT: Mike was faster . Yes, using DataViewer is a great idea – if you do have FM Advanced.

                            • 11. Re: Calculation Help
                              PaulCarroll

                              Thanks guys.

                               

                              I do have FM Advanced so will try the DataViewer.

                               

                              One last thing if I may - just to make sure I am on the right track..

                               

                              I have a portal set up with the following fields - Start Date, Monday Tuesday Wednesday, Thursday, Friday, Saturday, Sunday, End Date.

                               

                              The idea is that we can enter our staff availability for each week into the portal.

                               

                              I have then created duplicates of these fields outside the portal and set them all as calculations. Start Date is set to the calculation we have been discussing and the days are set to reference the original days in the portal.

                               

                              My thinking is that the second set (current week) will always pull in the current weeks availability from the portal fields.

                               

                              Is this correct?

                               

                              Thanks again.

                              • 12. Re: Calculation Help
                                erolst

                                You mean something like in the attached file?

                                • 13. Re: Calculation Help
                                  PaulCarroll

                                  Wow! Thanks for taking the time to put that together!

                                   

                                  Yes, thats the kind of functionality I am looking for.

                                   

                                  I have gone about it a different way (probably the wrong one at that) and have created a second portal on the same layout with matching fields except for my start date calculation we discussed earlier.

                                   

                                  I have set up the second portal with the following filter;

                                   

                                  Div ( Time Off::Start Date - 1 ; 7 ) = Div ( Get ( CurrentDate ) - 1 ; 7 )

                                   

                                  This seems to reflect the correct week on this basis - if I change the value of a particular day in the main portal the values in the second one change accordingly.

                                   

                                  Not sure if this will work when the week changes to a new one (not quite got my head around the DataViewer tool as yet.

                                   

                                  Does this look correct to you or am I barking up the wrong tree?

                                   

                                  My main issue - assuming the above will work - is getting the current weeks values to display on a second layout (list view) that shows all staff and their current availability.

                                   

                                  I have probably gone about this in entirely the wrong way. The layouts I am using are based on the personnel starter solution.

                                  • 14. Re: Calculation Help
                                    erolst

                                    PaulCarroll wrote:

                                     

                                    Does this look correct to you or am I barking up the wrong tree?

                                     

                                    My main issue - assuming the above will work - is getting the current weeks values to display on a second layout (list view) that shows all staff and their current availability.

                                    No, this looks OK (as far as I'm qualified to judge from a distance). The main advantage I can see in using a unified Monday date in both tables (as in my sample file) is that you could base a relationship on those fields, which would work much faster than a portal filter in filtering larger data sets. 

                                     

                                    If the above works, you would only need

                                     

                                    Go to Related Record [ matching only; target layout: YourDisplayLayout … ]

                                     

                                    to get your list.

                                    1 2 Previous Next