7 Replies Latest reply: May 15, 2014 9:55 AM by Debby _ RSS

    Sorting Data

    Debby _

      I am a new user of only 3 days.  My problem is the data I need is showing correctly in the table window, but when I view it in the summary window the invoice numbers are displayed in ascending/descending order.  I want the data to remain in the same order that is shown in the table window.  What should I do?

        • Sorting Data
          Grant Perkins

          Hi Debby,

           

          Welcome to the forum.

           

          What is the order of your records on the table? If you want the same order you probably need to have the same field somewhere in your summary and sort by it, otherewise the order will be that of the first key field which I'm guessing is Invoice Number.

           

          So if your data is sorted by, for example, customer number in the table you need to make Customer Number a key field in the summary as well so that the summary can use it for sorting.

           

          I hope this helps.

           

           

          Grant

           

           

          Originally posted by Debby:

          I am a new user of only 3 days.  My problem is the data I need is showing correctly in the table window, but when I view it in the summary window the invoice numbers are displayed in ascending/descending order.  I want the data to remain in the same order that is shown in the table window.  What should I do? /b[/quote]

          • Sorting Data
            Debby _

            My report has to first be sorted by company number, then customer name (both ascending).  However, the invoice number doesn't needed to be sorted.  Here is a small portion of the information I am using (in notebook form).  The invoice number IOT 551 starts at the left margin, while UAR 3437 and UAR 3855 are indented by one space directly under the IOT 551.  I need the summary to list the UAR's under the IOT (not above it in ascending order).

             

             

            Date   : 09-30-03                                        AGING ANALYSIS SORTED BY COMPANY, FIN. GROUP, CUST. (LITTON)                                                                Page      :    1

            Blacksburg Production                                                                                Company   :  101

            rtfacr24201200c2                                                              Requested Companies   : 101 - 103                                                                             polandl

            -


            Aging Analysis Code   :  30  30 Day Aging                                               Company             : 101                                               Customer         from :

            Aging On              : Document date                                                   Fin. Customer Group : ICO  Litton Customer                                               to   : ZZZZZZ

            As on Date            : 09-30-2003

            -


            Invoice           |Inv. Date|Due Date|  Inv Amnt - HC | Amnt Rcvd - HC | Balance in HC  |                             Open Amount in HC  Between                             |Reference

              Receipt Document|Rec.Date |        |                |                |                |--


            +--


                               

            Current

            31-60

            61-90

            91-120

            over 120

            -


             

             

            Customer: AM3626  Ametek Aerospace Products, Inc.        Credit Limit:     1000000.00       Order Balance:           0.00         20 Massachusetts

            IOT      551    19| 11-13-01|        |                |       1,260.00 |         243.32-|                |                |                |                |         243.32-| OP/sp1 7341a

            UAR    3437     1| 02-18-03|        |                |         407.68-|                |                |                |                |                |                | OP/sp1 7341a

            UAR    3855     1| 08-18-03|        |                |         609.00-|                |                |                |                |                |                | OP/sp1 7341a

                 TOTAL FOR CUSTOMER   AM3626                 0.00           243.32           243.32-            0.00             0.00             0.00             0.00           243.32-

            • Sorting Data
              Grant Perkins

              Debby,

               

              am I correct in understanding that the order you get is fine EXCEPT that the UAR references are list ABOVE the IOT's?

               

              If so, are you selecting both IOT and UAR lines from the same template - which I assume is the detail template?

               

              Here's a guess.

               

              All summary keys will have a sort applied to them within the position they occupy in the summary definition. Have perhaps got a SPACE at the front of the UAR lines? That would put them abobe the IOT line with no space in front of it.

               

              If that guess is accurate the quickest solution would, I think,  be to create a temporary field in your extract table and make its formula

               

              LTRIM()

               

               

              That will copy the contents of the original field into the new field and remove the leading spaces. Use the new field as a key in your summary and I think everything should display as you want it to.

               

              If it doesn't, let us know. I will be looking for alternatives while you try the suggestion.

               

              Best of luck.

               

              Grant

              Originally posted by Debby:

              My report has to first be sorted by company number, then customer name (both ascending).  However, the invoice number doesn't needed to be sorted.  Here is a small portion of the information I am using (in notebook form).  The invoice number IOT 551 starts at the left margin, while UAR 3437 and UAR 3855 are indented by one space directly under the IOT 551.  I need the summary to list the UAR's under the IOT (not above it in ascending order).

               

              [quote][font="courier"]  Date   : 09-30-03                                        AGING ANALYSIS SORTED BY COMPANY, FIN. GROUP, CUST. (LITTON)                                                                Page      :    1

              Blacksburg Production                                                                                Company   :  101

              rtfacr24201200c2                                                              Requested Companies   : 101 - 103                                                                             polandl

              -


              Aging Analysis Code   :  30  30 Day Aging                                               Company             : 101                                               Customer         from :

              Aging On              : Document date                                                   Fin. Customer Group : ICO  Litton Customer                                               to   : ZZZZZZ

              As on Date            : 09-30-2003

              -


              Invoice           |Inv. Date|Due Date|  Inv Amnt - HC | Amnt Rcvd - HC | Balance in HC  |                             Open Amount in HC  Between                             |Reference

                Receipt Document|Rec.Date |        |                |                |                |--


              +--


                                 

              Current

              31-60

              61-90

              91-120

              over 120

              -


               

               

              Customer: AM3626  Ametek Aerospace Products, Inc.        Credit Limit:     1000000.00       Order Balance:           0.00         20 Massachusetts

              IOT      551    19| 11-13-01|        |                |       1,260.00 |         243.32-|                |                |                |                |         243.32-| OP/sp1 7341a

              UAR    3437     1| 02-18-03|        |                |         407.68-|                |                |                |                |                |                | OP/sp1 7341a

              UAR    3855     1| 08-18-03|        |                |         609.00-|                |                |                |                |                |                | OP/sp1 7341a

                   TOTAL FOR CUSTOMER   AM3626                 0.00           243.32           243.32-            0.00             0.00             0.00             0.00           243.32- /QB[/font][/quote][/quote]

              • Sorting Data
                Debby _

                Grant,

                I used your suggestion and that worked for the example I showed you.  However, my report is a little more complex.  Other transactions in my aging report are not suppose to be alpabetical.  For example the invoice number may be SP1 5927 with 2 CRV line items listed below that invoice (indented by 1 space) then back to another SP1 invoice (with no indention).  With your suggestion all of my transactions were put in alphabetical order.  Instead I need the transactions that are at the left margin to be alphabetical, but the transactions indented by one space listed directly under the original transaction line. 

                Sorry to be so confusing & thanks again for sharing your knowledge.

                • Sorting Data
                  Debby _

                     

                  Grant

                  I think I figured it out, or atleast it is appearing in the summary the way I need it to.  I used the Recno function.  With the lines assigned a number it extracted to the summary field correctly.  Please let me know if you had any other suggestions.

                   

                  Thanks again.

                  • Sorting Data
                    Grant Perkins

                    Hi Debby,

                     

                    Isn't it great the way an inert report can keep throwing up new learning opportunities!

                     

                    This one ssems to have been particularly useful as a step by step tutorial with real purpose - which I hope you will agree is actually quite a good way to gain experience fast and memorably.

                     

                    Your solution sounds good to me. (It's also an opportunity to appreciate the difference between RECNO and ROWNO functions.) Since you want to keep the same order as the original report for level of summary sorting RECNO seems perfect and is a more complete solution given the extended list of document type codes you need to allow for.

                     

                    Quite an interesting challenge for a first project. How do you feel about the result?

                     

                    Grant

                     

                    Originally posted by Debby:

                        

                    Grant

                    I think I figured it out, or atleast it is appearing in the summary the way I need it to.  I used the Recno function.  With the lines assigned a number it extracted to the summary field correctly.  Please let me know if you had any other suggestions.

                     

                    Thanks again. /b[/quote]

                    • Sorting Data
                      Debby _

                      I love the results. We do the accounting for 3 separate locations and this will allow us to combine all the data and export it to excel. Our life will be much simpler the next time auditors arrive.  I am giving a thumbs up to our IS department and recommending that they purchase more licenses for other users.   Thanks for your support and quick responses.