I'm a bit stuck here....
I have a table with Dates ranging from 2012 -2016....How do I create a report that shows only the past 12 months....when I try creating the report....it always shows all of the date.
Thanks in advance.
Perform a find. This can be a manual find or in a script.
In a script:
enter find mode [ ] - clear the pause
set field [ yourtable::datefield; ">" & get ( currentDate ) - 365 ]
perform find [. ]
Since a year doesn't always have 365 days, and if you're really picky, and I am given odd bugs customers might report like "the date is off" this (leap) year, you could create a function that would also take into account leap years (divisible by 100 and not divisible by 400).
Seems like an obvious choice for a CF.
All depends what you mean by 'past 12 months'. If this is 21st August 2016, do you mean 1st August 2015 to 31 July 2016? Or 22nd August 2015 to 21 August 2016?
This calculation will return the date a year ago (depending on what you mean, of course):
Date ( Month ( DateStart ) - 12 ; Day ( DateStart ) ; Year ( DateStart ) )
For example, set the Start Date to be 28/2/2015 and it will return the year-previous date as 28/2/2014. Set it as 29/2/2016 and it will return 1/3/2015.
If that's not what you meant by 'a year ago' you could adjust it.
This is a helpful post:
Creating Find Set based on date ranges - Finding & Searching - FMForums.com
Yep, that was exactly what I was looking for! Thank you Philmodjunk! I had tried that before....but I forgot to type in the "&"....That's why it was not working.
I have 2 other questions.... should be easy.
How can my report display the currently selected User on a target field on another layout?...So when I use the script "Go to layout:MyReport" it shows the data only for that User...I'm having to manually find the User.
And Why is my page number showing as "?" at the end of the report?
Sorry if the questions seem a bit dumb...I'm new to filemaker. And thanks so much for the help!
in those cases, I use:
Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) - 1 ) )
( Month ( Get ( CurrentDate ) )
; Day ( Get ( CurrentDate ) )
; Year ( Get ( CurrentDate ) - 1 )
It handles leap years/days very well. If today is 2/28 (rare), then last year would be 2/27 or 3/1 (I haven't tested).
And YES! a cf (custom function would help).
Beverly - using the alternative I posted it calculates 'a year ago' from 2/29 (the 'rare' one...) as 3/1.
I am regularly amazed how powerful the FM Date function is. Almost Black Magic.
Thanks, alan! I knew I should have tested for
Date ( 3 ; 0 ; Year ( Get ( CurrentDate ) ) ) // last day of Feb.
my point was that the math can be done on each part of the
date ( month +/- ; day +/- ; year +/- )
FileMaker is smarter than I am to get the correct date!
Retrieving data ...