1 2 Previous Next 29 Replies Latest reply on Jan 1, 2012 3:56 PM by dougcummings

    Sorting

    dougcummings

      Title

      Sorting

      Post

      I just started to use Filemaker. I have been using dBase for the last 25 years. However right away I have discovered a problem. I have one field which is important in my data files and it is absolutely imperitive that it be sortable in order. But Filemaker will only accept it if it is a number field. I want it so sort for both numbers and text as I often work with numbers have have text in them. They may start with a letter or letters in rare cases, or they may end in a letter or letters, or they may have a letter or letters in the middle of the number. Some examples would be 140F1340, 703C, DJ1401, etc. 

      For some reason whoever wrote the Filemaker program never took this scenario into consideration. It worked fine with dBase. 

      Another issue is foreign languages. I have not explored this one thoroughly but I notice that if I enter a word that has a foreign character in it, it leaves a space where that letter is. For instance ä just appears as a blank. Is this correctable? This symbol is created using alt+132. I have a lot of files that contain foreign words, usually when working with proper names or the names of cities, and other similar applications. 

      If the items cannot be accomodated within the program, is there someone who can do some custom programming so that they can. 

      Doug

        • 1. Re: Sorting
          raybaudi

          about sorting:
          It is unclear which have to be the wished sort in the given example.

          about foreign characters:
          I think that it is merely a font problem, not a FileMaker issue; try another font.

          • 2. Re: Sorting
            dougcummings

            1) I am unable to understand what you mean by the first response. What I want is for the sort to still come out in numerical order even with there being some text as part of the number. It has worked for me all these years, until I made the switch to Filemaker. Now it doesn't work. 

            2) As for the font, I do not think this is a font issue. The foreign characters and letters are in the character set section of Windows and are not a separate font. You should be able to get ä from your keyboard just by typing in alt+132. 

             

            Doug

            • 3. Re: Sorting
              raybaudi

               1) it is not clear what should be the sorting of the example you gave

              Maybe ( ? ) :

              703C
              DJ1401
              140F1340

              2) alt+132 works for me at least with arial, courier, tahoma, times new roman.

              • 4. Re: Sorting
                dougcummings

                a) As for sorting, what I am trying to sort by is their correct alphanumeric order. A high percentage of the numbers contain letters somewhere, so I have that column set for text entries. I used to use dBase and it was very easy to do a sort. All of the entries were aligned on the right hand side so that for the above set of numbers they would look like this (assuming this messages displays properly): 

                       703C 

                   DJ1401

                130F1340

                However when I imported the file to Filemaker all the entries in this field are all over the place making a logical sort impossible. 

                 

                My question is how do you handle this situation. I understand it will work fine if the field is set for numbers, but it will not work if it is set for text. But with dBase (the program I have used for the past 25 years) it worked perfectly as a text field so all of my files are set up that way. 

                 

                b) as for the foreign characters, this is a function of the Windows program, and has nothing to do with the font in use. If you use the alternate keyboard alt+132 gives you ä, alt+0225 gives you á, etc. You have to use the numbers on the right hand side of the keyboard. 

                Doug

                • 5. Re: Sorting
                  philmodjunk

                  I'm not sure that I see the logic to your sort order.

                  703C sorts before DJ1401 because 703 is less than 1401?

                  And what part of 130F1340 makes it greater than the other two values?

                  FileMaker sorts text as text. This is a standard Alphabetical order that is quite predictable once you understand the rules used and it's not unique to FileMaker.

                  I can't tell if you just need to ignore all nonnumeric characters in this sort or if you some how include them.

                  If you just need to sort the values as if they were:

                  703
                  1401
                  1301340

                  Then this calculation field, cNumbersOnly, can be used for your specified sort:

                  Filter ( TextField ; 09876543210 )

                  Select Number as the return type.

                  • 6. Re: Sorting
                    dougcummings

                    703C would be a lower number than 1401 as the basic number would be 703. There may also be a 703A and a 703B, and possibly others starting with 703. 

                    130F1340 could be by itself, but this is not likely. There would likely be a whole series of numbers starting with 130F but they may not all be in the same file - but most likely some would. 

                    Doug

                     

                    • 7. Re: Sorting
                      philmodjunk

                      In the case of 130F1340, how does the 1340 affect the sort order?

                      I can sse that you might have 130A, 130B, 130C, but what is the significance of the remaining digits?

                      I think you'll need to parse this text into separate number and text fields so that you can specify a sort order that sorts first on the numeric portion as a number then then on the text portion as text. That then leaves the third portion of this value to consider...

                      • 8. Re: Sorting
                        dougcummings

                        The sort order is important, 130F1339 is different from 130F1340 which is different from 130F1341, etc. , which is different from 130A1001, etc.  Ditto on 703A, vs 703B, vs 703C, etc. Just pretend there is no letter there, they are still unique and thus different and this is important in the sorting. I did not create these numbers, however they are a very important and critical part of the data. 

                        Doug

                        • 9. Re: Sorting
                          philmodjunk

                          I take it that this value comes in three parts: Number, Text and a third number and not all entries have all three values...

                          What I am suggesting is that you keep this field as imported, but use scripts or calculation fields to extract these three parts into separate fields of the appropriate data type so that you can use them for proper sorting.

                          Do you have FileMaker Advanced? a custom function can be set up that extracts all numbers to the left of the first non numeric character, a 2nd can be used to extract the text up to either the end of the string or the first numeric character. A third can extract the number following the non numeric characters. (And a single function might return all three in a list of 2 to three values...)

                          I suggest a custom function here as it can use recursion to loop through the characters of this text to extract the individual values.

                          Frankly, I'm amazed that this sorted correctly in any system. I'll take your word for it, but the format seems very inconsistent.

                          703C

                          comes before DJ1401?

                          How to tell that DJ in the second value is to be ignored but that C in the first value is not????

                          • 10. Re: Sorting
                            dougcummings

                            In a nutshell here is the situation. We are getting nowhere with the previous discussion. 

                            a) Sorting. It is impossible to sort data logically. Filemaker does not recognize, or does not appear to recognize, alpha numeric sorts, even though the data has been entered logically. (I am migrating from dBase). Filemaker makes a complete mess out of it and it does not come out in logical order. The number 46 usually comes out between 459 and 460, not between 45 and 47, as just one example. New data I enter can show up anywhere as there does not appear to be distinct columns for new entries. It is kind of a guess and hope you are lucky challenge. 

                            b) Fractions and foreign letters. They are all garbled in Filemaker. I use a lot of foreign characters and letters, primarily for proper names. As one example the fraction 1/2 now appears as a weird symbol instead of 1/2. I have thousands of records that use fractions or foreign characters. To go through and manually change each one would take me months of steady and repetitious work. And to get the two dots above the letter O (the first letter in Osterreich if spelled in German) it comes out as the tm symbol, so I (again) would have to change this everywhere it appears manually. And on and on.  

                            With dBase if I want to make a change to many entries, say I found a spelling error, it was very issue to issue a "replace" command and change them all instantly. Is there a way to do this in Filemaker? 

                            Going back to dBase is not an option. I would still be using it if it was not for the fact that program is no longer a user program but his migrated to a prgrammers lanuage and is far too complicated for the average user. 

                            These two features are important to me as I use them constantly. 

                            Doug

                            • 11. Re: Sorting
                              philmodjunk

                              With dBase if I want to make a change to many entries, say I found a spelling error, it was very issue to issue a "replace" command and change them all instantly. Is there a way to do this in Filemaker?

                              Look into the Replace field contents option found in the records menu and which can also be used in scripts. It's able to do a "batch update" of a specified field for every record in your found set. It can not only put the same value in this field for every record, but you can set up a calculation that makes current content specific changes to this field for every record.

                              FileMaker is very consistent and predictable in how it sorts values. It's just not sorrting them by the rules you expect. Since I still do not understand the logic to your current examples, I cannot be more specific. I do think that once that logic is adequately understood, it's possible to set up a sort that will work by breaking your field into pieces and sorting on each piece in the specified order. That does not mean you can't keep the current data as entered, but rather calculation fields can extract the relevant pieces and you specify them in your sort while keeping the original field intact.

                              • 12. Re: Sorting
                                dougcummings

                                I thought I was being fairly detailed in my explanation, but if not then please ask the questions you want answers to. In a nutshell, I have a column marked as the "number" column because that it was it is. But I can call it something else if that will solve the problem. With dBase it could be a text field and thus it was a text field and it sorted that alpha numerically. But Filemaker will not accept it as text field and still sort logically. I tried to explain it as simple as I could earlier. 

                                One problem I see with Filemaker is you do not have a fixed amount of spare for the each letter in the field. So 1111 would take up less space than 9999. I think, but am not sure, if this will create a problem as well. I always entered the numbers in that field so they were justified on the right hand side, so they would sort in numerical order. If there were letter suffixes they went on the end, if there were letter prefixes they went where they should be. So as before - they would be entered thusly (see below) and when sorted would be in order. 

                                 

                                112.345

                                     130F1350

                                     1967

                                 

                                If you need more information please ask questions. It makes sense to me, but it may not make sense to you. 

                                Doug

                                 

                                 

                                 

                                 

                                • 13. Re: Sorting
                                  raybaudi

                                  I need more information.


                                  If you really want to order this text ( it is absolutely not a number ), I need to know the maximum length of each character type.


                                  For example:

                                  ######@@@@#######

                                  corresponds to a maximum of 6 numbers followed by a maximum of 4 letters followed by up to 7 numbers.

                                  • 14. Re: Sorting
                                    philmodjunk

                                    please ask the questions you want answers to

                                    I did last week and still have not received an answer:

                                    Quoting from last week:

                                    Frankly, I'm amazed that this sorted correctly in any system. I'll take your word for it, but the format seems very inconsistent.

                                    703C

                                    comes before DJ1401?

                                    How to tell that DJ in the second value is to be ignored but that C in the first value is not????

                                    One detail that you may find helpful: If you put numerals into a text field, Filemaker will sort them in the expected order only if they all have the same number of characters. This can require "padding" them with leading zeroes.

                                    1, 5, 6, 12, 20, 50, 90 will not sort in numeric order:

                                    01, 05, 06, 12, 20, 50, 90 will sort in numeric order.

                                    The lack of such leading zeroes and the inclusion of text that may or may not (as I currently interpret your example values) also be part of what determines the sort order makes this appear inconsistent when it comes down to suggesting a method that works with FileMaker.

                                    1 2 Previous Next