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

    Multiple line report help

    richardCFI _

      I am new to Monarch (version 7) and am having trouble extracting the extra detail lines from the following report. There may be up to 9 extra detail lines. But there is no blank line before the next Invoice line so I am having difficulty getting the "End Field On" to end at the right time. Also, I cannot decide how to trap the extra lines because they start at different positions and have no unique identifying information.

       

      [font="courier"]  9  18-12-01  INV   56860  01          500.15         1.40       501.55

        9  01-02-02  INV   57331  01          417.61         1.39       419.00

                           MR. MARK FOYLE                                                                               

      SPECIAL COMPANY

                             27151, GOVT DUTY, MEMO & ARTS, REGISTER                                                                               

      SPECIAL COMPANY FORMATION - FEES                                                                               

      FOYLE RUN

        9  04-02-02  INV   57360  01          201.76        35.55       237.31

      /font[/quote]Any help or suggestions would be greatly appreciated.

        • Multiple line report help
          Grant Perkins

          Hi Richard and welcome!

           

          I think your report is going to give you some headaches trying to extract what you want in one pass. If you had a blank line between records (I assume that each line starting with a 9 and then a date is an individual record?) I think I could offer a couple of interesting ideas to try but as it is I think you are stuck with a single line detail template.

           

          So I'm going to suggest a 2 stage process and hope that you have V7 Pro. so that you can use some external lookup features.

           

          Think of the report as 2 separate reports. One has the obvious lines of detail, the other has the extra text lines. We need to separate them.

           

          That will allow us treat the process as 2 unique operations.

           

          Firstly extract the extra text into a lookup table (maybe export it to Excel?).

           

          Create a template based on a 2 line sample with a regular line as line one and the first line of some extra text as line 2. Trap on the first line as you will need to do for the second stage of the process. Pick some data from line one that will give you a unique key with which to connect the 2 extracts later. (I hope you can derive a unique key.) 'Paint' the field for line 2 to ensure that it is wide enough to capture any text which may appear.

           

          In the Advance Properties set the "Start Field On" value to a number of SPACES - keying in 10 spaces for example works on the sample posted.

           

          Set the "End Field On" to "Non Blank Preceding String". 0 characters seems to work on the sample.

           

          You should see all the text lines highlighted.

           

          Have a look at the table. You should see only those records where the extra text lines exist PLUS on entry with blank text field at the end, maybe. As far as I can guess that will not be an issue anyway.

           

          Export the result (Key and text field) .

           

          Open the report again in a new session. Create a model for the extraction of the data line required. Make sure that one of the fields (extracted or calculated) is populated with the keys required by the external link.

           

          Make a link to the external lookup file using the key and import the text to the records it needs to be attached to.

           

          That should be it.

           

          To make this a single process you could create each stage as a PROJECT with a PROJECT EXPORT and then create a small batch file which will run the First extract (text) and create the Excel file entry, close the Excel file and then run the second project which will give you the entire output you require. That would make running the the process a single click operation or allow it to be scheduled if you are using systems software that supports scheduling.

           

          I hope that helps in some way. If anything is not clear (very likely) or does not work as I describeed (also quite likely!) let me know and I will see if I can resolve the problem for you.

           

          There are other approaches but the ones that come to mind involve either pre-preparation of the file to change its format to be more helpful (using Monarch utilities) or the use of Monarch to manipulate the file and export it in a new format, then running a single stage analysis.

           

          In either case you are still into 2 stages in the process so it seems logical to look at this route first.

           

          Have fun.

           

          Grant

           

          Originally posted by richardCFI:

          I am new to Monarch (version 7) and am having trouble extracting the extra detail lines from the following report. There may be up to 9 extra detail lines. But there is no blank line before the next Invoice line so I am having difficulty getting the "End Field On" to end at the right time. Also, I cannot decide how to trap the extra lines because they start at different positions and have no unique identifying information.

           

          [font="courier"]  9  18-12-01  INV   56860  01          500.15         1.40       501.55

            9  01-02-02  INV   57331  01          417.61         1.39       419.00

                               MR. MARK FOYLE                                                                               

          SPECIAL COMPANY

                                 27151, GOVT DUTY, MEMO & ARTS, REGISTER                                                                               

          SPECIAL COMPANY FORMATION - FEES                                                                               

          FOYLE RUN

            9  04-02-02  INV   57360  01          201.76        35.55       237.31

          /font[/quote]Any help or suggestions would be greatly appreciated. /b[/quote]

          • Multiple line report help
            richardCFI _

            Thanks Grant, you're a star. I have now implemented it as a 2 step process. The important part which I could not figure out myself was setting the "Start Field On" to a number of spaces for the extra detail lines.

            • Multiple line report help
              Grant Perkins

              Originally posted by richardCFI:

              Thanks Grant, you're a star. I have now implemented it as a 2 step process. The important part which I could not figure out myself was setting the "Start Field On" to a number of spaces for the extra detail lines. /b[/quote]Hi Richard,

               

              Glad you found the solution!

               

              The use of blanks as a preceding string is rather obscure and sometimes you have to be careful to get the right number of blanks to be selective but it is rather nice to have it available.

               

              Last time I tried it it did NOT work for the other preceding string option "Anywhere in previous line" which was new in V7. In fact preceding spaces seem to be stripped from that particular option. No big deal as I suspect it could quite a fragile way to select anyway and as far as I can recall I have only come across one situation where it seemed like a possible method of working round a problem report.

               

              Have fun with Monarch.

               

              Grant