9 Replies Latest reply: May 15, 2014 9:55 AM by Pat Corbett RSS

    Simple Multi line (?) trap question

    Pat Corbett

      I have been using Monarch Pro, v 8.01, for all of four or five days. Very nice. I am quite impressed.

       

      I am currently dealing with a fairly straight-forward report format. I am pulling most of the data I want from the report with a simple detail trap.

       

      There is one field (item no.) that is causing me a problem, as it will occasionally have a line wrap (in the report), and as a result there may be from 1 to 3 characters that end up on the next row. I initially handled this by using an append trap, but ended up with repeating data in the table. (And I don't want to use a summary, either.)

       

      I have looked at various ways to bring in the characters that wrap, without using an append. I looked at the multi-line approach, but can't figure out a good solution.

       

      As shown in the report file, the first two item numbers below are no problem. The third item number has 3 characters that are on the next line, and the 4th item number also has some characters on the line that follows it.

       

      Item no

      1542010.0000000

      1542020.0000000

      155D01998.00000

      0d0            

      155W19998.00000

      00            

       

      Would anyone have suggestions for how I could trap the wrapped characters without using an append trap?  I know the solution is staring me right in the face, but I cannot get to it.

       

      Thanks for any guidance.

        • Simple Multi line (?) trap question
          Grant Perkins

          Hello Pat and welcome to the forum.

           

          In theory a good solution to your problem would be to treat the the field that wraps as if it is a multi-line field. To do this you need to try 2 things.

           

          Firstly the way you trap works is important. It needs to recognise only the first lines and not the wrapped lines. So, using the sample data you format you posted, a trap that required only that the first character of the line had to be numeric would not work. But if the data lines always started with "15" and you trapped on that the selection ans wrap would work fine.

           

          Similarly you might see if a trap based on the first line having a "." (period) somewhere (floating trap required in this case) would select as you require. It should so long as there is always a "." in the first line of a record and never in the second.

           

          So if the right sort of trap definition is possible, define the field. Right click on it and go into the advance properties then set an appropriate "End field on" value. In this case it would probably be "None of the above".

           

          Then see whether it gves you the result you need across the entire report.

           

          HTH. I'm happy to offer some other ideas if this is not going to work providing you can tell us what it is that makes it fail.

           

          Have fun.

           

           

          Grant

          • Simple Multi line (?) trap question
            Pat Corbett

            Hello Grant, and thank you for your quick reply.

             

            The field in question (Item no.) is a text field, as is the wrapped line, and can begin with any character. The "." in the field can be in varying positions in the field.

             

            I have tried the multi-line approach, including using a "." string, to no avail. I have also tried the floating trap, without success.

             

            In both the multi-line and floating trap attempts, I cannot get the entire wrapped field to be captured in all instances.

             

            Any further thoughts?

            • Simple Multi line (?) trap question
              Grant Perkins

              Originally posted by Pat Corbett:

              Hello Grant, and thank you for your quick reply.

               

              The field in question (Item no.) is a text field, as is the wrapped line, and can begin with any character. The "." in the field can be in varying positions in the field.[/b][/quote]Does that mean the "." can appear on the second line instead of the first line?

               

               

                 [b]

              I have tried the multi-line approach, including using a "." string, to no avail. I have also tried the floating trap, without success.

               

              In both the multi-line and floating trap attempts, I cannot get the entire wrapped field to be captured in all instances.

               

              Any further thoughts? /b[/quote]The floating trap, based on the "." for exmaple, and assuming there is only one in the field should work providing the "." always occurs in what you need to see as the first line. If it occurs in the wrapped part of the line or does not occur at all the "." would not be a suitable trap identifier. However it also assumes in this case that you are selecting the entire row as a field (as per the sample) which I realise may not be the case.

               

              Either of those situations would also mean the multi-line field definition would not give the expected results. However, if you have a successful trap but a failed multi-line field extraction then there is something else influencing the results.

               

              Is there any possibility of being able to post a section of the report, including a record or two which fail, into the forum? If you can do that use the CODE button (Instant UBB codes below the message entry window) to retain the report format since it seems that could be important for this one.

               

              I would also be happy to take a look at a sample report off line if you are in a position to release it. If the information is confidential a representative sample of a test report would be fine providing the data it contains is pretty much the same as you are seeking to analyse.

               

              Contact me through my profile and provide an email contact address and I will get back to you as soon as I can with an email address to send the report to.

               

              If it is a problem report there are some other approaches that can be taken but they are not the sort of things that it would be reasonable to expect anyone to spot in the first week of using Monarch!

               

              HTH.

               

               

              Grant

              • Simple Multi line (?) trap question
                Pat Corbett

                Grant,

                 

                The "." only appears in the first line, although its position in the first line can vary.

                 

                As to selecting the entire row as a single field, I am actually selecting several fields from each row. The sample in my first post was only one of the fields.

                 

                I have included three selections from the report below.

                 

                The first selection below is an example from the report without any line wrapping in the first field (Item). This is easy to extract data from. I am selecting seveal fields from each record.

                 

                [font="courier"] Item            Category        Description              UOM     Quantity            Value     Quantity            Value     Quantity            Value     Quantity   Extended Value   

                -


                -


                -


                --- -


                -


                -


                -


                -


                -


                -


                -


                     

                1100006.0000000 INVENTORY - RAW Band,12 Ligne,Silver Bea YD.       265.00            97.31         0.00             0.00         0.00             0.00       265.00            97.31   

                1100007.0000000 INVENTORY - RAW Band,12 Ligne,Black,,50- YD.     3,222.00         1,186.41         0.00             0.00         0.00             0.00     3,222.00         1,186.41   

                1100022.0000000 INVENTORY - RAW Band,19 Ligne,Silver Bea YD.       275.00           151.47         0.00             0.00         0.00             0.00       275.00           151.47   

                1100023.0000000 INVENTORY - RAW Band,19 Ligne,Black,1 3/ YD.     2,605.84         1,435.29         0.00             0.00         0.00             0.00     2,605.84         1,435.29   

                1100026.0000000 INVENTORY - RAW Band,19 Ligne,Midnight,5 YD.       261.40           143.98         0.00             0.00         0.00             0.00       261.40           143.98   

                /font[/quote]The second example below is a sample that illustrates the line wrapping in the first field.

                 

                [font="courier"]Item            Category        Description              UOM     Quantity            Value     Quantity            Value     Quantity            Value     Quantity   Extended Value   

                -


                -


                -


                --- -


                -


                -


                -


                -


                -


                -


                -


                     

                153W84081.00000 INVENTORY - RAW Body,Straw,20*,Nat,3 Bu, EA.       659.00         5,874.04         0.00             0.00         0.00             0.00       659.00         5,874.04   

                00                                                                               

                153W86081.00000 INVENTORY - RAW Body,Straw,20*,Nat,3Bu,1 EA.     1,219.00        11,534.45         0.00             0.00         0.00             0.00     1,219.00        11,534.45   

                00                                                                               

                153W86081A.0000 INVENTORY - RM  Body,Straw,20*,Nat,3Bu,J EA.       897.00         8,487.61         0.00             0.00         0.00             0.00       897.00         8,487.61   

                000                                                                               

                /font[/quote]Note that the line wrapping can occur at any point in the report. The final example below shows 4 lines from the report, of which 2 are non-wrapped and 2 are wrapped.

                 

                [font="courier"]1542010.0000000 INVENTORY - RAW Body,Raw,Promo,Dark      EA.       299.00         3,340.33         0.00             0.00         0.00             0.00       299.00         3,340.33   

                1542020.0000000 INVENTORY - RAW Body,Raw,Promo,Pastel    EA.       132.00         1,399.42         0.00             0.00         0.00             0.00       132.00         1,399.42   

                155D01998.00000 INVENTORY - RAW Body,Straw,0,Seconds,Pan EA.       184.00           480.86         0.00             0.00         0.00             0.00       184.00           480.86   

                00                                                                               

                155W19998.00000 INVENTORY - RAW Body,Straw,0,Seconds,JG, EA.     1,336.00         4,315.28         0.00             0.00         0.00             0.00     1,336.00         4,315.28   

                00                                                                               

                /font[/quote]I appreciate your help in this matter, and look forward to your thoughts.

                • Simple Multi line (?) trap question
                  Grant Perkins

                  Hi Pat,

                   

                  OK here's what I would be looking at with this report.

                   

                  Set the trap to work with something that is consistentle available and unique to the 'first line' of a record. So o0ne of the things you can use here is that all primary lines will have values of some sort under the Category, UOM, Quantity, Value, etc. fields. INdeed all values fields will (it appears) have a decimal point in a consistetn position on the line (which may be why the floating trap did not work as expected.)

                   

                  Secondary (wrapped) lines do not have any values as those character columns. So if, fr example, you trapped on the "." under the first Value column. (simple selection - you may need more than that but it will do to show the principle for now) the line in which it is contained will be selected but any wrapped lines will not.

                   

                  If you them make the Item field a multi-line field Monarch should pick up the balance of the Item field that wraps to the second line. Remember to make the DISPLAY for the field size laerge enough to display the additional characters. So you rfield DATA width here might be 15 but I would make the display width somewhat more - say 30 to be sure that all the second line characters will display even if the wrap effectively doubles the size of the field.

                   

                   

                  HTH.

                   

                   

                  Grant

                  • Simple Multi line (?) trap question
                    Pat Corbett

                    Grant,

                     

                    I played around with the "End Field On" options for the Item field.

                     

                    First I selected "none of the above", which did trap the wrapped characters that I want, but also brought in other characters I didn't want.

                     

                    Then I changed the End Field On option to the Line Count approach, setting it to 2. This appears to have done the trick, although I am a bit confused. I thought the Line

                    Count option for End Field On would only work if all instances of the field had the same number of lines. In my case, the Item field will have either one or two lines.

                     

                    Thanks for your assistance.

                    • Simple Multi line (?) trap question
                      Pat Corbett

                      Grant,

                       

                      Two more questions:

                       

                      First, the wrapped text is coming in with a single blank space in front of the first wrapped character. I know I can strip this out after exporting the data to Excel, but is there a simple way of avoiding the space in Monarch? (In the report, there is no extra space in front of the wrapped text.)

                       

                      Second, since I appear to get the same results whether I use the End Field On Line count option and the None of the above option, is there any compelling reason to use one rather than the other?

                       

                      Again, thanks for you help.

                      • Simple Multi line (?) trap question
                        Grant Perkins

                        Originally posted by Pat Corbett:

                        Grant,

                         

                        Two more questions:

                         

                        First, the wrapped text is coming in with a single blank space in front of the first wrapped character. I know I can strip this out after exporting the data to Excel, but is there a simple way of avoiding the space in Monarch? (In the report, there is no extra space in front of the wrapped text.)[/b][/quote]The space is retained to provide some sort of indication of original line for multi line fields taken from text blocks.

                         

                        You can strip it in Monarch if you create a calculated field to apply a formula to convert it. The STRIP function should do it.

                         

                        [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001176"]This[/url] recent thread covers a few other ways of dealing with the concatentation of two parts of a string but is probably more use to users of earlier versions of Monarch.

                         

                        [b]

                         

                        Second, since I appear to get the same results whether I use the End Field On Line count option and the None of the above option, is there any compelling reason to use one rather than the other?

                        /b[/quote]Probably not in your case because you know that there will only ever be 2 lines. If there were a variable number of lines the 'none of the above. setting would run on until the next occurrence of a template. I'm guessing the extra characters you get appear after section changes or at the end of pages within the report. If it is at the end or beginning of pages defining Page footers and headers would mostly likely overcome that (though some report layouts are resistant to such simple remedies!)

                         

                        It's been a while since I looked at the requirement for wrapped lines for a field where the second (or subsequent) lines may or may not exist. I think as long as you have just a single SAMPLE line in the template you should be OK. The more complex scenario to deal with is when there is an entire field which may or may not be present on a second (or subsequent) line, at which point defining the template can become quite challenging.

                         

                        I will try to make some time to play around and refresh my memory later today ... the embedded help is a little un-specific about what happens if you use the fixed number of lines but the process encounters another template occurrence before hitting that number. Which leaves it as one of those questions where my brain suddenly decides it is not so certain of the answer!    

                         

                        Sounds like you have the problem beaten one way or another.

                         

                        HTH.

                         

                         

                        Grant

                        • Simple Multi line (?) trap question
                          Pat Corbett

                          Grant,

                           

                          You have been quite helpful. I did make a point of perusing through the forum topics yesterday, and read several instances of your solution involving headers when a multi-line field is involved, et al. These were useful as well.

                           

                          Thanks again for your help.