7 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Extracting Multiple Rows

    diajus _

      I'm in dire need of an expert opinion.  What do I need to do to accomplish the following?

       

      ROW 1:FULL NAME

      ROW 2:PHONE NUMBER

      ROW 3:FULL NAME

      ROW 4:FULL NAME

      ROW 5:FULL NAME

      ROW 6:PHONE NUMBER

       

      How can I extract information from this type of report if some rows have multiple rows of information pertaining to that record while others may not.  In this generic example I would like to return one row of information appending phone numbers to the record for those that have one and leaving the filed blank for those that do not. 

       

      In my real case project I have a report that is producing a second line of information for some rows and not for others.  I need that second row of information and append it to the correct row of data. 

       

      I'm running version 7.

        • Extracting Multiple Rows
          Grant Perkins

          Hi,

           

          There are a number of ways this might be addressed depending upon the report format and content.

           

          If the tlelphine number, when it appears, has a field description tag of some sort - for example "Telephone:" 123-234-1234 - you maybe could set up an APPEND template using the exact same trap as your detial (Full Name) lines, paint a field where the horizontal position of the tel. number will appear when it appears and then set the Advanced Properties for the field to start the field on a 'Preceding String' of "telephone:" (without the quote marks of course!)

           

          If that is not possible you may need to consider whether you could make the Full Name field a multi-line field. That would result in the telephone number being extracted when it appears allowing you to use some calculated fields to then separate the Name from the Number.

           

          The formulae for the calculated fields are somewhet dependent on the formats of the name and number components so a little difficult to provide a definitive way forward at the moment.

           

          If the format of the report is very generic even trapping the rows accurately might be a problem but I'm hoping that wis the the situation you find.

           

          HTH.

           

           

          Grant

          • Extracting Multiple Rows
            Nick Osdale-Popa

            Originally posted by Grant Perkins:

            If the tlelphine number...[/b][/quote]Drunk posting will not be tolerated!

             

               

            • Extracting Multiple Rows
              Grant Perkins

              Originally posted by Nick Osdale-Popa:

                /size[quote]quote:[/size]Originally posted by Grant Perkins:

              If the tlelphine number...[/b][/quote]Drunk posting will not be tolerated!

               

                   /b[/size][/QUOTE]:D 

               

              I will have a strong word with the keyboard which more and more frequently seems to fail to respond correctly to instructions!

               

              Either that or it's a bug in the internet.

               

              Absolutely nothing to do with my typing 'skills' or proof-reading of course ...

               

               

              Grant

              • Extracting Multiple Rows
                diajus _

                Here is the best example I can provide. THANK YOU for taking the time to respond.  The telephone number in my preceding example does not have a description tag to it.  In this real life example the telephone number would be represented in the second row 'TRANSFER SUCCESSFUL'. 

                 

                This report has multiple rows if a description of the transaction exists.  Other wise it has a single row with the next record to follow.  

                 

                When I preview this post it left justified the description.  The example I want to provide lists the description right below the 8000 field beginning position right below the 8.

                 

                 

                0111-0021 1 30.50 8000 6053

                                  TRANSFER SUCCESSFUL

                0111-0021 1 10.00 8000 1245

                0111-0021 1 34.00 8000 1246

                0111-0021 1 50.00 8000 1247

                0111-0021 1 11.00 8000 1248

                                  CLOSE ACCOUNT

                0111-0021 1 55.44 8000 1249

                0111-0021 1 12.44 8000 1250

                                  TRANSFER

                • Extracting Multiple Rows
                  diajus _

                  Originally posted by Grant Perkins:

                  Hi,

                   

                  There are a number of ways this might be addressed depending upon the report format and content.

                   

                  If the tlelphine number, when it appears, has a field description tag of some sort - for example "Telephone:" 123-234-1234 - you maybe could set up an APPEND template using the exact same trap as your detial (Full Name) lines, paint a field where the horizontal position of the tel. number will appear when it appears and then set the Advanced Properties for the field to start the field on a 'Preceding String' of "telephone:" (without the quote marks of course!)

                   

                  If that is not possible you may need to consider whether you could make the Full Name field a multi-line field. That would result in the telephone number being extracted when it appears allowing you to use some calculated fields to then separate the Name from the Number.

                   

                  The formulae for the calculated fields are somewhet dependent on the formats of the name and number components so a little difficult to provide a definitive way forward at the moment.

                   

                  If the format of the report is very generic even trapping the rows accurately might be a problem but I'm hoping that wis the the situation you find.

                   

                  HTH.

                   

                   

                  Grant /b[/quote]The end result should be one row of data with a null field if there is not a description in the row immediately below. 

                   

                  0111-0021 1 30.50 8000 6053 TRANSFER SUCCESSFUL

                  0111-0021 1 10.00 8000 1245

                  0111-0021 1 34.00 8000 1246

                  0111-0021 1 50.00 8000 1247

                  0111-0021 1 11.00 8000 1248 CLOSE ACCOUNT

                  0111-0021 1 55.44 8000 1249

                  0111-0021 1 12.44 8000 1250 TRANSFER

                  • Extracting Multiple Rows
                    Grant Perkins

                    Post to display actual format.

                     

                    Originally posted by diajus:

                    [font="courier"]0111-0021 1 30.50 8000 6053

                                      TRANSFER SUCCESSFUL

                    0111-0021 1 10.00 8000 1245

                    0111-0021 1 34.00 8000 1246

                    0111-0021 1 50.00 8000 1247

                    0111-0021 1 11.00 8000 1248

                                      CLOSE ACCOUNT

                    0111-0021 1 55.44 8000 1249

                    0111-0021 1 12.44 8000 1250

                                      TRANSFER /font[/quote][/b][/quote]

                    • Extracting Multiple Rows
                      Grant Perkins

                      Originally posted by Grant Perkins:

                      Post to display actual format.

                       

                        /size[quote]quote:[/size]Originally posted by diajus:

                      [font="courier"]0111-0021 1 30.50 8000 6053

                                        TRANSFER SUCCESSFUL

                      0111-0021 1 10.00 8000 1245

                      0111-0021 1 34.00 8000 1246

                      0111-0021 1 50.00 8000 1247

                      0111-0021 1 11.00 8000 1248

                                        CLOSE ACCOUNT

                      0111-0021 1 55.44 8000 1249

                      0111-0021 1 12.44 8000 1250

                                        TRANSFER /font[/quote][/b][/quote][/b][/size][/QUOTE]This works (for the sample) in V8 and as far as I know should work in V7 as well.

                       

                      I defined a detail trap for the detail lines based on the "-" at position 5.

                       

                      I then created an append template with the exact same trap, 1 sample line - the contents don't much matter. Paint a field where your occasional data appears horizontally on the line. Go to the advanced field properties and set the 'Start field on' value to 'After last defined field in template.'

                       

                      Ok that and go back to see what it gives you. The default for append fields is that they appear before the detail fields in the table but you can change the order and save that in the model.

                       

                      If you still have a problem an alternative might be to try the advanced property as 'Start on ... Preceding string' and use a string of spaces. This also works with V8 and should be OK with V7 as I recall.

                       

                      HTH.

                       

                       

                      Grant