If you wish to automate it then you will not need the global fields at all. The script might look like:
Go To Layout [ layout based upon Sales ]
Enter Find Mode [ uncheck pause ]
Set Field [ yourSaleDate ; (calculation below)
Set Error Capture [ On ]
Perform Find [ ]
If [ Get ( LastError ) ]
Show Custom Dialog [ OK ; "No records found" ]
Go To Layout [ Original layout ]
... do whatever you wish with the found set
Let ( [
end = Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) ;
start = end - 6
start & ".." & end
d = Get ( CurrentDate ) ;
Date ( Month ( d ) - 1 ; 1 ; Year ( d ) ) & ".." & d - Day ( d )
Year ( Get ( CurrentDate ) - 1 // this produces 2010 and is valid search criteria in a date field
Some of your items were duplicates so I was unsure what else you might want. :-)
On the other hand, sometimes you want/need the flexibility of a date range based search as this can let you pull up records for other intervals besides the last week, last month and last year intervals.
Enter Find Mode 
Set field [YourTable::SalesDate ; YourTable::gStartDate & "..." & YourTable::gEndDate ]
Set Error Capture [on]
This script will only work if gStartdate and gEndDate have global storage specified.
That is correct...to be able to have both ways scripts as LaRetta mentioned and have the flexibility as Phil mentioned. I need the scripts to enter the beginning dates Lets say for This Month gStartdate=12/01/11 gEndDate=12/31/11...I need to figure out how to get the those values...see attached setup for my layout...and it will make some sense hopefully.
Correction for attached pic, it should be cOrderTypeINVOICE = order_type
Hmmm, I was describing how to perform a find, you have a relationship here.
If by "last week" you mean "previous 7 days from today", You could use Get ( currentDate ) - 7 for startdate and Get ( CurrentDate ) for End date.
If you mean last week starting with Sunday, you can use LaRetta's calculations to put in start and end dates--just capture the individual 'start' and 'end' values in your two global fields instead of constructing a date range expression.
The same works for a "last month" interval.
For "last year", you'd use something like this:
Date ( 1 ; 1 ; Year ( Get ( currentDate ) ) - 1) for start date and
Date ( 12 ; 31 ; Year ( Get ( currentDate ) ) - 1) for end date
Oh, you are using a relationship and not performing find.
Then user can enter dates into the globals or you can have buttons next to the fields. For each of the buttons, create a script which sets the globals with the results. Scripts would be:
Set Field [ gStart ; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) - 6 ]
Set Field [ gEnd ; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) ]
Set Field [ gStart ; Date ( Month ( Get ( CurrentDate ) ) - 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ]
Set Field [ gEnd ; Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ]
Set Field [ gStart ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) - 1 ]
Set Field [ gEnd ; Date ( 12 ; 31 ; Year ( Get ( CurrentDate ) ) - 1 ]
Ok all of it works great...I figured by getting those scripts I should be able to figure out the script for
"This Month"...I guess my small brain is just having a tough time grasping the date scripts...
Hi Jay, not sure if you were asking for a nudge on this month but it would be:
Set Field [ gStart ; Month ( Get ( CurrentDate ) ; 1 ; Year ( Get ( CurrentDate ) ) ]
Set Field [ gEnd ; Date ( Month ( Get ( CurrentDate ) + 1 ) ; 0 ; Year ( Get ( CurrentDate ) ) ) ]
Thanks so much...jus't don't understand the reason for +1 and the 0 and how that comes out to last day of a particular month.
If you put Date ( Month ( date ) + 1 ; 0 ; Year ( date ) ) then FileMaker interprets Month ( date ) + 1 and moves the month increment forward by one month. But since the day is 0 instead of day 1, it drops back another day and ends on the last day of the prior month, which in this case, since we jumped ahead one month actually determines the last day of THIS month.
You can incrementally move each piece (month day and year) by this method and FM will properly adjust. If you use true dates and date functions, they will always properly account for leap year as well.
Date - Day ( date ) does the same thing ... if you deduct the number of days from a date, you end up on the last day of the prior month because the day is '0'.
Edited wording a bit