8 Replies Latest reply on Dec 9, 2008 12:58 AM by raybaudi

    Sorting question...

    Rackman

      Title

      Sorting question...

      Post

      Hi again,

       

      I'm trying to set up a sort of a date field to generate a daily follow up list.

       

      The problem I'm having is although each record contains a "Follow Up" field, they will be blank if that particular record is not scheduled for a follow up. When I try to sort Ascending, it puts all of the blank fields first w/ the completed ones way at the bottom of the list (over 3600 records). If I sort descending, it puts the farthest away dates at the top of the list.

       

      How do I weed out all of the blank date fields and get the dates closest to today at the top of the list?

       

      TIA

       

      Joe 

        • 1. Re: Sorting question...
          Kundinger
            

          Hi "Rackman",
           
          A simple solution to your issue...
           
          In your sort script,
            - add a step to "find & omit the BLANK FIELD records" 
            - perform your sort step
           
           
          I hope this helps you and any other interested readers... Good Luck!!!


          Kundinger

             

          • 2. Re: Sorting question...
            Rackman
              

            Thanks for the quick answer.

             

            Unfortunately I'm totally new to FileMaker Pro. I just switched from Bento which had this type of function built in.

             

            I'm still learning the scripting side of FMP but would appreciated any further assistance you could give in setting this type of sort up.

             

            Thanks again,

             

            Joe 

            • 3. Re: Sorting question...
              raybaudi
                

              Hi

               

              a way is sorting ( ascending ) by a calculated ( evaluate if all referenced fields are empty ) UNSTORED number field with calc:

               

              Case(
              date < Get ( CurrentDate ) ; Get ( CurrentDate ) + 1000 ;
              date
              )

              • 4. Re: Sorting question...
                Rackman
                  

                Thanks Daniele for the response.

                 

                Unfortunately, I'm new to scripting and do not fully understand your answer.

                 

                Any additional information would be helpful.

                 

                Thank you again,

                 

                Joe 

                • 5. Re: Sorting question...
                  raybaudi
                    

                  Ok.

                   

                  Go to File >> Manage Database... >> Fields

                   

                  and create a new field with name "SortOrder" Type Calculation and calc:

                   

                  Case(
                  date < Get ( CurrentDate ) ; Get ( CurrentDate ) + 1000 ;
                  date
                  )

                   

                  Note that "date" must be changed with the real name of your date field.

                   

                  While we are here:

                  1) uncheck the box: "Do not evaluate if all referenced fields are empty"

                  2) Choose that the result of the calculation must be a Number

                  3) Push the button "Storage Options..." and click into the box: Do not store calculations results.

                   

                  OK

                   

                  OK

                   

                  Now you'll have a new field for each record of your DB... SORT by it.

                   

                  Today and Tomorrow and always, you'll have the result of sorting showing at the top the date closest to today.

                   

                  BTW: sorting by unstored field may become slowly when the number of records increase.


                  • 6. Re: Sorting question...
                    Rackman
                      

                    Thanks Daniele! 

                     

                    That did the trick.

                     

                    Now I have another small problem...

                     

                    The sorting works fine which I'm using in a list view. When I run the sort, it sorts correctly but for some reason the screen jumps down the list after the sort so I then have to scroll back to the top of the window to see the first record of the sorted list.

                     

                    Is there any function I can add to the sort that will reset the window back to the first record at the top of the list so I don't have to keep scrolling back to the top?

                     

                    Thanks again,

                     

                    Joe 

                    • 7. Re: Sorting question...
                      raybaudi
                        

                      The jump to the buttom of the list is done if you had putted the cursor into a record that will became one of the last in the sorted list.

                       

                      You can:

                       

                      1) try Table view with sortable columns

                       

                      or

                       

                      2) Make a script of two steps:

                       

                      Sort [ ascending ; by field: SortOrder ]

                      Go to Record [ first ]

                       

                      and set a new button to perform that script.

                      • 8. Re: Sorting question...
                        raybaudi
                          

                        Another good way to view ONLY records of the current month ( and w/o the new field ) is using a script like this:

                         

                        Enter Find Mode [ ]
                        Set field [ yourDateField ; Month ( Get ( CurrentDate ) ) ]
                        Perform Find [ ]
                        Sort records [ Ascending ; yourDateField ; no window ]