13 Replies Latest reply on Jun 9, 2015 12:18 AM by raybaudi

    Sorting Text Fields Containing Both Text

    walrus28

      Title

      Sorting Text Fields Containing Both Text & Numbers

      Post

       

      I have a text field called item_name that contains names on product packaging.

      I would like to sort my records based on this field. I would like it to sort as text, but to sort numbers as numbers, even when the numbers are part of a string of text. 

      Numbers can be present at the beginning, middle, end, multiple times (up to 5) or not at all. 

      Here are some sample entries in the order I would like to sort them.

             
      • 5/1 Pens
      •      
      • 24/7
      •      
      • 80 Dogs
      •      
      • 500 Dogs
      •      
      • 8,000 Dogs 9 Pigs 39
      •      
      • 8,000 Dogs 55 Pigs 8
      •      
      • 8,000 Dogs 55 Pigs 39
      •      
      • Comin' @ You
      •      
      • Comin' 4 You
      •      
      • Cool Train 9
      •      
      • Cool Train 387
      •      
      • Pizza
      •      
      • Real 70's Big
      •      
      • Real 70's Small
      •      
      • Real 600's

       

      (I made up the names, but they follow the same patterns as the names actually in my field.)

       

        • 1. Re: Sorting Text Fields Containing Both Text & Numbers
          philmodjunk

          Do you have FileMaker Advanced? I am thinking of a custom function that extracts the first number from this field but that won't be an option for you without Advanced.

          • 2. Re: Sorting Text Fields Containing Both Text & Numbers
            walrus28

            No, I do not have FileMaker Advanced. 

            • 3. Re: Sorting Text Fields Containing Both Text & Numbers
              walrus28

              Could I use a script instead?

              • 4. Re: Sorting Text Fields Containing Both Text & Numbers
                philmodjunk

                Yes a looping script can achieve the same result. A custom function is just a bit easier to implement once defined. The problem with a script is that you have to make sure that it runs to update this special sort field every time the original field shown above is updated.

                Set Variable [$L ; value: Length ( YourTable::LabelField ) ]
                Loop
                    Set Variable [$K ; value: $K + 1 ]
                    Exit Loop If [ $K > $L // we've scanned past the end of the text in the label field ]
                    Exit Loop if [ PatternCount ( "987654321" ; Middle ( YourTable::LabelField ; $K ; 1 ) ]
                    Set Variable [$Numb ; value: $Numb & Middle ( YourTable::LabelField ; $K ; 1 )]
                End Loop
                Set Field [YourTable::NumbSort ; If ( IsEmpty ( $Numb ) ; 9999999999999999 ; getasumber ( $Numb ) ]

                Now, when sorting your records sort them by NumbSort as the first field in your sort order and by LabelField as the second field in your sort order. (NumbSort must be of type Number and LabelField must be of type text.)

                • 5. Re: Sorting Text Fields Containing Both Text & Numbers
                  philmodjunk

                  Left something out:

                  Set Variable [$L ; value: Length ( YourTable::LabelField ) ]
                  Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Exit Loop If [ $K > $L // we've scanned past the end of the text in the label field ]
                      Exit Loop if [ Not PatternCount ( "987654321" ; Middle ( YourTable::LabelField ; $K ; 1 ) ]
                      Set Variable [$Numb ; value: $Numb & Middle ( YourTable::LabelField ; $K ; 1 )]
                  End Loop
                  Set Field [YourTable::NumbSort ; If ( IsEmpty ( $Numb ) ; 9999999999999999 ; getasumber ( $Numb ) ]

                  • 6. Re: Sorting Text Fields Containing Both Text & Numbers
                    walrus28

                    Thanks but this does not seem to work for numbers that are not at the beginning of the field name. 

                    • 7. Re: Sorting Text Fields Containing Both Text & Numbers
                      philmodjunk

                      Correct. From your example, it did not appear that numbers embedded later on in the string had any effect on the sort order.

                      Note that "cool train 9" sorts after "24/7" in your example.

                      • 8. Re: Sorting Text Fields Containing Both Text & Numbers
                        walrus28

                        Yes "cool train 9" sorts after "24/7", but "8,000 Dogs 9 Pigs 39" sorts before "8,000 Dogs 55 Pigs 8".

                        • 9. Re: Sorting Text Fields Containing Both Text & Numbers
                          philmodjunk

                          Yes, the example script does not handle that situation.

                          The script to do that, which would need a second loop inside the first would take more time to set up than I currently have available.

                          • 10. Re: Sorting Text Fields Containing Both Text & Numbers
                            raybaudi

                            This calculation is able to manage all your given examples:
                            ( if item_name can contain more than 5 words, the calc must be modified )

                            Let ([
                            text = item_name ;
                            dec = Left ( 1/2 ; 1 ) ;
                            adj = Substitute ( text ; [ "/" ; dec ] ; [ " " ; ¶ ] ) ;
                            w1 =  GetValue ( adj ; 1 ) ;
                            w2 =  GetValue ( adj ; 2 ) ;
                            w3 =  GetValue ( adj ; 3 ) ;
                            w4 =  GetValue ( adj ; 4 ) ;
                            w5 =  GetValue ( adj ; 5 )
                            ];
                            Case (
                            PatternCount ( w1 ; dec ) ; Right ( "0000000000" &  Truncate ( w1+1/1000 ; 3 ) ; 12 ) ;
                            Div ( w1 ; 1 ) ; Right ( "00000000" & w1+,001 ; 12 ) ;
                            w1
                            )
                            &
                            Case (
                            PatternCount ( w2 ; dec ) ; Right ( "0000000000" &  Truncate ( w2+1/1000 ; 3 ) ; 12 ) ;
                            Div ( w2 ; 1 ) ; Right ( "00000000" & w2+,001 ; 12 ) ;
                            w2
                            )
                            &
                            Case (
                            PatternCount ( w3 ; dec ) ; Right ( "0000000000" &  Truncate ( w3+1/1000 ; 3 ) ; 12 ) ;
                            Div ( w3 ; 1 ) ; Right ( "00000000" & w3+,001 ; 12 ) ;
                            w3
                            )
                            &
                            Case (
                            PatternCount ( w4 ; dec ) ; Right ( "0000000000" &  Truncate ( w4+1/1000 ; 3 ) ; 12 ) ;
                            Div ( w4 ; 1 ) ; Right ( "00000000" & w4+,001 ; 12 ) ;
                            w4
                            )
                            &
                            Case (
                            PatternCount ( w5 ; dec ) ; Right ( "0000000000" &  Truncate ( w5+1/1000 ; 3 ) ; 12 ) ;
                            Div ( w5 ; 1 ) ; Right ( "00000000" & w5+,001 ; 12 ) ;
                            w5
                            )
                            )

                            • 11. Re: Sorting Text Fields Containing Both Text & Numbers
                              raybaudi

                              ...or you could use a looping script, something like:

                              Set Field [ YourTable::result ; "" ]
                              Loop
                              Exit Loop If[ $count > WordCount ( YourTable::item_name ) ]
                              Set Variable [ $count ; Value: $count + 1 ]
                              Set Variable [ $word ; value:
                                                           Let ([
                                                           dec = Left ( 1/2 ; 1 ) ;
                                                           adj = Substitute ( YourTable::item_name ; [ "/" ; dec ] ; [ " " ; ¶ ] ) ;
                                                           w =  GetValue ( adj ; $count )
                                                           ];
                                                           Case (
                                                           PatternCount ( w ; dec ) ; Right ( "0000000000" &  Truncate ( w+1/1000 ; 3 ) ; 12 ) ;
                                                           Div ( w ; 1 ) ; Right ( "00000000" & w+,001 ; 12 ) ;
                                                           w
                                                           )
                                                           )
                              ]
                              Set Field [ YourTable::result ; YourTable::result & $word ]
                              End Loop

                              • 12. Re: Sorting Text Fields Containing Both Text & Numbers
                                walrus28

                                Thanks for the ideas PhilModJunk and raybaudi. 

                                Here's the script I ended up using: 

                                Set Variable [ $name ; value: table::item_name  ]

                                Set Variable [ $L ; value: Length ( $name ) ]

                                Set Variable [ $N ; value: 1 ]

                                Set Variable [ $Number ; value: “”]

                                Set Variable [ $SortName ; value: “” ]

                                Set Variable [ $Decimal ; value: 0 ]

                                Set Variable [ $DecimalPoint ; value: Left(1/2;1) ]

                                 

                                Loop

                                 

                                    If [ PatternCount ( “0123456789” ; Middle ( $name; $N; 1 ) ) and $Decimal = 0 ]

                                        Set Variable [ $Number ; value: $Number & Middle ( $name; $N; 1)]

                                    Else

                                        If [ $Number  ≠  “” ]

                                            Set Variable [ $SortName ; value: $SortName & Right ( “0000000000” & $Number ; 10 ) ]

                                            Set Variable [ $Number; value:””]

                                        End If        

                                        Set Variable [ $SortName ; value: $SortName & Middle ( $name; $N; 1) ]

                                        If [ PatternCount ( $DecimalPoint; Middle ( $name; $N; 1 ) ) or $Decimal = 1]

                                            Set Variable [ $Decimal ; value: 1 ]

                                        Else

                                            Set Variable [ $Decimal ; value: 0 ]

                                        End if

                                    End If

                                 

                                    If [ $N = $L AND $Number  ≠  “” ]

                                        Set Variable [ $SortName ; value: $SortName & Right ( “0000000000” & $Number ; 10 ) ]

                                    End If

                                 

                                    Set Variable [ $N ; value: $N + 1 ]

                                    Exit Loop if [ $N > $L ]

                                 

                                End Loop

                                Set Field [ table::item_name_sort ; $SortName ]

                                • 13. Re: Sorting Text Fields Containing Both Text & Numbers
                                  raybaudi

                                  When ( and If ) you'll use an advanced version of Filemaker, try this custom function:

                                  https://www.briandunning.com/cf/1785