4 Replies Latest reply: May 15, 2014 9:58 AM by Stephen Reid RSS

    Multiple Line Report

    Andy Rowland

      Can anyone help with the following multiline report.  Invoice details always cover 2 lines and Cash details are always 1 line.  I initally set up a single line detail template and trapped the '-' in date but i cant figure out how to get the second detail line of the invoices.  I have tried a multiline selection, an append and even looked at Advanced -> End Field On Line#2 cant quite get it.

       

      What am i doing wrong or am i making it more difficult than it needs to be??!?!

       

      [font="courier"]Type  Date   Per'd Batch Ref1     Ref2      Status     Date Due   Goods    Vat   Value

      Inv.30-Apr03 09.03 14464 258007   458537    300503A    31-May03   100.00   17.50 117.50

                                            B1466                    

      Cash28-Feb03 07.03 14223 280203A  058183                          100.00-       

      Inv.30-Apr03 09.03 14464 258009   458539    On Hold    31-May03    10.00    1.70  11.70

                                            L1462                    

      Inv.30-Apr03 09.03 14464 258010   458540    On Hold    31-May03    etc      etc    etc

                                            L1462                    

      Cash30-Apr03 09.03 14423 300403A  059004                       

      Inv.20-May03 10.03 14545 259182   514065    On Hold    30-Jun03

                               WATER TANKS  P1435                    

      Inv.20-May03 10.03 14545 259183   514066    On Hold    30-Jun03

                               ROTOVATORSx2 L1462                    

      Cash30-May03 10.03 14524 300503A  059505                       

      Inv.31-May03 10.03 14567 259546   545351    On Hold    30-Jun03

                               BRICKS[/font][/quote]Any suggestions?

        • Multiple Line Report
          Mike Urbonas

          Hi Andy,

           

          You are right, this should not be hard, and the good news the solution is not hard...just requires just a little finesse with Monarch.

           

          I would suggest you use a single-line Detail template to get all invoice and cash data without regard for the second invoice data line.  Among the fields you define, include a field that contains "Inv." or "Cash" (call it TYPE, for example).

           

          Then use a Footer trap to trap the second line of invoice data.  You might choose to call this data field SECOND INVOICE LINE.

           

          Now when you look in your Monarch Table view, Monarch will not only associate the second line of the invoice data with the corresponding first line of invoice data, but also any immediately preceding cash-related data lines.  In this case, we don't want that, so I suggest blanking such data out of your cash-related rows of data using a calculated field similar to this:

           

          IF(TYPE="Cash","",[SECOND INVOICE LINE])

           

          which tells Monarch to display a blank field if the data type is Cash.  If it is not, then the data type is Invoice, so it is OK to display the second invoice line data.

           

          I would complete the model job by hiding the original SECOND INVOICE LINE field, as the new calc. field correctly displays the second invoice line data for only the Invoice lines the way you want.  smile.gif[/img]

           

          Regards,

          Mike Urbonas

          Datawatch

           

          [size="1"][ June 23, 2003, 01:23 PM: Message edited by: Mike Urbonas ][/size]

          • Multiple Line Report
            Andy Rowland

            Works a treat!!

             

            Just goes to show that if it doesnt quite work using a simple model then Calculated fields can be very powerful!

            • Multiple Line Report
              Grant Perkins

              Andy,

               

              Mike's solution is clearly the best for your needs but playing around with the problem it occurred to me that there might be a requirement from time to time for something similar but with the need to have a different solution if, for example, the Footer feature was required to grab something else.

               

              Using your sample report layout I came up with 2 other approaches that might be considered under those circumstances and offer them as reference.

               

              Firstly, with the sample layout you provided, it is possible to use an 'append' to get the data fields though the associations are somewhat different. However since the fields related to the Cash lines are to be discarded that does not matter anyway. An 'append' which traps on "Inv." at the beginning of a line would also work in conjunction with the calculated field solution method of cleaning up the Cash records.

               

              Secondly, and a little bit more complicated but possibly the only option in some similar circumstances, would be to use the MSRP utility (or a text editor perhaps depending upon file sizes, automation needs and so on) to change the 2 line INV records to single lines and write a new version of the file.

               

              For your sample records that should simply mean replacing any line break 'character' which is followed by 'a number' of spaces to something else which does not include the line break instruction. For example Line Break followed by 10 spaces might simply become 1, 10, or 11 spaces depending upon the result each option might have on the line format, length and so on.

               

              Obviously these are not ideas you need on this occasion but they might come in handy at some other time.

               

              Best regards,

               

              Grant

               

               

              Originally posted by Andy Rowland:

              Works a treat!!

               

              Just goes to show that if it doesnt quite work using a simple model then Calculated fields can be very powerful! [/b][/quote]

              • Multiple Line Report
                Stephen Reid

                Hi

                 

                [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000243"]Click here[/url] for another post that may be of use to you if this is an area that is causing you problems.

                 

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