11 Replies Latest reply on Dec 8, 2011 3:57 PM by philmodjunk

    sorting timeframes

    WesleySantiago

      Title

      sorting timeframes

      Post

      My database won’t sort numbered time correct.
      One table represents “event” records, another represents timeframes to be referenced by the first.

      A major problem occurs when they are sorted. Instead of a pop-up showing:

      1 minute
      5 minute
      10 minute
      1 hour
      (where progressive time growth is natural to select from...)


      I get:
      1 hour
      1 minute
      10 minute
      5 minute

      ASCII sort strikes again. Does anyone have any hacks to ease this difficulty? (FM11)

        • 1. Re: sorting timeframes
          philmodjunk

          It's not just an ascii sort issue. You have two different units in the same field: hours and minutes. FileMaker can't tell that 1 hour is larger than 1 minute.

          You need to sort on a number field where all values use the same unit of measure.

          Here's a calculation field that will solve the immediate issue, but I would suggest using one field for the value and a drop down formatted field for recording the units here.

          Let ( [Units = RightWords ( TimeFrame ; 1 ) ; value = Leftwords ( TimeFrame ; 1 ) ] ;
                  If ( Units = "hour" ; value * 60 ; value )
                ) // Let

          make sure to specify Number as the return type for this calculation field and now you have a field on which you can sort your records correctly.

          • 2. Re: sorting timeframes
            WesleySantiago

            I must have something unusal going on.

            In my Timeframe table I made a second field called seconds, holding the amount of time in seconds.

            When manipulating the relationship between Events and Timeframe table, I choose to sort on the seconds field. Back in the Events layout, the pop-up (showing Timeframe values) was still incorrect.

            • 3. Re: sorting timeframes
              philmodjunk

              Is the seconds field of type number or text when you check it in Manage | database | fields?

              • 4. Re: sorting timeframes
                WesleySantiago

                The seconds field are numbers type.

                • 5. Re: sorting timeframes
                  philmodjunk

                  Then it should sort correctly barring damage to the field's index. Better check how you are sorting with this field and the values returned in this field very carefully. Test by sorting only on this one field.

                  If you are absolutely sure that the correct values are present in the file, but it is not sorting on this field correctly, you can try re-indexing the field:

                  Open Manage | Database | Fields

                  Double click the seconds field and use the storage tab to turn indexing Off.

                  Click Ok twice to close the field options and Manage | Database windows.

                  Re-open Manage | database and turn indexing for Seconds back on.

                  • 6. Re: sorting timeframes
                    WesleySantiago

                    I double checked everything. It still doesn't work. The seconds field weren't even indexed. I turned that on, and met no success.

                    • 7. Re: sorting timeframes
                      philmodjunk

                      Seems unlikely that you are sorting on that specific field if indexing was not on. (unless indexing is specifically set to "off", including it in a sort order should cause the field to be indexed.)

                      At this point, I can't suggest anything more without having a much more detailed view of your database design, the actual values in your seconds field and exactly how you are sorting your records.

                      • 8. Re: sorting timeframes
                        WesleySantiago

                        I'll do my best to put some screen shot up ASAP.

                        My database design is very plain vanilla at this point.

                         

                        I suspect the problem is related to File>Manage>Values, where there is no option to sort for a pop-up.

                        • 10. Re: sorting timeframes
                          philmodjunk

                          I suspect the problem is related to File>Manage>Values, where there is no option to sort for a pop-up.

                          Sometimes a picture is worth a thousand words or about ten thousand in this case.

                          I've been telling you how to sort records in a table and you've been asking for help in sorting values in a value list.

                          I can see these values come from a field in a table rather than a custom value list. FileMaker always sorts such a field in ascending order and since this field is a text field, it sorts them alphabetically and that's not the order you want. The bad news is that there isn't any simple way to change the order.

                          You can use custom values and arrange them correctly by listing them in the correct order in Manage | value lists.

                          You can opt to display the contents of two fields for your value list: TimeFrame as field 1, seconds as field two, then specify that the value list sort values by the second field. This only works as long as the value in seconds is unique for every record. And it looks ugly because you have to have that second field visible.

                          You can toss out the pop up menu and replace it with a portal to this table. Change the fields in the portal row into a button and clicking them can do the same thing as selecting the time frame in your current pop up. Since this is a portal, you can now sort by the seconds field.

                          You can also use a hidden sort field filled with spaces if your list of values is fairly small and if you don't need the second value list field for something else.

                          Add a number field, SortOrder, to your table. Number it in the order you want. (1,2,3 etc.) Keep to small integers for this.

                          Add a calculation field: Substitute ( 10^SortOrder - 1 ; 9 ; " " ) // when sort order is 1 you get 1 space, 2 produces 2 spaces and so on...

                          Use this calcultion field as your second field in the value list.

                          • 11. Re: sorting timeframes
                            philmodjunk

                            My apologies for not reading your original post more carefully...