4 Replies Latest reply: May 15, 2014 9:52 AM by Grant Perkins RSS

    Report with Variable Lines, Some with Identiacal Field Names

    MorseB _

      Is there an easy way to extract data when the record contains multiple lines with same column headings?  I do not see any differences to uniquely identify.

       

      Some records have two ID Code and ID rows.  Some only have one.  The information I need is in the second row when there are two.  However, when there is only one, it is in the first record.  Also note that preceding row information is different - DATE CODE removed.

       

      REF DESC    TRACE NUMBER FROM THE ACH PAYMENT

      DATE CODE PROCESS            DATE 04/20/04      

      ID CODE ASSIGND BY SELLER/AG   ID 3051036447    

      ID CODE ASSIGND BY SELLER/AG   ID B1728P1B1728P1

       

      REF DESC    TRACE NUMBER FROM THE ACH PAYMENT

      ID CODE     CD ASSGND BY ORIG OR   ID 2694137

        • Report with Variable Lines, Some with Identiacal Field Names
          tcorley _

          1)Make a detail trap using the first Line of the record.

           

          R: REF

          S: REF DESC TRACE NUMBER FROM THE ACH PAYMENT

           

          Select any part of the line for a field. The entire line "REF DESC TRACE NUMBER FROM THE ACH PAYMENT" works fine.

           

          2) Create an Append trap using the Line "DATE CODE PROCESS DATE 04/20/04". Use the same trap value as you did for the detail line => "REF".

           

          R: REF

          S: DATE CODE PROCESS DATE 04/20/04

           

          Highlight the date to make it your field. After it is highlighted right click on it. This will bring up the General/Advanced Tab. Click on the Advanced Tab. Select preceding string. Type "date code process date" as the preceding string.

           

          3) Create an Append Trap using the Line "ID CODE ASSIGND BY SELLER/AG ID 3051036447". Use the same trap value as you did for the detail line => "REF".

           

          Highlight the ID "3051036447". After it is highlighted right click on it. This will bring up the General/Advanced Tab. Click on the Advanced Tab. Select preceding string. Select After Last defined field in the template and Non-blank preceding string of 0 characters.

           

          4) Switch to the table view.

          Rsplit(<<name_of_id_field>>,2,CHR(10),1) should give you the id you want.

          • Report with Variable Lines, Some with Identiacal Field Names
            MorseB _

            I thought appends went down.  I am using version 6.01.

            • Report with Variable Lines, Some with Identiacal Field Names
              cardealers _

              My 2 cents:

               

              Detail - 1 allowed - Required - Lowest sort level

              Appends - 9 allowed - Data above, left or right of detail

              Footer - 1 allowed - Data below or right of detail

              Page Header - 1 allowed - Page break or 10th Append.

               

              CarDealers

              • Report with Variable Lines, Some with Identiacal Field Names
                Grant Perkins

                Beth,

                 

                Does the text in the report look just like you posted? Also are the ID data fields required offset as they seem to be in the post?

                 

                If so my suggestion is similar to tc's.

                 

                Create a detail template of 2 lines trapping on REF. In the first line paint one of the ID fields (to an appropriate size) in the required position and in the advanced tab use a preceding string that will pick only that field. Let's say you are painting the single line only field. A preceding string might be ORIG for example.

                 

                For the 2 line example a suitable preceding string might be SELLER. For this field also set the 'End Field On' value to 'blank field values'.

                You can split the second value into a separate field with the rplist function as tc suggests. It may be useful to rtrim the field first just in case there are odd extra spaces around. You should also get away with using " " as an alternative to CHR(10) if you prefer.

                 

                If you need the date for the lines to which it applies create and append using REF again, paint a field in the position for the date and give it a preceding string of, say, DATE. That should give you  a populated date field for only the detail records with 2 lines.

                 

                I hope this helps.

                 

                 

                Grant

                 

                Originally posted by MorseB:

                Is there an easy way to extract data when the record contains multiple lines with same column headings?  I do not see any differences to uniquely identify.

                 

                Some records have two ID Code and ID rows.  Some only have one.  The information I need is in the second row when there are two.  However, when there is only one, it is in the first record.  Also note that preceding row information is different - DATE CODE removed.

                 

                REF DESC    TRACE NUMBER FROM THE ACH PAYMENT

                DATE CODE PROCESS            DATE 04/20/04      

                ID CODE ASSIGND BY SELLER/AG   ID 3051036447    

                ID CODE ASSIGND BY SELLER/AG   ID B1728P1B1728P1

                 

                REF DESC    TRACE NUMBER FROM THE ACH PAYMENT

                ID CODE     CD ASSGND BY ORIG OR   ID 2694137 /b[/quote]