3 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    variable multiple lines

      Hi,

       

      Monarch Pro v7.0

       

      Here is a simple sample of the report:

       

      [font="courier"]TLR   DEBIT   CREDIT   NUMBER   ACCT

      6003  41.50    41.50    12345      1

                           ED 03-01-05 ATM 0301

                                  th Ave

           

      6005  50.00    50.00    54321      2

                           ED 03-01-05 ATM 0301

                                  08TH STREET

       

      6010  10.00             23456      1

      6010           10.00    23456      2

                           ED 03-02-05 Internet

                                  ST/EDT REF 1234

       

      6005  45.00    45.00    45678      2

                           ED 03-02-05 ATM 0301

                                  nDeer Rd

       

      6010  90.50             23456      1

      6010           10.00    23456      2

      6010           70.00    98765    143

      6010           10.50    23456      4

                           ED 03-03-05 Internet

                                  ST/EDT REF 1235[/font][/quote]What I am trying to do is just trap each record for TLR = 6010 from the 1st 6010 through the line that says "ST/EDT". A blank line precedes and follows each "block" or record.

       

      The number of lines is at least 2 and as many as how many transactions are done for each 6010 record. It can have 1 or more Debits and 1 or more Credits, but the Total Debits = Total Credits for each record.

       

      I want a calculated field for each record of Total Debits and Total Credits and list the NUMBER and DATE and TLR. Sometimes there may be more than 1 NUMBER for each record.

       

      Any help is greatly appreciated,

      Jeff

        • variable multiple lines
          Grant Perkins

          Jeff,

           

          I could interpret the 'list' requirement in a couple of ways  ! Is this a vertical list or a horizontal list you require?

           

          I can get to this fairly easily as a summary;

           

          [font="courier"] TLR Footer Dat Numbe         Credit          Debit

          6010 03/02/2005 23456          20.00          20.00

               03/03/2005 23456          41.00         181.00

                          98765         140.00              

          Summ                          201.00         201.00[/font][/quote]I guess you can too. The combination of multiple dates as well as multiple Numbers does make things a little tricky. Not sure what rules you might have for the dates requirements. (Note the values are twice those that posted simply because I have the example twice in my input file testing something else.)

           

          I think I would do something like this then export this summary to a new 'report file' and run a second phase process - now I have a single occurance of the TLR - to pick the 'Footer Date' column as a multi-row field and the Number column also as multi-row. That way I could list the results in a single horizontal field.

           

          However quite how useful that might be, given the potential for large numbers of entries in the strings, I cannot tell.

           

          Does this help?

           

          Grant

           

           

          Originally posted by JSR:

          I want a calculated field for each record of Total Debits and Total Credits and list the NUMBER and DATE and TLR. Sometimes there may be more than 1 NUMBER for each record.

           

          Any help is greatly appreciated,

          Jeff /b[/quote]

          • variable multiple lines

            Thanks for the post Grant!

             

            Ideally, I would like a horizontal list (i.e. TLR, NUMBER, DATE, DEBIT1, DEBIT2,..., CREDIT1, CREDIT2,..., TOTDEB, TOTCRED) - not sure if this is viable.

             

            What do I need to do to get the result you have in your post without the amounts doubling? I can't even get that far.

             

            HELP,

             

            Jeff

            • variable multiple lines
              Grant Perkins

              Hi Jeff,

               

              Pretty much anything is feasible but I would have concerns about not knowing how many transaction values I might get on a line!

               

              Also the potential for multipe dates for a Number - or is that an anomaly of the sample data?

               

              The model that gets the result posted is quite simple.

               

              Trap the detail lines using "6010" and creat the fields required.

               

              Trap the date (and anything else from the 2 lines at the bottom) on a FOOTER template. I uesd the "ED" charcaters but is they are not consistent there should be someting else available - the dashes in the date format for example.

               

              That gets you a table. Then you create a summary setting TLR, NUMBER and DATE as Key fields and the Debit and Credit fields as Measures.

               

              I then tset the properties of the TLR and DATE fields to "Suppress Duplicates" so that values only display once.

               

              Now you could go further and 'Export' the result so far to a text file and then read that back in as a report. Pick the 6010 template identifier again (for example)and then each of the field on a row but make each field a MULTI-ROW field so that, for each, you will get a list of the values in the "X" lines of the report - 3 lines in this case. SO TLR would have a single value, DATE would have 2 values, NUMBER would have 3 values and the DEBIT and CREDIT fields would have 3 values.

               

              Select the Summary CREDIT and DEBIT values in a FOOTER template and you should get the row you want - but I do have some reservations about the readability and usefulness of the result! However, if that is what is required, that is what can be delivered.  smile.gif[/img]

               

              The double values only exist because I duplicated the data in the report file I created (Cut from the forum screen and pasted into Wordpad) so the data set existed twice in my input file.

               

              If you wish I can email the sample text and model file to you - just send me a Private Message with an email address that will find you (and accepts attachments) and I will send them on.

               

              I hope this helps.

               

              Grant

               

              Originally posted by JSR:

              Thanks for the post Grant!

               

              Ideally, I would like a horizontal list (i.e. TLR, NUMBER, DATE, DEBIT1, DEBIT2,..., CREDIT1, CREDIT2,..., TOTDEB, TOTCRED) - not sure if this is viable.

               

              What do I need to do to get the result you have in your post without the amounts doubling? I can't even get that far.

               

              HELP,

               

              Jeff /b[/quote]