7 Replies Latest reply: May 15, 2014 9:54 AM by Mike Urbonas RSS

    Trying to keep records in the same order as report

    Nick Osdale-Popa

      I have a Purchase Order report that have different detail lines whether the report is Over $250 or Under $250.  This obviously uses 2 models for each detail.

       

      I was hoping the Line()[/b] function would return the physical line of the report that the detail was on, but this reports the Line number within the detail.

       

      I need some way to merge the two outputs back in the same order as the original report.

       

      Just to give some idea of how the report would look:

      There could be 3 PO's that are Over $250 then the 4th one could be Under. I tried using  Recno()[/b] but since Record #1 of the Under would be the 4th physical record in the report, when I resort the data it appears after Record #1 of the Over, I need it to appear as the 4th record again.

       

      I've looked through the Functions to see if there was one that reported the physical line of the report from which the data is being retrieved, but I didn't see such a beast.

       

      Unfortunately, there isn't a date entry field , "Record Number", or any other item on the report that shows an order to the report. There is an Date Entered field actually in the database from which this report is being generated and I believe that is the sorted order that report is being produced, but I don't have access to the report code to change it so that it shows this field.

       

      I hope this makes sense.  Let me know if more info is needed.

        • Trying to keep records in the same order as report
          Grant Perkins

          Nick,

           

          I just ran a quick check on my V5.02 installation using the functions "Page()" and "Line()" against a report which auto formats to 256 line 'pages'. The detail selections are discrete single lines. If you are using multi-line templates or any of the other special selection facilities my understaning is that "Line" will return the Last Line Number of the selection made by the template. This would not help you to order the data within the selection but would allow multi line selections to be ordered within the table. However I would expect "Rowno" to be more useful for that in most circumstances.

           

          The results show the 'page' number and page line number of each record selected, just as you expected them to. So at first sight it looks like you should be able to get what you want to restore the different transactions to the order you require.

           

          The "Rowno()" function should give the record number in the detail table so using "Line()" ought to be correct for your purposes.

           

          Could there be some other hidden factor involved here? Are you using V5 or V6 for this?

           

          I hope this suggests something to look into.

           

          Grant

           

              Originally posted by Nick Osdale-Popa:

          I have a Purchase Order report that have different detail lines whether the report is Over $250 or Under $250.  This obviously uses 2 models for each detail.

           

          I was hoping the Line()[/b] function would return the physical line of the report that the detail was on, but this reports the Line number within the detail.

           

          I need some way to merge the two outputs back in the same order as the original report.

           

          Just to give some idea of how the report would look:

          There could be 3 PO's that are Over $250 then the 4th one could be Under. I tried using  Recno()[/b] but since Record #1 of the Under would be the 4th physical record in the report, when I resort the data it appears after Record #1 of the Over, I need it to appear as the 4th record again.

           

          I've looked through the Functions to see if there was one that reported the physical line of the report from which the data is being retrieved, but I didn't see such a beast.

           

          Unfortunately, there isn't a date entry field , "Record Number", or any other item on the report that shows an order to the report. There is an Date Entered field actually in the database from which this report is being generated and I believe that is the sorted order that report is being produced, but I don't have access to the report code to change it so that it shows this field.

           

          I hope this makes sense.  Let me know if more info is needed.[/b][/quote]

           

          [size="1"][ January 04, 2003, 07:51 AM: Message edited by: Grant Perkins ][/size]

          • Trying to keep records in the same order as report
            Nick Osdale-Popa

            I don't think RecNo(), Line(), or even grabbing the Page # is going to help.

             

            There are two different models because the traps are completely different for each detial section of the model. Here is a schematic of how the report can look:

            [font="courier"]Header Info

            Detail250 Detail250 Detail250 Detail250

            Detail250 Detail250 Detail250 Detail250

            :

            :

            Header Info

                Detail251    Detail251 

            Header Info

                Detail251    Detail251 

            :[/font][/quote][b]Header info[/b] is the same for each "type"

            Detail250[/b] is how a line of PO's under $250 would look

            Detail251[/b] is how a PO of over $250 looks.

             

            Nothing is common between the two details to create a faux trap and glean appropriate info.

             

             

            Now given the above info the report could have 50

            Under250's spanning 3 pages, then have 50 Over250's spanning 2 pages.

             

            Line() reports the last line of the trap line, so if a Trap takes up to 12 lines one time and then only 10 lines the next, you'll get 12 and 10 respectively.

             

            Recno() reports the records it returns, thus I'll get records 1-50 for Under250's and records 1-50 for Over250's.  When I merge these two reports (in Excel) and sort by Recno() Record 1 of Over250 will come after Record 1 of Under250, when it should be after Record 50 of Under250. It would be ideal if the report was layed out like this (Under250's then Over250's), obviously you just don't sort, but the report is going to be interspersed with groups of Over250's and Under250's.

             

            Does this make it clear of the delimma I'm facing?   :confused:

            • Trying to keep records in the same order as report
              Grant Perkins

              Nick,

               

              Yep, I see the problem. I've had a similar problem before (2 different detail line formats) but it was easier for me as the data in the line provided enough information for any restructuring required.

               

              If you can use a SINGLE LINE (discrete) detail my suggestion should work. I do appreciate it could be a big IF! Multiline selections could never be reliable, as far as I can imagine, even if they worked part of the time. But then you know that already.

               

              If you can, in one model, trap the "Detail250...." line using the 'D' in the first char position on the line (ie non-blank first character) and some subsequent unique layout features which don't clash with your 'Header Info' line, you have a half chance of success.

               

              Trapping on a single discrete line will bring the

              Line() function into play to give the 'Line on Page' number. (Page number sounds like it would be useful as well if Line number works.

               

              Your second model would of course be the same except that the 'Detail251' trap would be "    D" to pick up the indented lines only. (Four spaces and D based on the sample format offered.)

               

              Each detail line (provided it comes from the same report!   ) must have a different Line and Page number combination.

               

              You mention a 'line' of PO's for the Detail250 records but a PO (Single) for the Over 250 record. Is one simply a list of PO numbers and the other a detail of fields from the PO? Sorry if that sounds like a picky question - I feel much more comfortable when I can see the real data as it often answers such questions before they need to be asked. And of course the data details can sometimes suggest new ways of looking at the problem.

               

              If there is no way you can use a single line detail selection trap I think it may be a lost cause. One for the new features list!

               

              But if anything new ideas come to mind I will let you know immediately.

               

              Grant

               

              PS. I don't for one second believe that you could be lucky enough with the real data BUT the sample format does allow the selection of both line types in a single trap using the | numeric OR trap. Of course there would still be the problem of defining the data fields which are bound to overlap ...

               

               

                Originally posted by Nick Osdale-Popa:

              I don't think RecNo(), Line(), or even grabbing the Page # is going to help.

               

              There are two different models because the traps are completely different for each detial section of the model. Here is a schematic of how the report can look:

              [font="courier"]Header Info

              Detail250 Detail250 Detail250 Detail250

              Detail250 Detail250 Detail250 Detail250

              :

              :

              Header Info

                  Detail251    Detail251 

              Header Info

                  Detail251    Detail251 

              :[/font][/quote][b]Header info[/b] is the same for each "type"

              Detail250[/b] is how a line of PO's under $250 would look

              Detail251[/b] is how a PO of over $250 looks.

               

              Nothing is common between the two details to create a faux trap and glean appropriate info.

               

               

              Now given the above info the report could have 50

              Under250's spanning 3 pages, then have 50 Over250's spanning 2 pages.

               

              Line() reports the last line of the trap line, so if a Trap takes up to 12 lines one time and then only 10 lines the next, you'll get 12 and 10 respectively.

               

              Recno() reports the records it returns, thus I'll get records 1-50 for Under250's and records 1-50 for Over250's.  When I merge these two reports (in Excel) and sort by Recno() Record 1 of Over250 will come after Record 1 of Under250, when it should be after Record 50 of Under250. It would be ideal if the report was layed out like this (Under250's then Over250's), obviously you just don't sort, but the report is going to be interspersed with groups of Over250's and Under250's.

               

              Does this make it clear of the delimma I'm facing?     :confused:   /b[/quote]

               

              [size="1"][ January 10, 2003, 08:23 PM: Message edited by: Grant Perkins ][/size]

              • Trying to keep records in the same order as report
                Nick Osdale-Popa

                Grant, I've posted the report layout in an earlier post. Go  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000008"]here[/url] to see it.

                 

                [size="1"][ May 18, 2006, 05:42 PM: Message edited by: Todd Niemi ][/size]

                • Trying to keep records in the same order as report
                  Gareth Horton

                  Nick, will using File() help to determine which file it came from help?

                   

                  This could give you another sort key for Excel, if you make it put out a relevant numeric value with a calcualted field.

                   

                  i.e

                   

                  Sort by File then Recno

                   

                  should give

                   

                  Under 250

                  1

                  2

                  ...

                  50

                  Over 250

                  1

                  2

                  ...

                  50

                   

                  That's if I've understood it correctly.

                   

                  Gareth

                   

                  Originally posted by Nick Osdale-Popa:

                  I don't think RecNo(), Line(), or even grabbing the Page # is going to help.

                   

                  There are two different models because the traps are completely different for each detial section of the model. Here is a schematic of how the report can look:

                  [font="courier"]Header Info

                  Detail250 Detail250 Detail250 Detail250

                  Detail250 Detail250 Detail250 Detail250

                  :

                  :

                  Header Info

                      Detail251    Detail251 

                  Header Info

                      Detail251    Detail251 

                  :[/font][/quote][b]Header info[/b] is the same for each "type"

                  Detail250[/b] is how a line of PO's under $250 would look

                  Detail251[/b] is how a PO of over $250 looks.

                   

                  Nothing is common between the two details to create a faux trap and glean appropriate info.

                   

                   

                  Now given the above info the report could have 50

                  Under250's spanning 3 pages, then have 50 Over250's spanning 2 pages.

                   

                  Line() reports the last line of the trap line, so if a Trap takes up to 12 lines one time and then only 10 lines the next, you'll get 12 and 10 respectively.

                   

                  Recno() reports the records it returns, thus I'll get records 1-50 for Under250's and records 1-50 for Over250's.  When I merge these two reports (in Excel) and sort by Recno() Record 1 of Over250 will come after Record 1 of Under250, when it should be after Record 50 of Under250. It would be ideal if the report was layed out like this (Under250's then Over250's), obviously you just don't sort, but the report is going to be interspersed with groups of Over250's and Under250's.

                   

                  Does this make it clear of the delimma I'm facing?    :confused:  /b[/quote]

                  • Trying to keep records in the same order as report
                    Nick Osdale-Popa

                    I just gave you a 5 Star rating, Grant, for the help you provided to me through emails.

                     

                     

                    Thanks again!

                    • Trying to keep records in the same order as report
                      Mike Urbonas

                      Nick, sounds like Grant has been able to help you offline...  smile.gif[/img]

                       

                      But FWIW, here is a suggestion that may help for similar situations.

                       

                      You could create a Monarch "model" which consists of nothing but a blank detail trap , with one huge field consisting of the entire line.  The table view will contain every line of the report.

                       

                      Add a calc. field to the left of this huge field, RowNo() or similar, and export the entire table as text.  Now use this "report", which you can also trap the row number!