Jump to content

Search for criteria within a range of dates in FM7

This topic is 6490 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I was wondering if someone could help explain the proper syntax for creating a script where the user could select a month to search for from a dropdown list(ex: February) and the database would find all contributors who had given money in that month by searching the contributor table. I figure there is a simple command for this but I havent come up with anything in the scriptmaker because all dates stored in the contributor table are in ##/##/#### format (ex: 02/01/2004) which is different from just the spelled out word "February" found in the drop down list. Hopefully I'm being clear on this... thanks for any help, I greatly appreciate it!!

PS: Later down the road, I would like to search for all contributions given within the past 3 years based on the current date from a specific contributor in the table. Im guessing this would be another type of script??? If I'm missing any key date syntax rules please feel free to point them out because numerous other date queries will be heavily used by the system I'm setting up for our client. Any advice on solving this would be great too! Thanks again!

FileMaker Version: 7

Platform: Windows XP

Link to comment
Share on other sites

To search for all dates in a month, you should create a calc field Month = MonthName ( DateField )

Then on a search layout, in find mode, your users can use a value list on the Month field to search for all records with that month name.

You will probably need to have a Year field too, for narrowing the search to a specific year: Year = Year ( DateField )

When I script searches like this, I auto-fill some of the search fields (like putting the current year in the Year field.) Anything to make it easier and more foolproof is always good.

To search on a range of dates, use ">4/5/2003" or "4/5/2003...4/5/2004".

Link to comment
Share on other sites

I'm working with FM7Newbie on the same project. To clarify our problem in the PS: We want to be able to search for all contributions made by an individual in the last 3 years to date. This is search will be made 100s of times a day and it would really be a pain to manually enter "04/20/2001...//" everytime

I need the syntax to perform a find on the following range: "(Today minus 3 years)...//"

so we can hard code a button to do the find in one click (and SAVE A LOT OF TIME).

Link to comment
Share on other sites

Script should look something like this:

Enter Find Mode []

Insert Calculted Result [ Contribution Date , Status(CurrentDate) - 1095 ]

Insert Text [ Contribution Date, "...//" ]

Perform Find []

Link to comment
Share on other sites

That will work for this year, 2008, 2012, etc. For most other years it will be off by one day. I suggest you use

Date(Month(Status(CurrentDate)), Day(Status(CurrentDate)), Year(Status(CurrentDate) - 3)


Link to comment
Share on other sites

This topic is 6490 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.