Find Last Month's Records

[SnetCo]

To create a sales tax report for the previous month sales, I have a script that finds records that were paid say "3/1...3/31" which finds them for March of the current year. It works great until I get to January. I don't want the user to have to enter 12/1/01...12/31/01. I want to be able to click a button for December and find all the previous December's records, no matter what year it is. This will always be done before the 20th of the following month. I hope this makes sense, this is my first post.

[Steven H. Blackwell]

quote:

---

Originally posted by Janet McDonald:

To create a sales tax report for the previous month sales, I have a script that finds records that were paid say "3/1...3/31" which finds them for March of the current year. It works great until I get to January. I don't want the user to have to enter 12/1/01...12/31/01. I want to be able to click a button for December and find all the previous December's records, no matter what year it is. This will always be done before the 20th of the following month. I hope this makes sense, this is my first post.

---

There are at least two different ways to do this. What operating system are you using?

Old Advance Man

[SnetCo]

I'm on a Mac, system 9 with FM PRO 5.

[Steven H. Blackwell]

OK, you can use an AppleScript, and I will post it in a little while.

Old Advance Man

[SnetCo]

Thank You!

[Steven H. Blackwell]

Let's do this the easy way, at least for now.

Assume that you already have a field called "date" that contains the date you're tracking. Define two new calculation fields (type number) one of which is called "year" and is equal to (Year of date) and the other of which is called "month" and is equal to (Month of date). THese functions are found in the Calculation functions under the date heading.

Then for December only, create a Perform AppleScript ScriptMaker step that has this code. Watch the line wrap:

set theDate to current date

set theYear to (year of theDate) as string

tell application "FileMaker Pro"

show (every record of database 1 whose cell "Year" = (theYear - 1) and cell "month" = 12)

end tell

When this script is run for December of last year's records in January of this year, it will return all the December 2001 records.

Sooner or later you may want to address the issue of multiple years, and a lot of them, but this will work for now.

There are oither ways to do this as well, including just doing a scripted Find on the Year and Month fields, but this solution will work.

HTH

Old Advance Man

[Thom]

Write a ScriptMaker script called "Find Last Month":

Enter Find Mode []

Insert Calculated Result [ dateField,

[SnetCo]

It works great. Thank you all for your input.