11 Replies Latest reply on Jun 18, 2013 4:17 PM by philmodjunk

    Numeric sort not working.

    DeirdreHealey

      Title

      Numeric sort not working.

      Post

            

                          Recently have been unable to sort my database by item number.  Numbers 41 and 42 out of 59 records somehow show up at the end.  Al other records are in correct order.

                           

                          Repost follows:

                          I have my items listed numerically & have had a problem recently with my 2013 items. I now have 59 items listed & have 2 that will not stay in order. Numbers 1-40 are ok & numbers 41 & 42 consistently are after the last item added--ie, # 59 is no longer last. Early on, with less numbers, I had problems also, but now it seems to be only these 2. I have done many sorts & gone into that area & erased all & resorted to no avail.

                          I have just purchased the new imac & my os is 10.8.3.  I had hoped the new computer would solve my problem, but it remains the same.

                           

            

        • 1. Re: Numeric sort not working.
          schamblee

               Have you ran recover on the file to verify there are no errors in the databases?  If you haven't I would recommend running recover.

          • 2. Re: Numeric sort not working.
            philmodjunk

                 If the recovered copy sorts correctly, it could be that the recover fixed the problem by rebuilding a damaged index. You can use advanced recovery options to rebuild your indexes without making any other changes to the file:

                 If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

                   
            1.           With the file closed, select Recover from the File Menu.
            2.      
            3.           Select "Use advanced Options"
            4.      
            5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
            6.      
            7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.
            • 3. Re: Numeric sort not working.
              DeirdreHealey

                   I ran Recover & Advanced Recover as suggested & the problem became worse. I made a copy so I would not have to worry about my actual file. I have not added any more inventory ,but will soon. 

              • 4. Re: Numeric sort not working.
                RickWhitelaw

                     I would have simply turned off indexing for the number field by which you're sorting, closed  Manage Database, reopened  it, and turned indexing back on. This should rebuild your index.

                • 5. Re: Numeric sort not working.
                  philmodjunk

                       Which is a method that should produce results identical to the advanced recover method I described eariler. I recommend the advanced recover method most of the time here in the forum as it avoids the issue of correctly selecting the right field for which to rebuild the index as it rebuilds all indexes in the file.

                       The fact that "the problem became worse." when the file was recovered suggests that either there is some other issue invovled or that the file is damaged beyond the recover tool's ability to repair the file.

                       Deirdre, can you describe exactly what you mean by "the problem became worse"? There might be a clue there as to what's going wrong here.

                  • 6. Re: Numeric sort not working.
                    symbister

                         is it possible that numbers 41 and 42 have a leading character (eg a space) - and we're all assuming that it's a number field, but is it possibly a text field ?

                    • 7. Re: Numeric sort not working.
                      philmodjunk

                           Good point. I had ruled that out in my own thinking because if this is a text field, you'd expect to get other incorrect sorting such as 11 sorting before 2, but perhaps these numbers are padded with leading zeroes...

                      • 8. Re: Numeric sort not working.
                        DeirdreHealey

                             Sorry for the slow response--by "got worse" I meant that more numbers were showing up out of order than before running the recover.  Should I assume the "turning off indexing for a single field" method would fail as well?  I don't know how to do that.  Thanks!

                        • 9. Re: Numeric sort not working.
                          philmodjunk

                               It's just another way to rebuild the index for a field. It should not produce any different effect from using the advanced recover option. The only difference is that the Advanced Recover method rebuilds all the indexes for all fields while enabling, disabling indexing for a single field rebuilds the index(es) for just that one field.

                               Is this a field of type number?

                          • 10. Re: Numeric sort not working.
                            DeirdreHealey

                                 The file type is "number" although it lets me enter whatever characters I want (all records currently have 4 digits--all numeric).

                            • 11. Re: Numeric sort not working.
                              philmodjunk

                                   Then I must repeat what I posted earlier:

                                   Either the file is damaged or there is some factor we can't see without examiniing an actual copy of your file.

                                   I don't recall if I suggested this before, but since rebuilding the indexes did not resolve this, try a full up recover and see if any issues are discovered and even if no problems are reported by the recover process, test the recovered copy to see how records sort.

                                   Things to keep in mind about Recover:

                                   While Recover almost always detects and fully corrects any problems with your file...

                                     
                              1.           The recovered copy may behave differently even if recover reports "no problems found".
                              2.      
                              3.           Recover does not detect all problems
                              4.      
                              5.           Recover doesn't always fix all problems correctly
                              6.      
                              7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

                                    

                                   And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).