2 Replies Latest reply: May 15, 2014 10:03 AM by Kristie _ RSS

    Multiple Line records

    Kristie _

      Hi, I have tried every combination of detail, append, footers with this data but am not having any luck.  Each record can have 1 line or more, the detail can be in the next line or after a blank line.  When I try the detail, append I am getting the appended data on each record whether it belongs or not.  Any ideas?  here is a sample:

      000000000111 0000012345 DOE, JANE A       200,000.33 O172    1,500.00                                                                               

      BACKDATE     

      000000002222 0000012345 JANE DOE, INC      10,000.03   76      500.36      500.00                                                                               

      BACKDATE 

      000000033333 0000012345 JOHN DOE, INC           500.41 D172    8,00.18                                                                               

      BACKDATE      

          • REVERSAL - BALANCE INCREASED                                   

       

      000000044444 0000001234 DOE, JOHN         600,000.05 M 66   70,000.87  

         

         

          • ADVANCE - BALANCE INCREASED                      

      000000055555 0000001234 DOE, JAMES        100,000.00 M 66   10,000.00  

          

          • ADVANCE - BALANCE INCREASED                            

      000000066666 0000001234 DOE INC            30,000.08 O172  400,000.00                                                                               

      BACKDATE  

      000000077777 0000005678 DOEY INC          100,000.08 M 66  100,000.00 

          

          • ADVANCE - BALANCE INCREASED

                                                                                      000000888888 0000012345 DOE, KIM           30,000.97 M176      500.36      400.11     

      000000999999 0000012345 DOES, INC         700,000.42 M176    5,000.51    1,000.43   

      000022222221 0000012345 DOE, JOHNNY        30,000.00 O176      400.24                                                                               

      the columns are lined up but the rows really look like this.  The record starts at the number 00000000nnnnn and ends when the next number starts.  thanks in advance for any help.

        • Multiple Line records
          Data Kruncher

          Hi Kristie,

           

          That one is a little nasty, but it can be done. Here's my approach.

           

          You may have seen references here about a solution we call the "guru trap". We'll not only use it, but we'll use it three times.

           

          First up though is the detail line of course. I trapped it rather lazily with a single N in position 1.

           

          Now for the backdate. If the report required a single "guru trap", I'd have gone with the the "after last defined field" Advanced option, but that's not the case, so we're left with the others. Line 1 won't work, and there's nothing unique enough in the detail line to be consistent from record to record to use the "string anywhere in previous line" option.

           

          That leaves us with the "Preceding string in current line". But "backdate" is right up against the left margin, so there's nothing preceding the data. Out of options, we have to find a way.

           

          Instead of treating all of "backdate" as the data, we'll skip "back" and use "back" as the preceding string text. Now we paint "date" as the field and name the field "Pre_Backdate". Save the append template.

           

          Now add another append, again using a single N in position one. Define the reversal lines using "*** rev" as the preceding text option. You can't use "***" because the advance lines also use asterisks. Paint the field from "ersal" over. Name it Pre_Reversal. Save the template, and on to the next.

           

          Lastly, repeat the same for the advance lines. Name the field Pre_Advance.

           

          Over in the table window, create three calculated fields to get your full data back, as we've necessarily cropped it.

           

          Backdate becomes:

          [SIZE=2]if(="","","BACK"+)[/SIZE][/CODE]

           

          Reversal becomes:

          if(="","","REV"+)

          /CODE

           

          Advance becomes:

          if(="","","ADV"+)

          /CODE

           

          It's a bit of work, but I think that it does what you need.

           

          Kruncher