FileMaker Example File: Find Month Via Calculation

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

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

There are times you may want to see a found set of records for the past month, a month in the future or the current month. This isn’t something you will find straight out of the FileMaker box but you can script it easily enough. In this example, we have two buttons that will find all the records for the previous month and for one month in the future. Really, we have 4 buttons but they do the same thing using different methods. We will cover that a little later.


A big part of this example uses the family of date functions. So if you are not familiar with them, you might need to do a little research first. Also, we do run a script when the example file starts up that creates records with dates that will match our searches. So don’t let this script throw you off.

The next thing we did was create a calculation field that will build what you would type in for a range of dates. You know, the string of a start date and then three periods ( ... ) and following up with the end date. So a text string of 12/1/2003...12/31/2003 would be used to find all the record dates in the month of December of 2003. The calculations are as follows ...

Past month
GetAsText(Date(Month(Get(CurrentDate))-1; 1; Year(Get(CurrentDate))))
& "..." &
GetAsText(Date(Month(Get(CurrentDate)); 1; Year(Get(CurrentDate)))-1)

Future month
GetAsText(Date(Month(Get(CurrentDate))+1; 1; Year(Get(CurrentDate))))
& "..." &
GetAsText(Date(Month(Get(CurrentDate))+2; 1; Year(Get(CurrentDate)))-1)

Both calculations use the date function of Date(month, day, year ) and some simple math. To get the first day of last month, we simply subtract one from the month of the current date and make the day the 1st. We do basically the same thing to get the last date. We calculate the first day of the current month and subtract 1. That will give us the last day of last month. The future dates use the same setup except we are careful to make sure our calculated month has a 1 added to it. So we take those two calculated dates and concatenate them together with a text string of three periods in the middle.

So we all we need to do is make the script go into find mode, paste in the string we want to search with and then perform the find. We wrote two different methods for doing this.

The first set of scripts use those calculation fields. They copy the results of the calculation while in browse mode, switches to find mode, pastes the data into the date field and performs the find.

The second set of scripts does not use a copy and paste action. This may be a superior setup because copy requires the field you are copying from to be on the current layout. If we were to delete those calculation fields from the layout, the first set of scripts would break. In the second set, we don’t need the calculation fields at all. We are using the Insert Calculated Result script step. So the script is doing the calculation and separate calculation fields are not necessary.

An example file can be downloaded by clicking (here).
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - 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.