13 Replies Latest reply on Mar 27, 2013 7:45 PM by LaRetta

    Sort Order Bug

    speckledchris

      Hi All,

       

      I have an unusual issue I could use some help on.

       

      I have a simple sort that I am trying to accomplish. The sort is based on two fields. A primary and a socondary. The primary field is a time field type and the other is a text field type. Regardsless of the field types (I have tried severl), the sort will on only work on the first field in the sort order. The secondary field in the sort order is ignored.

       

      Doesanyone have any troubleshooting suggestions as to why the sort will only work on the first field in the sort order and not all of them? Could this be a bug?

       

      Thanks for your help!!

       

      Chris

        • 1. Re: Sort Order Bug
          ch0c0halic

          Could you provide an example of the data and why you think its sorting wrong?

           

           

          The opinions expressed in this email are my own and do not reflect those of my employer or anyone else.

          Regards,

          Ch0c0halic, FileMaker 12 Certified Developer

          • 2. Re: Sort Order Bug
            taylorsharpe

            This works for you as long as you understand that time fields are internally stored as the number of seconds.  So if you have something that is one hour, two minutes and 20 seconds, you may format this and show it as "01:02", but internally, it is thinking of it as ( 1 * 3600 ) + ( 2 * 60 ) + 20  which equals 3740.  You may have a second time of one hour, two minutes, and 15 seconds, which can look formatted like "01:02" also, but in reality, it is 3735.  So FileMaker will sort 3735 before 3740 and only look at the 2nd field for sorting if those two numbers are the same (e.g., 3735). 

             

            Other workaround is you want to work within what you are showing formatted is to have a calculated field that puts the two fields together and sort on that one combined field. 

            • 3. Re: Sort Order Bug
              keywords

              The first sort criterion groups the records into sets with the same values in the sort field, then the second criterion sorts within each set. Perhaps your time field sort is creating sets of one record only, in which case there is no further sorting possible no matter what the second criterion is.

              • 4. Re: Sort Order Bug

                speckledchris wrote:

                 

                The sort is based on two fields.  A primary and a socondary.  The primary field is a time field type and the other is a text field type.  Regardsless of the field types (I have tried severl), the sort will on only work on the first field in the sort order.  The secondary field in the sort order is ignored.

                Hi Chris,

                 

                My immediate inclination is to suspect that you are sorting on a related field or that, in your sort dialog, you are selecting a different table occurrence of the right field.  Could either of those issues fit?  Or might the fields (or at least one) placed on the layout be from different table occurrence by accident?  Depending upon the current perspective and relationships involved, your results can change.

                • 5. Re: Sort Order Bug
                  AlanStirling

                  Hi Chris

                   

                  From my view of things, you are sorting by the seconds of the time and unless two records have the same time (to the second), then the second sort will have no effect.

                   

                  Since you are probably viewing the time as HH:MM (Hours and Minutes), then you are expecting the sort to ignore any seconds in the time field - it won't.

                   

                  You need to add a calculated time field, where you insert the Hours and Minutes but not the Seconds and then use this field for your primary sort.

                   

                  If your original time field was called 'StartTime', then the formula for the new calculation field would be;

                   

                  StartTime Calc = Time(Hour(StartTime);Minute(StartTime);0)

                   

                  Set the field type to Time.

                   

                  When you sort on this field, all records with the same hours and minutes can then be sub-sorted by your second text field.

                   

                  Best wishes - Alan Stirling, London UK.

                  • 6. Re: Sort Order Bug

                    You make a good point Alan, that Chris might not be getting the correct results but time fields still sort properly by time and that will take priority.  However, before resorting to a time calc to remove the seconds, it might be that Chris really needs to just sort by the second field first, and then within THAT sort, the time field sort will produce the records in ascending from there.  This would be the way a grouped report or summary works.

                     

                    Good catch!

                    • 7. Re: Sort Order Bug

                      Keywords nailed it, I think. His response wasn't there when I started.

                      • 8. Re: Sort Order Bug
                        speckledchris

                        Hi All,

                         

                        Wowzers.  I stepped out for a bite to eat and a chance to clear me head and came back to all of your comments.  Thanks a ton.  To try and explain bit further based on several of the comments...  Here’s the example is greater detail:

                         

                        Field 1:

                        FieldName: products::start_time

                        Field Type: Time

                         

                        Field 2:

                        Field Name:  products::_kf_track_id

                        Field Type: Text

                        Note:  This field is a related key field related to a table called “Tracks”

                         

                        I want to sort all of the records in the products table first by the Start Time then by the assigned Track ID.  Not all products have an assigned Track ID. The following represents some test data:

                         

                        Starting Data:

                        Record ID      Start Time     Track ID

                        1                     10:00:00        25

                        2                     10:00:00        26

                        3                     10:00:00        27

                        4                     8:00:00  

                        5                     15:00:00

                        6                     11:00:00        25

                        7                     11:05:00        26

                         

                        The sorted result I would like to see is as follows:

                         

                        Record ID      Start Time     Track ID

                        4                     8:00:00  

                        1                     10:00:00        25

                        6                     11:00:00        25

                        2                     10:00:00        26

                        7                     11:05:00        26

                        3                     10:00:00        27

                        5                     15:00:00

                         

                        I hope this clarifies things a bit more.  LaRetta... I'm curious if the fact that the track_id field is a related field is part of the problem based on your feedback.  Thanks all for the help!

                         

                        Chris

                        • 9. Re: Sort Order Bug

                          Yes, sort by TrackID and then time.

                          • 10. Re: Sort Order Bug
                            keywords

                            You say: I want to sort all of the records in the products table first by the Start Time then by the assigned Track ID

                             

                            But the sorted result you want to see, as per your table, is sorted by Track ID first, then by Start Time (ie. ID 25 are together, sorted by time, ditto ID 26) where there is a track ID, but somehow you have records 4 and 5 NOT grouped even though they appear to have no track ID. How come?

                            • 11. Re: Sort Order Bug
                              speckledchris

                              Keywords..

                               

                              You're exactly right.  For some reason I was thinking that filemaker would know to seperate the records with no track_id assigned since I was sorting by the time first.  I didn't think through the process that the secondary fields would sort as well (including the blank ones). I feel a little dumb.  Thanks for this.

                               

                              Now...  with that said,  I need to get to the end result as outlined above.  Do any of you have a suggestion on how to accomplish this?  Maybe a looping script or what not?  Curious.  I'll probably get something figured out now that I have my head out of the sort world.  That just won't work by itself.

                               

                              Chris

                              • 12. Re: Sort Order Bug
                                keywords

                                On the information presented:

                                 

                                1.     a sort by Time then ID should give your the following order:  4, 1, 2, 3, 6, 7, 5

                                2.     a sort by ID then Time should give your the following order:  4, 5, 1, 6, 2, 7, 3

                                 

                                I can't figure out how FM could have sorted record 5 to the end, based on what you have provided.

                                • 13. Re: Sort Order Bug

                                  Keywords said, "I can't figure out how FM could have sorted record 5 to the end, based on what you have provided."

                                   

                                  It doesn't and that is what is needed.  But there is no logical pattern Chris.  :-/

                                   

                                  Record ID      Start Time     Track ID

                                  4                     8:00:00  

                                  1                     10:00:00        25

                                  6                     11:00:00        25

                                  2                     10:00:00        26

                                  7                     11:05:00        26

                                  3                     10:00:00        27

                                  5                     15:00:00

                                   

                                  As keywords points out, the 15 is not in logical order because it should be up with the other 'empty' TrackID.  So TrackID 'blank' should have 8 and 15, Track 25 would have 10 and 11. Logic would dictate this order:

                                   

                                  Record ID      Start Time     Track ID

                                  4                       8:00:00  

                                  5                     15:00:00

                                  1                     10:00:00        25

                                  6                     11:00:00        25

                                  2                     10:00:00        26

                                  7                     11:05:00        26

                                  3                     10:00:00        27

                                   

                                  ... as also described by Keywords.  If you need this particular sort then you must provide the logic from which it comes.  You say you want it first in time order but Record ID 6 and 2 then are out of sequence as well. :^)