1 2 3 Previous Next 37 Replies Latest reply on Jan 12, 2016 3:41 AM by disabled_morkus

    A month from today?

      Is there an intelligent way to have FileMaker tell me what one month from today is?


      If the $renewDate is 1/31/16 and I issue this FMP command:


      Date( Month ($renewDate) +1 ; Day($renewDate ) ; Year ($renewDate ) )


      FM gives me: 3/2/16.


      Not what I want.


      I really want: Date( Month($renewDate + 1) ; ; ) to be 2/28/16, but this syntax does not work.




      I did a workaround to get the right answer, but the basic issue is I don't know the function, if FM has one, that will just add one month to the current date.


      In other environments, this calculation would be 2/28/16, which IS what I want, not 3/2/16.


      Maybe I'm missing a FM function???


      Thanks in advance,


      - m

        • 1. Re: A month from today?

          how long is a month ? In your opinion, it's 4 weeks. OK, then just use


          Date( Month ($renewDate) ; 28 + Day($renewDate ) ; Year ($renewDate ) )

          • 2. Re: A month from today?

            What's happening is that FM really is adding a month. The same day (31st) next month is 2/31, which of course isn't a real date, but FM resolves that as the 31st day of February... which is 3/2.


            Believe it or not, the way FM makes that decision turns out to be beneficial more often than not. For example, if you're in November and add three months to a date, it'll come up with the 14th month of this year... which is Febuarary of the following year. This keeps us from having to do a bunch of "If ( month > 12..." calcs. It also allows you to use Date ( 3 ; 0 ; 2016 ) to get the last day of Feburary.


            What you really want to do is add a month, but adjust to the end of the target month if that would put you beyond the target calendar month.


            This'd look like this:


            Let ( [

            $renewdate = GetAsDate ( "1/3/16" )

            ; monthstoadd = 1

            ; target = Date ( Month ( $renewdate ) + monthstoadd  ; Day ( $renewdate )  ; Year ( $renewdate ) ) // this day next month

            ; monthend = Date ( Month ( $renewdate ) + monthstoadd + 1 ; 0 ; Year ( $renewdate ) ) // the end of next month

            ] ;


            GetAsDate ( Min ( target ; monthend ) )





            Chris Cain


            • 3. Re: A month from today?

              Date( Month ($renewDate) + 1 ; Day($renewDate ) ; Year ($renewDate ) )

              • 4. Re: A month from today?

                Get (current date) + 30

                • 5. Re: A month from today?

                  Very nice.

                  Perfect, in fact.



                  - m

                  • 6. Re: A month from today?

                    You seems forgot that 2016 is leap year.

                    • 7. Re: A month from today?

                      Yes, my original posting was not exactly correct. I should have said "2/29/2016".


                      Thanks for pointing this out.


                      -- m

                      • 8. Re: A month from today?

                        I actually didn't realize that at first, either. That's the main reason the "0th" day is so useful!


                        Chris Cain


                        • 9. Re: A month from today?

                          I thought your "MIN" function approach with the two dates was extremely insightful. I probably wouldn't have thought about using MIN unless I was examining a whole bunch of date pairs. I'm also not that comfortable using LET ... yet.


                          How did you come up with that date-MIN() approach idea? Was this an issue you ran into before?


                          I had basically done the same thing with IF statements in the script, but your approach was better since I was able to remove the IF-THEN logic and just have a single call to the a custom function.


                          The custom function accepts two parameters: (1) the number of months, positive or negative, and (2) the date to add or subtract those months from. I could send that function to you, but, based on your posting, you probably already have it implemented somewhere.


                          Look forward to hearing back.


                          - m

                          • 10. Re: A month from today?

                            You can use IF-THEN logic in function.


                            Let ( [

                              theDay = Day ( baseDate ) ;

                              tempDate = Date ( Month ( baseDate ) + months ; theDay ; Year ( baseDate ) ) ;

                              resultedDay = Day ( tempDate )

                            ] ;

                            // return the last day of previous month if the day is not in the month

                            tempDate - Case ( theDay <> resultedDay ; resultedDay )


                            • 11. Re: A month from today?


                              Well, MIN is normally thought of as a number function, but in reality it works on dates because, in effect, the date is just a "mask" of the number of days since 1/1/0001. Those "number" functions generally work just as well on dates. In fact, most of them will work on text, although the results are sometimes a bit hard to get your head around.


                              As far as whether I've run into the issue before... well, it's more accurate that at some point I've done just about everything to do with dates either the hard way, the wrong way, or both. If I could get someone to retroactively pay me a nickel an hour for all the time I've spent overthinking and over-coding date calculations, I'd be retired by now. For instance, I cringe when I think of all the various nested If's I wrote in the old days just because I didn't realize that FM would automatically "roll over" if I put in a month greater than 12, or a day greater than the number of days in a month, and so forth. When you first encounter this, as you did, it seems counter-intuitive and perhaps even wrong, but once you get your head around it, it's amazingly useful.


                              As for Let... you could probably get by just fine in this case without a let. I find it's essential when I want to re-use a field or function over and over, since FM only has to evaluate it once and I (a lazy typist) only have to type it once. However, I also find it really useful for declaring my "moving parts" up front. Once you get used to it, it makes functions easier to read and also makes it easier to reuse and "tweak" the functions because you can change those "moving parts" right up front, in one place. I'd encourage you to use Let more. I promise you won't regret it!


                              And finally... I think scripts are the "go to" tool for many developers. They're comfortable and easy to use, and they were the ubiquitous hammer in my own toolbox for the longest time. Obviously, for any developer, they're a huge and powerful part of development. As a downside, I think scripts tend to be the "least common denominator" in the toolset (scripts, functions, custom functions, relationships, etc.), and while you can theoretically do just about anything you need to do with scripts, they are sometimes... inelegant? As a custom solution developer, I'm weary of reinventing the wheel, and adamant about keeping code small and reusable. I've spent too much time copying and pasting code (especially scripts) and then changing doing basically a long, manual "find and replace" to get it to work in a slightly different context. Some years ago, due to my skillset and FM's capabilities at the time, we had a full-time employee who would get tasks like "See these 7 scripts for invoices? Duplicate them for sales orders, quotes and purchase orders, but change 'inv' to 'sos', 'quo' and 'pur' and then repoint all the buttons on those layouts..." We just don't do that anymore. That employee's gone, and frankly, she was happy to move on to more meaningful work.


                              And, yes, I can definitely see this as a custom function. To elaborate on the "Let" discussion, if I wrote it, it'd probably look something like this:


                              Let ( [

                              x=1 // see Calculation Function Formatting | Extensitech



                              // testing

                              ; startDate = GetAsDate ( "3/31/16" )

                              ; MonthsToAdd = -1



                              ; target = Date ( Month ( startDate ) + MonthsToAdd ; Day ( startDate )  ; Year ( startDate ) ) // this day next month

                              ; monthend = Date ( Month ( startDate ) + MonthsToAdd + 1 ; 0 ; Year ( startDate ) ) // the end of next month

                              ] ;



                              GetAsDate ( min ( target ; monthend ) )






                              Chris Cain


                              • 12. Re: A month from today?

                                Another interesting approach.



                                • 13. Re: A month from today?

                                  Wow, great reply, thanks!


                                  I wish FMP was (more) object oriented, but that would probably mean it would be too complicated for some. Not sure. With an object, this type of behavior could be encapsulated and not stored as a custom function (so '90s).


                                  But, having said that, this function might actually be a better candidate for a Static class method (more shareable) so maybe not that object oriented ... even in Java!


                                  Thanks again for your thoughtful reply.


                                  - m

                                  • 14. Re: A month from today?

                                    absolutely perfect! Thanks, Chris!



                                    1 2 3 Previous Next