10 Replies Latest reply on Mar 5, 2010 10:18 AM by LeoB

    sort script not sorting

    synergy46

      Title

      sort script not sorting

      Post

      I have a table (Transactions) that has these fields:

      TransactionID.pk (indexed, auto-enter, serial)

       

      I have this script: 

      Go to Layout ["transactions.rpt.ID" (transactions)]

      Enter Browse mode[]  */  Redundant, I know

      Sort Records [Restore; No Dialog]

      View as [View as List]

       

       

      As a script trigger which is run 'on layout load' and as a stand alone script, the script DOES NOT sort

      on the TransactionsID field.  I have an almost identical script that sorts on different fields in different layouts

      that works great.... Even clicking the Sort button the Status Bar and sorting manually does not work????

       

      Sort Selection

       

       

      Report output

       

       

        • 1. Re: sort script not sorting
          philmodjunk
            

          Perhaps your transactionsID field is a text field instead of the number field it is supposed to be?

           

          The order in your example appears to follow the rules for sorting text in descending order.

          • 2. Re: sort script not sorting
            synergy46
              

            Correct-a-mondo!!! And thank you very much for your insight.  Your observation was absolutely correct. 

             

             

            • 3. Re: sort script not sorting
              philmodjunk
                 It was familiar because I've made that mistake myself. I've also encountered unusual cases where I had to get numbers in a text field to sort correctly.
              • 4. Re: sort script not sorting
                LeoB

                Phil:


                 

                 

                 

                Well, I had made the same mistake as the original poster -- ID fields incorrectly labeled as text fields.  However, even after correcting all the ID fields it still will not sort.


                 

                 

                 

                This is a script to go from a main menu to the primary table in the file.  I want it to go to the layout, find all current year records, and then sort them with the most recent records first:

                 

                 

                 

                    

                     Go to Layout ["Record [Activity Record]" (Activity)]

                     Perform Find [Restore]

                     Sort Records [Restore; No dialog]  {CreationDate, descending order}

                     Show Custom Dialog


                 

                 

                There is an OnLayoutLoad script on the Activity Record, but it has no sort function in it.  I tried removing that script entirely, but no change.


                 

                The script goes to the correct layout, performs the find correctly, but no matter what it always shows the oldest record, not the latest record.


                 

                 

                Any other ideas come to mind?

                 

                Thanks

                • 5. Re: sort script not sorting
                  mrvodka

                  and the creation date field is def a date field right?

                  • 6. Re: sort script not sorting
                    LeoB

                    Yes.  In Manage Database the field type for CreationDate is Date and the other attributes are:

                     

                     

                    Auto-Enter checked for Creation Date

                    Prohibit modification of value during data entry

                    Validate only during data entry

                    Indexing all

                     

                     

                     

                     

                    When I look at the table in table view I can see all the creation dates listed.

                     

                     

                     

                    Thanks

                    • 7. Re: sort script not sorting
                      philmodjunk

                      What happens if you just select sort from the Records menu while you have this layout up?

                       

                      Also, since this was originally a text script, perform a find on the field and enter a question mark as the sole criteria. This will find all records with an invalid date entered in the field.

                      • 8. Re: sort script not sorting
                        LeoB

                        What happens if you just select sort from the Records menu while you have this layout up?

                         

                        It still does not view correctly.  It does sort correctly in table view, but see new twist below.

                         

                        Also, since this was originally a text script, perform a find on the field and enter a question mark as the sole criteria. This will find all records with an invalid date entered in the field.

                         

                        I only have about 25 records in the table and when I look at them in table view I can see all the creation dates entered correctly.  When I did enter a ? in the creation date field in Find Mode, it found no records.

                        -------------------------------------------------------------------------------------------------------------------------------------------------------------------

                        Now, a new twist to this.  Well, not new, just something I noticed while spending what seems like hours trying to figure this out.

                         

                        As I mentioned earlier, the script is run off a button on the Main Menu to take the user to the Activity Records table and then sort the records.  There are three buttons:  All Years, Current Year, and Previous Year.  All are doing the same thing.  The All Years button script only has a sort function while the other two have a find function and then a sort function.

                         

                        In the Activity Records table I have a script that runs OnLayoutKeystroke that inserts a new timestamp in a field every time the record is modified in any way.  This timestamp is used in a script that runs OnRecordLoad that compares that timestamp with the timestamp entered when a manager approves the record.  If the record has been changed in any way since the manager approved it, it removes the approval and puts it back in the queue for the manager to reapprove.  All that seems to work fine.

                         

                        The twist that I found was that when I use those buttons to go to the Activity Records layout, the records are actually being sorted properly when I look at the layout in table view.  What is happening is that the first record to pop up is the one with the latest modification timestamp!

                         

                        I removed all the layout script triggers on the Activity Records layout (there were three: OnLayoutLoad, OnLayoutKeystroke, and OnRecordLoad).  But that did not change anything.  All buttons take the user to the layout and the first record to display is the one with the latest modification timestamp, although in table view all of the records are sorted correctly.

                         

                        I also tried changing the sort from creation date to the record ID.  The same thing happens: the first record displayed is the one with the latest modification timestamp, even though in table view the records are sorted correctly.  In table view the record with the latest modification timestamp has a black bar on the left side of the record showing it's the one displayed.  This happens with all of the layout script triggers in place, and with them all removed.

                         

                        I tried adding Show All Records to the originating script, with no change.

                         

                        So now, even if I disable that modification timestamp, the records still won't show correclty.  Is there some function that can be added to the script to force it to show the first record in the sort or found set?

                         

                        Thanls for all the help.

                         


                         


                        • 9. Re: sort script not sorting
                          philmodjunk

                          Go to record [first]

                           

                          Will make the first record in the found set the current record.

                          • 10. Re: sort script not sorting
                            LeoB

                            Yep -- worked like a charm.  Now I feel dumber than my three dogs laying here snoring.

                             

                            Thanks (again)