1 2 Previous Next 15 Replies Latest reply on Aug 3, 2012 8:22 AM by philmodjunk

    Sort Records By Day , Week, and Month

    ZoocMan85

      Title

      Sort Records By Day , Week, and Month

      Post

      Hey everyone,

      I have a report and i have three buttons on it. Sort by Day, Week, and Month. I want to be able to sort records on the report by the following. How do i do this. Or would it be better to make two fields and a user enters the dates ? To and From for ex. Please help. Thanks.

       

      Ray Z

       

        • 1. Re: Sort Records By Day , Week, and Month
          philmodjunk

          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.

          • 2. Re: Sort Records By Day , Week, and Month
            ZoocMan85

             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 ?

            • 3. Re: Sort Records By Day , Week, and Month
              philmodjunk

              The answer to my question would then be that you want to find records rather than to sort them. Wink

              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.

              • 4. Re: Sort Records By Day , Week, and Month
                ZoocMan85

                 Can you post your script Phil so i can see how you coded it? Thanks.

                • 5. Re: Sort Records By Day , Week, and Month
                  philmodjunk

                  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.
                  Freeze Window
                  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 ]
                  Freeze Window
                  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 ]

                  Else
                         Enter Browse Mode
                         Show Custom Dialog [ Title: "ERROR"; Message: "No valid search criteria."; Buttons: “OK” ]
                         Exit Script [ ]
                  End If

                  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” ]
                  Else
                         #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 ]
                        
                  End If

                  • 6. Re: Sort Records By Day , Week, and Month
                    ZoocMan85

                     Phil here is my new screen shot of the layout. What do i need to do on this script for these two fields ?

                    • 7. Re: Sort Records By Day , Week, and Month
                      ZoocMan85

                       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 ?

                      • 8. Re: Sort Records By Day , Week, and Month
                        philmodjunk

                        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

                        • 9. Re: Sort Records By Day , Week, and Month
                          ZoocMan85

                          Phil here is my script. Still not working correctly. What am i doing wrong?

                          • 10. Re: Sort Records By Day , Week, and Month
                            Sorbsbuster

                            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 ]

                             

                            • 11. Re: Sort Records By Day , Week, and Month
                              ZoocMan85

                               The field i have set to be global are Date From and Date to . Should timestamps be global as well?

                              • 12. Re: Sort Records By Day , Week, and Month
                                Sorbsbuster

                                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?

                                • 13. Re: Sort Records By Day , Week, and Month
                                  ZoocMan85

                                   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 ?

                                  • 14. Re: Sort Records By Day , Week, and Month
                                    Sorbsbuster

                                    Putting a g in front will not change the field type or definition.  It is just a common nomenclature practice.

                                    1 2 Previous Next