Do you mean to sort the records or find the records?
Sort by day, week, month sounds like you want to sort your records in order, but "make two fields and a user enters the dates ?" sounds like you are actually trying to find records rather than arrange records already found into a specified order.
Here is a screen shot of the report. THe buttons up top kind of give u a general idea. I have a time stamp field on each record. What would be the easiest way to view the data by day, week, or month?
Or is it easier to do a find on records from two fields. From and To. Then search and sort them by timestamp field ?
The answer is that all of these options are possible. Take a look at the screen shot at the end of this post. This is a small window that I open on one of my solutions so that the user can specify what range of dates they want for their report.
With the buttons shown on this layout, you could set up a script that finds records date with today's date, dates in the current calendar week, the previous z days, the same calendar month as today, the previous 30 days.... or you could give them the option to selct Month of July 2012, or First Quarter 2011, or date 1 through date 2....
All come down to a scripted find that uses the choices made by the user to create a find request for data falling in a particular date range.
Can you post your script Phil so i can see how you coded it? Thanks.
There are two scripts.
The first opens up a floating window with the above fields. Drop downs, a calculation field and script triggers all play a role in minimizing user errors when selecting/entering date criteria on it. Both are implemented in a FileMaker 10 script so it cannot take full advanatage of some of the newer options such as FileMaker 12's modal windows and it's written for a windows platform so it includes some extra window resize steps to deal with a problem that occurs when you open a new window while your windows are in a Maximized state:
#Specify Date, Date Range, Quarter, Month or Year for most Reports.
#The script uses a script parameter to select the appropriate search layout to display.
Set Field [ Scrap Purchaser::gReturn Name; Get(LayoutName) ]
New Window [ Name: "Search Criteria"; Height: Case (Get(ScriptParameter) = "Specify Date" and Get ( WindowZoomLevel ) = "100*" ; 282 * 1.27 ;
Get(ScriptParameter) = "Specify Date" ; 282 ; Get ( WindowZoomLevel ) = "100*" ; 352 * 1.28 ; 352 );
Width: If ( Get ( WindowZoomLevel ) = "100*" ; 423 * 1.31 ; 423 ); Top: 100; Left: 100 ]
Move/Resize Window [ Name: Get(FileName); Current file; Height: Get ( ScreenHeight ); Width: Get ( ScreenWidth ); Top: 0; Left: 0 ]
Show/Hide Status Area [ Lock; Hide ]
Allow User Abort [ Off ]
Go to Layout [ Get(ScriptParameter) ]
Set Field [ Scrap Purchaser::gFirst Date; Get(CurrentDate) ]
Set Field [ Scrap Purchaser::gLast Date; "" ]
Set Field [ Scrap Purchaser::gMonth; "" ]
Set Field [ Scrap Purchaser::gQuarter; "" ]
Set Field [ Scrap Purchaser::gYear; "" ]
Set Field [ Scrap Purchaser::gYearAlone; "" ]
Pause/Resume Script [ Indefinitely ]
#Remaining steps are in separate script so that specify layouts will respond identically to all combinations of user input.
Perform Script [ “Find Records Palette”; Parameter: Get(ScriptParameter) ]
The second is preformed when the Find button shown above is clicked: (I've actually shortened this script by omitting non date criteria also used in this script.)
#Search the Scrap Purchaser table for records matching the specified criteria.
#First part of this script is found in "Specify Search Criteria"
Close Window [ Current Window ]
Adjust Window [ Maximize ]
Go to Layout [ Scrap Purchaser::gReturn Name ]
#Parse date specifications
Enter Find Mode [ ]
If [ Scrap Purchaser::gFirst Date ≠ "" and Scrap Purchaser::gLast Date = "" /* Single Date Specified */ ]
Set Field [ Scrap Purchaser::gReport Mode; "Daily" ]
Set Field By Name [ Get ( LayoutTableName ) & "::Date"; Scrap Purchaser::gFirst Date ]
Else If [ Scrap Purchaser::gFirst Date ≠ "" and Scrap Purchaser::gLast Date ≠ "" /* Date Range Specified */ ]
Set Field [ Scrap Purchaser::gReport Mode; "Daily" ]
#[Date] Field must be present on layout specified in [gReturn Name] in order for this step to execute correctly.
Set Field [ Scrap Purchaser::Date; Scrap Purchaser::gFirst Date & "..." & Scrap Purchaser::gLast Date ]
Else If [ Scrap Purchaser::gMonth ≠ "" /* Month, Year Specified */ ]
Set Field [ Scrap Purchaser::gReport Mode; "Monthly" ]
Set Field [ Scrap Purchaser::::cMonthYear; Scrap Purchaser::gMonth & ", " & Scrap Purchaser::gYear ]
Else If [ Scrap Purchaser::gQuarter ≠ "" /* Quarter, Year Specified */ ]
Set Field [ Scrap Purchaser::gReport Mode ; "Quarterly" ]
Set Field [ Scrap Purchaser::::cQuarterYear ; Scrap Purchaser::gQuarter & " Quarter, " & Scrap Purchaser::gYear ]
Else If [ Scrap Purchaser::gYearAlone ≠ "" /* Year Specified */ ]
Set Field [ Scrap Purchaser::gReport Mode; "Yearly" ]
Set Field [ Scrap Purchaser::::cYear ; Scrap Purchaser::gYearAlone ]
Enter Browse Mode
Show Custom Dialog [ Title: "ERROR"; Message: "No valid search criteria."; Buttons: “OK” ]
Exit Script [ ]
Allow User Abort [ On ]
Set Error Capture [ On ]
Perform Find [ ]
Set Error Capture [ Off ]
If [ Get(FoundCount) = 0 ]
Show Custom Dialog [ Title: "No records were found"; Message: "Divisions: " & Substitute(Scrap Purchaser::gDivision;"¶";", ") & "¶" & "Criteria:¶" &
Scrap Purchaser::cSearchExplain; Buttons: “OK” ]
#Sort by Date, Tag Number
Sort Records [ Specified Sort Order: Scrap Purchaser::Date; ascending Scrap Purchaser::Tag #; ascending ] [ Restore; No dialog ]
Set Field [ Scrap Purchaser::gSelected Date; Scrap Purchaser::Date ]
Phil here is my new screen shot of the layout. What do i need to do on this script for these two fields ?
Pretty crazy intense script. Im not going to do a pop up window. I just want the user to put a date in those two fields and then have it search the records off the time stamps ? Whats the easiest way to do this ?
Then skip the first script entirely and just use the code in the second. The key is to have the user enter data to be used as find criteria into fields with global storage specified.
When the script takes the window into find mode, all data fields go blank so that search criteria can be entered into them. Global fields are the exception. They retain their data while in find mode so that the set field steps can copy the data from them into the fields where they need to be entered as search criteria. The set field steps can perfrom calculations to combine the data entered by the user with other operators such as "..." to produce the needed find requests.
Here's a link to another thread where I posted a sample script with many different examples of how set field can be used in such a script. It's a much simpler script example: http://forums.filemaker.com/posts/ba7347f58a
Phil here is my script. Still not working correctly. What am i doing wrong?
Note that Phil's field-naming convention indicates that the DateFrom and DateTo fields are defined as global fields (in their Field Definition). (He calls them gFirstDate, etc.)
You could Make the 3 Set Field steps just one:
Set Field [Foot Print Log::Time Stamp ; Foot Print Log::Date From & "..." & Foot Print Log::Date To ]
The field i have set to be global are Date From and Date to . Should timestamps be global as well?
But it is the timestamp field you are searching in? It must be true data (it is the data you hold in your records) so cannot be global.
When you do the find manually in the timestamp field (entering the same dates you used in the full script), what records does it find?
When you run the script and then do Ctrl+R (to re-create the Find) does the search criterion look the same as you entered?
Ok i got it working. Here is my script. I put a g in front on my global fields and then it started working. On global fields...do you have to put a g in front ? or is this just good practise to define them ?
Putting a g in front will not change the field type or definition. It is just a common nomenclature practice.