This one is flummoxing me.  We have due dates that are calculated two or three months in the future.  So, two months from January 15, 2015 calculates to March 15, 2015, and that's easy enough to do.  However, say we are doing three months from January 31, 2015.  Obviously, April 31, 2015 does not exist, so we need this to go to the *last* day of April, April 30, 2015.

 

If I use the Date function, and plug in Date($month+3;$day;$year) for January 31, 2015, FM returns May 1, 2015, which totally makes sense, but unfortunately doesn't work for what we need.

 

The only solution I can think of is to kludge my way through, where I look at $newmonth=$month+x and $day, and then if $day=31 and the $month is April, June, September, or November, make $day=30.  And perform a similar calculation for February, which is a bit harder due to leap year.

 

However, I'm thinking that there is probably an elegant way of doing this in FMP that I'm completely missing.

However, I'm thinking that there is probably an elegant way of doing this in FMP that I'm completely missing.

 

The thing to know is that you can “over”- and “under”-feed the arguments of the Date() function, and the calculation engine will duly calculate the correct date.

 

So the last day of any given month is the day before the first day (the “zero-eth”, if you like) of the following month, which you can express as e.g. (date of last day of current month)

Let (
  cd = Get ( CurrentDate ) ;
  Date ( Month ( cd ) + 1 ; 0 ; Year ( cd ) )
)

 

The thing to know is that you can “over”- and “under”-feed the arguments of the Date() function, and the calculation engine will duly calculate the correct date; so the last day of any given month is the day before the first day of the following month (the “zero-eth”), which you can express as e.g. (date of last day of current month)

Let (
  cd = Get ( CurrentDate ) ;
  Date ( Month ( cd ) + 1 ; 0 ; Year ( cd ) )
)

 

So if I'm understanding what you're getting at, the idea is to find the last day of the month X months from the base date, and if this last day is less than the day of the last day of the base date, to set day accordingly?  I have to think as well, I guess, how this would affect year.

So if I'm understanding what you're getting at, the idea is to find the last day of the month X months from the base date, and if this last day is less than the day of the last day of the base date, to set day accordingly?

 

Yes, you can need to compare the day of the result to the expected day (that's easier than comparing the months); something like: 

Let ( [
  baseDate = YourDateFieldOrExpression ; // or plug it directly into the three relevant lines below 
  shiftBy = 3 ;
  mb = Month ( baseDate ) ;
  db = Day ( baseDate ) ;
  yb = Year ( baseDate ) ;
  newDate = Date ( mb + shiftBy ; db ; yb )
  ] ;
  Case (
    Day ( newDate ) = Day ( baseDate ) ;
    newDate ;
    Date ( mb + shiftBy + 1 ; 0 ; yb ) 
   )
)

or just

Let ( [
  … 
 ] ;
 Min ( newDate ; Date ( mb + shiftBy + 1 ; 0 ; yb ) )
)

which OTOH means that the second expression always needs to be calculated, which is not the (pun notwithstanding) case in the first version.

Awesome!  I will play around with this.  Thank you very much!

These are always so much fun!!  Here's another way to approach it, where monthsAhead is a number:

Let ( [
date1 = Date ( Month ( YourDate ) + monthsAhead  ; Day ( YourDate ) ; Year ( YourDate ) ) ;
date2 = date1 - Day ( date1 )
] ;
Case (
Day ( YourDate ) > Day ( date1 ) ; date2 ; date1 )
)

FM Calculations and Function Fun hasn't found me yet.......i spend hours staring at numerous FM calculation posts and i still don't get most of the logic, especially when it comes to dates, it is taking me forever to take hold of the FM Calculations Fun, i am forever confused  

Good deal!  I will try out both of these solutions!

 

Miss A, I hear ya!  I find dates particularly tricky when you want to do things a bit differently than FMP normally handles them. 

i spend hours staring at numerous FM calculation posts and i still don't get most of the logic...

 

That doesn't help much unless you also:

• Open FM Help and read about the function.  
• Create a test file
• Create the function in a calculation or your data viewer 'watch' tab (preferably both)
• Try the Help example and/or to repeat the thread's discussion you are reading
• Enter different sample data and view your results

Then in Data Viewer 'watch' tab, comment out the calculation portion of the Let() (see red below) and enter each Let() variable individually (see blue below) to view what each piece does.:

 

Let ( [

date1 = Date ( Month ( YourDate ) + monthsAhead ; Day ( YourDate ) ; Year ( YourDate ) ) ;

date2 = date1 - Day ( date1 )

] ;

/* Case (

Day ( YourDate ) > Day ( date1 ) ; date2 ; date1 )

*/

date1

)

 

Portions of each variable can also be broken apart.  Before Data Viewer, I would create these test files and calculations and put in sample data and bingo ... the logic becomes clear as I entered different data.  Now with Data Viewer, most testing takes place in 'Watch' where I use Let() to add my sample data as variables and view the results but I still also create several sample records for testing because it is the only way to replicate auto-enter (replace) calculations and it also is the only safe way to guarantee you are replicating the exact behaviours you are testing.

 

If you take the time to do this, you will pick it up far faster than just trying to force something into your brain that you cannot view in action.  Watching the data adjust as you manipulate the calc is pure exciting magic.  :-)

OK, two thoughts. First, I so need to upgrade to Advanced to get Data Viewer. Second, I still haven't tried out the suggestions here. Tough day at work. But I'll say this about FMP. I started my firm's FMP DB in September-ish of last year, with no prior DB experience. Made mistakes of course. But right now the DB has functionality on the order of systems that would cost me mid-six figures to replicate in a commercial patent DB package (not to mention a high-5 figures yearly support contract), without the customization that I build in that's particular to my firm's needs. And this is me as a patent lawyer doing FMP development 1-2 hours a day 5-6 days a week part-time. Not too shabby.

That doesn't help much unless you also:

• Open FM Help and read about the function.  
• Create a test file
• Create the function in a calculation or your data viewer 'watch' tab (preferably both)
• Try the Help example and/or to repeat the thread's discussion you are reading
• Enter different sample data and view your results

 

I had started doing part of this but not all.  This helps a lot.

 

These are always so much fun!!  Here's another way to approach it, where monthsAhead is a number:

Let ( [
date1 = Date ( Month ( YourDate ) + monthsAhead  ; Day ( YourDate ) ; Year ( YourDate ) ) ;
date2 = date1 - Day ( date1 )
] ;
Case (
Day ( YourDate ) > Day ( date1 ) ; date2 ; date1 )
)

 

OK, let me just say -- this is clever.  I love it -- the date2 calculation is genius!  One question -- is there any particular reason you use Case instead of If here?  I've only used Case when there is more than one "test," and if when there is just one.

The reason I usually use Case() is that, if the criteria changes, I can add additional tests easily.  Even if we think there will be only two tests, it can change; for example, I had once used If() to test whether Male or Female only to have business rule changed it to Male, Female, Unknown, differentiating it from an unselected value of null.

 

I also prefer using Case() because both Comment and Ray Cologon prefer it over If().  :-)

 

However, there are times when it is critical that the result be only two possibilities and I will use If() as an indicator (to myself) that it has a tight restriction to only two possible values.  Mostly, it is a personal choice.  Both Case() and If() produce empty default result if none specified and both short-circuit when they hit the first true.

BTW, using:

 

date - day (date )

 

... is a method picked up from Comment here on forums.  He is the date master without question.

Good deal.  I'll start migrating to Case.  I wish there were a script case command -- I have instances where I keep having to do multiple "else if," but cest la vie. 

 

Yeah, that date-day(date) was really the kicker for me.  I played around with this in a test script before moving to my production script, and further played around with it using pencil and paper.  I love elegant solutions! 

I prefer the Case because they are easier to write and to read.

I wish there were a script case command -- I have instances where I keep having to do multiple "else if," but cest la vie. 

 

Yes, scripts are comprised of script steps so you usually need to use If/Else construct but sometimes we can use If/Else within a single script step.  

 

Sometimes I catch myself beginning to write a script as this:

MULTIPLE SCRIPT STEPS
1. If [ your criteria ]
2. Set Field [ Field A ; value 1 ]
3. Else
4. Set Field [ Field A ; Value 2 ]
5. End If

... when instead I would simply do this:

SINGLE SCRIPT LINE
1. Set Field [ Field A ; If ( your criteria ; value 1 ; value 2 ) ]

There are other situations besides Set Field[] where the logic can be within a calculation for a single resulting action.

 

Hi Lee!  I agree.

 

It is easier to read (for me) simply because that is what is most-often used.  Since the only difference is two characters and I am inherently lazy, I prefer using If().  I'm also lazy in not wanting to change it later ... eh, same side of two coins.  

And yes, Mike, you REALLY need to get FMP Advanced.  :-)

 Mike, you REALLY need to get FMP Advanced.  :-)

 

and when you do, proudly display it here by upgrading your profile. LOL thought I would preempt this one.

I definitely need to upgrade!! 

 

My personal preference is not to pack so much into individual script steps.  When eyeballing a script, it's difficult to see the logic within a single set-variable step, say, as compared to seeing the logic over separate steps.  For example, I'd much rather see:

 

IF $test="yes"

  set variable $result (to "We have a yes!")

ELSE

  set variable $result (to "Boo, no yes.")

ENDIF

 

than a single

  set variable $result

 

which you then have to click on to see the (if $test"=yes; "We have a yes!"; "Boo, no yes.") logic contained therein.