A READER ASKS: Retrieve FileMaker Records More Than One Year Old

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 


I have a field "Medical Exams". One person can have more than one "Medical Exam" entry.

How do I retrieve records of people that have not had an entry for over one year?

Again, I'm sure that there is an easy solution, as with all my other questions.

Part of this answer comes in using the Date and Get(CurrentDate) functions.

The Date function allows you to take 3 parameters ( which can be a field, a calculated value, literal text or a combination thereof ) and make a proper date out of them. Your three parameters are for the month, the day and the year. The nice thing about it is that you can add/subtract any number to any of the parameters and FileMaker will calculate the correct date. For example adding 2000 days to the day parameter will automatically take into account the odd number of days in a month and even leap years!

The Get(CurrentDate) function will return a date result of date that shows in the computer systems clock (which is hopefully correct). The format of the date returned can vary depending upon the date format settings of the operating system of the computer in use.

So what we will want to do is use the Get(CurrentDate) function within the Date function for the Month, Day and Year parameters. For the year, we will want the current year minus 1 and that gets us last years date.

Here you can see the Data Viewer showing us the calculation. The Data Viewers monitor tab is a great place to do “sandbox” testing for calculations.

Now that you know how to determine what last year was, you can use a find operation or a GTRR action to bring up the found set of records.

BTW: GTRR is an acronym for Go To Related Record and describes a process to going to a set of matching related records in the current window or brining up those records in a new window. The later so you can see both the parent record(s) and the child record(s) at the same time. Using GTRR can be very powerful action for a portal. It can allow the FileMaker portal to become an internal pick list for a user or a script. Yes, when you start scripting actions that go to a portal row, perform an action and then go to another portal row in a loop. When you find yourself doing programming such as this, you are definitely introducing yourself to a higher level of FileMaker design!

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.