2 Replies Latest reply: May 15, 2014 10:10 AM by Grant Perkins RSS

    Variable length sample text - secon try to show sample

    MacA _

      I am working with a report where the detail lines per group varies and if I try to make the sample group lines to fit that largest sample lines then it picks up some from the next group.  Here is an example (note there is *** between each grouping):


      123  James Jones


      Date Paid: 06/15/2011   Gross:  1,500.00   Regular Pay    1,200.48

                                                Overtime Pay       45.22

      Check DT: 06/16/2011                         Personal        25.44


      Date Paid: 06/29/2011   Gross:  1,600.00   Regular Pay    1,401.56

                                                Overtime Pay      145.22

      Check DT: 06/30/2011                        Personal         45.44

                                                 Sick Pay         134.23

                                                 Shift Diff        45.23  


      If I only select the first 3 lines from the first detail for my sample I do not get the last two lines of my second group.  But if I select 5 lines for my sample I pick up regular Pay twice for the first group.  I have tried doing just one which works BUT I need to distinguish each group by the date paid and one some of my records I get check date in the field when I do that as another header or append records.


      Any suggestions?

        • Variable length sample text - secon try to show sample
          Olly Bond

          Hello Mac


          There are two methods - one doing the work in the report window, the other in the table window. Which is best depends on the regularity of your data. Both use the Advanced tab of the Field Properties dialog.


          The first method uses the Preceding String function to tell Monarch to scan down a multiple-line text block and retrieve a field after the occurrence of a string like "sick pay".


          The second method is to just set the End Field On to "none", and to grab the last 1-3 lines as one big text block, say called "Blob". Then in the table you could retrieve Sick Pay using text functions like lsplit() and intrim().


          Hope this helps,



            • Variable length sample text - secon try to show sample
              Grant Perkins

              Hello MacA,


              Olly is right. However as a pre-cursor to his advice you will need to work with the consistent first 3 lines as your data sample in the template. You have to work with the smalles number of consistently available lines the report allows. However, unless you are using a floating trap when different rules apply, the actual data in the sample lines is not important. Therefore if your record blocks always have a blank line before the next record (or a row of asterisks or whatever) you can include the extra line in the template if it helps - which it might. If that is the case with your report let us know and I will write something up for you that extends on what follows here.


              Sticking with just 3 lines available ...


              Trap the 3 line record as I assume you do already and paint the field.


              Next, define an Append template using exactly the same trap as you used for the detail template but paint fields in the position that is required to capture what you need from lines 4 and onwards. For these fields use the 'Preceding String' property as Olly mentioned, one for each possible field that may be found - "Sick Pay", "Shift Diff", etc.


              If all the fields are in the same column you will get 3 per template. Need more? Then add another template. The order does not matter BUT each preceding string will need to be unique in some way since Monarch will only find the first instance in any 'set'. You can define more than one field per line providing there is no overlap or other conflict. Any fields that do not appear in a record will be left empty in the resulting extraction. You can name the field for its preceding string.


              That concept will usually be enough to get you what you describe. If it isn't then the chances are you will be needing the second of Olly's suggestions and creating a multi-row field starting on the third (or 4th if you get a 'free row') of your detail record and then save the entire block of text as a single field to be further manipulated using calculated fields in the table after extraction.


              For further reading you could search for forum entries with the phrase 'guru trap' and some examples of both approaches are likely to turn up!


              Or just see how you go and post beck with more questions if you get stuck on something when the data throws a curve ball.