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

    Trapping adhoc extra line

    hakru _

      Hi,

       

      I’m new to Monarch and is wondering if there is a solution to trap an occasional extra line. The extra line contains a comment refering to the prior detail line. I'm able to trap everything as needed but for the lines with no comments the previous comment is repeated until a new comment occurs.

       

      1|Detail

      2|Detail

      3|Detail

      3|Comment:”Some comment”

      4|Detail

      5|Detail

      6|Detail

      6|Comment:”Some comment”

      7|Detail

      8|Detail

       

      I need a table:

       

      1|Detail, Blank

      2|Detail, Blank

      3|Detail, ”Some comment”

      4|Detail, Blank

      5|Detail, Blank

      6|Detail”Some comment”

      7|Detail, Blank

      8|Detail, Blank

       

      Hope it make sense.

       

       

      Thanks,

      Hannes

        • Trapping adhoc extra line
          Grant Perkins

          Hi Hannes and welcome to the forum.

           

          You have the usual 'Newcomer's" syndrome of needing something slightly above newuser level knowledge as soon as you start! Most people here seem to have the same experience. You are amongst friends.

           

          Can I assume the the tag "Comments:" appears if there is a comment line?

           

          If so, trap your details as I assume you already do and presumably that trap can be set to miss the Comments lines.

           

          The, using the SAME TRAP as for the details template create an APPEND template. Paint the field for the comments as required. Right click on the field to get to the Advanced Properties. Set the 'Start Filed on' option to 'Preceding String' and make that string  "comments:" (without the quote marks.

           

          You will then have an append template that auto resets with every detail template and only reports anything if the Comments field exists.

           

          If the word Comments does not appear we may need another approach.

           

          HTH.

           

           

          Grant

            • Trapping adhoc extra line
              hakru _

              Grant,

               

              Thanks for the welcome and quick response!

               

              I am guilty as charged on the "Newcomer's" syndrome! I'm including an extract of the actual data here as I still can't get it to work.

               

              02197000|BLT,HEX,1.25|114.0700|Draglines|14|10|0|24|0|0|8.1|SASUPPLIER

              02203480|BLT,HEX,1.75|160.5800|Draglines|145|0|0|145|75|0|3.1|SASUPPLIER

              02203480|BLT,HEX,1.75x8,UNC,GMessage:|Signal:CRDragline/Vendor|CR|CostReview|RequisitionBlo|

              02258162|SCR,FH,.625x|60.0800|Drills|31|0|0|31|100|999,999|3.1|SASUPPLIER

              02714050|NT,HEX,HVY,|17.1700|Draglines|111|0|0|111|30|0|2.1|SASUPPLIER

              02714450|NT,HEX,JAM,|25.2100|Draglines|10|0|0|10|0|0|5.7|SASUPPLIER

              02717050|NT,HEX,FULL|34.7100|Drills|5|0|0|5|4|0|2.1|SASUPPLIER

              02718050|NT,HEX,FULL|30.4300|Draglines|100|0|0|100|144|999,999|3.1|SASUPPLIER

              02718050|NT,HEX,FULL,1.25,UNMessage:|Signal:CRDragline/Vendor|CR|CostReview|RequisitionBlo|

              02718200|NT,HEX,JAM,|27.1300|Draglines|69|0|0|69|144|999,999|2.1|SASUPPLIER

              02718200|NT,HEX,JAM,1.25,UNCMessage:|Signal:CRDragline/Vendor|CR|CostReview|RequisitionBlo|

              02720100|NT,HEX,FULL|64.1100|Draglines|31|0|16|15|144|999,999|2.9|SASUPPLIER

              02720100|NT,HEX,FULL,1.5,UNCMessage:|Signal:CRDragline/Vendor|CR|CostReview|RequisitionBlo|

              02720150|NT,HEX,JAM,|43.5000|Draglines|20|0|0|20|100|999,999|5.7|SASUPPLIER

              02722050|NT,HEX,HVY,|87.7000|Draglines|70|0|0|70|60|0|4.1|SASUPPLIER

              02725050|NT,HEX,FULL|188.3600|Draglines|22|0|0|22|120|999,999|5.7|SASUPPLIER

               

              Thanks,

              Hannes

                • Trapping adhoc extra line
                  Grant Perkins

                  Hannes,

                   

                  The second symptom of Newcomer's syndrome is that source material will be less than straightforward when presenting itself ...   

                   

                  My previous suggestion was related to a 'text' based report that would hopefully follow a consistent format. Most do but there have been exceptions.

                   

                  Unfortunately what you have is a 'pipe' delimited file (Source being SAP?) which makes the original suggestion less appropriate.

                   

                  As you have the Pro version of Monarch you can just read the file as a database but that will give you separate records for each line not the combined lines you seek.

                   

                  One might also consider the 'Floating Trap' feature but that can get a bit tricky with a lot of fields of varying sizes. More importantly you obvious floating trap, the "|" character, is a reserved character in Monarch related to the 'Numeric OR' trap. So in order to be able to use a floating trap you would need to use the Monarch Utility (or some other suitable program) to first change the "|" to a different character in the source file and then process with Monarch. easy enough but only moves you a short way forward and I am not sure the data us guaranteed to play ball anyway.

                   

                  As I can;t see an easy way to work with this file as it is - even the few record here present so many variations that I would imagine an entire report could only make life more complicated - I propose that you firstly change the format of the report to more clearly identify the detail first lines from the intermittent second lines. Here's one way.

                   

                  Read the file as a pipe ("|") delimited database. Lesson 15 of the learning guide would be a good reference here. The method is pretty much the same as the features for reading delimited files in Excel, Access and so on if you are familiar with them.

                   

                  That will get the data into a table with no templates required.

                   

                  Now create a SUMMARY with the first field in the table as a key, maybe the second as a key though it is probably not required if the sample lines are typical, and the the reast of the fields as ITEMS.

                   

                  Change the SUMMARY FIELD PROPERTIES of the first field (First Column) to "Suppress Duplicate Values" in the DISPLAY tab.

                   

                  Now EXPORT this summary to somewhere you can find it again!

                   

                  (At this point I will observe that we are some way beyond new user basics but don't panic.)

                   

                  Open another Monarch session and read in the file you have just exported as a text file, NOT a database.

                   

                  The start if the 'extra' lines should now be blank. This is good.

                   

                  Create a template to capture you detail lines which will be the ones with numbers in the first column. Keep it simple, we are going to use exactly the same trap for an append in a minute so the trap character positions need to be available in the additional lines as well. Paint in the field you need, name them and so on.

                   

                  Next create an APPEND template using the same trap as the detail but this time paint in the field that appear in the SECOND occasional lines. (To help with positioning you can select a new sample line based on one of those lines and 'promote' it to the sample are of the template definition window.)

                   

                  When you paint the first field for those rows - the second column in effect - right click on the field and go to the Advanced Properties tab,. In the 'Start field on' section of the screen click in the 'Preceding string of .... on the current row' and enter 9 spaces. (If you don't need any part of that field don't define it and move on to the next set of fields.)

                   

                  For the next column paint the field in the in advanced properties use a preceding string  "message". (I have assumed that MESSAGE will always appear in the first column of the extra lines if there are any. If this is not the case things will get more interesting bit let's get the concept working here before we look at any further issues.)

                   

                  Do the same for the remaining fields on those rows. The screen should show that the fields only exist on the extra lines.

                   

                  Go to the table screen and you should see the extra columns to the left of the detail data columns (by default since they are APPENDS which, theoretically, are at a higher level than the detail although in this case we have tweaked things a little ...  You can reposition the fields to where you want them to be for final output.

                   

                  I have a feeling there will be some more questions about manipulating the data in the fields but I won't try to second guess them now.

                   

                  You can automate the 2 step process using a simple batch script so that it can be run as a single activity or, if you are into coding, then the processes could be embedded into some smart code if you wish. Reading up on Projects and Project Exports would be advisable before moving into that stage. Not difficult if you have been suitably introduced to the concepts.

                   

                  If you go though this successfully (I'm sure you will) you will have rapidly progressed from novice to junior expert. If you can broaden the application of the knowledge gained here and apply it to other requirements by adaptation you can consider yourself expert and other things that you may well come across in the future will be that much easier to identify, plan and apply. You don't necessarily need to know precisely what every Monarch tool can do, just have a good feel for how it might be used once you read the Help precis of its capabilities and then take your imagination with you when you see what you can do with it.

                   

                  HTH.

                   

                  Sorry it's long but I'm assuming an attempt to spell it out, in the circumstances, may be the most productive help I can offer. If you get stuck let us know.

                   

                   

                   

                  Grant

                    • Trapping adhoc extra line
                      hakru _

                      Grant,

                       

                      Thank you very much for this detailed answer, it's now up to me to play around and get to grips with what is available!

                       

                      Hannes

                        • Trapping adhoc extra line
                          Grant Perkins

                          Hannes,

                           

                          It's a good learning exercise - not too complicated.

                           

                          I can provide some prototype models (based on your posted sample) if you get stuck.

                           

                          There are probably other ways to address the requirement - if the rest of the report does not throw up any big differences there is some potential for using a multi-line field concept to grab each record in a single field and then slice and dice it into individual fields using calculated fields in the Table.

                           

                          Also if you did not require the information in the second line second column (before the word "Message:") there are some further options that could be considered.

                           

                          It all quite easy once you have done it the first time - whichever method you adopt.

                           

                          Have fun.

                           

                           

                          Grant

                            • Trapping adhoc extra line
                              hakru _

                              Grant,

                               

                              I will appreciate some prototype models. I do not need any info prior to the "message:" string, I just need the line above the message with only the message "CR|CostReview" message as an added field on the line.

                               

                              Thanks,

                              Hannes

                                • Trapping adhoc extra line
                                  Grant Perkins

                                  Hannes,

                                   

                                  OK.

                                   

                                  I think we still need to go with the idea of reading the file as a database and generating the table.

                                   

                                  I doubt a model I produce will help you much with that - the database interpretation wizard will take you through the process.

                                   

                                  You should end up with a table that will export (Use a Fixed Font like Fixedsys) to a text file that will look like this.

                                   

                                   

                                  F1       F2                           F3                        F4        F5         F6                           F7         F8         F9        F10          F11 F12      

                                  02197000 BLT,HEX,1.25                 114.0700                  Draglines 14         10                            0         24          0          0          8.1 SASUPPLIER

                                  02203480 BLT,HEX,1.75                 160.5800                  Draglines 145        0                             0        145         75          0          3.1 SASUPPLIER

                                  02203480 BLT,HEX,1.75x8,UNC,GMessage: Signal:CRDra gline/Vendor CR        CostReview RequisitionBlo                                                                         

                                  02258162 SCR,FH,.625x                 60.0800                   Drills    31         0                             0         31        100     999999          3.1 SASUPPLIER

                                  02714050 NT,HEX,HVY,                  17.1700                   Draglines 111        0                             0        111         30          0          2.1 SASUPPLIER

                                  02714450 NT,HEX,JAM,                  25.2100                   Draglines 10         0                             0         10          0          0          5.7 SASUPPLIER

                                  02717050 NT,HEX,FULL                  34.7100                   Drills    5          0                             0          5          4          0          2.1 SASUPPLIER

                                  02718050 NT,HEX,FULL                  30.4300                   Draglines 100        0                             0        100        144     999999          3.1 SASUPPLIER

                                  02718050 NT,HEX,FULL,1.25,UNMessage:  Signal:CRDrag line/Vendor CR        CostReview RequisitionBlo                                                                         

                                  02718200 NT,HEX,JAM,                  27.1300                   Draglines 69         0                             0         69        144     999999          2.1 SASUPPLIER

                                  02718200 NT,HEX,JAM,1.25,UNCMessage:  Signal:CRDrag line/Vendor CR        CostReview RequisitionBlo                                                                         

                                  02720100 NT,HEX,FULL                  64.1100                   Draglines 31         0                            16         15        144     999999          2.9 SASUPPLIER

                                  02720100 NT,HEX,FULL,1.5,UNCMessage:  Signal:CRDrag line/Vendor CR        CostReview RequisitionBlo                                                                         

                                  02720150 NT,HEX,JAM,                  43.5000                   Draglines 20         0                             0         20        100     999999          5.7 SASUPPLIER

                                  02722050 NT,HEX,HVY,                  87.7000                   Draglines 70         0                             0         70         60          0          4.1 SASUPPLIER

                                  02725050 NT,HEX,FULL                  188.3600                  Draglines 22         0                             0         22        120     999999          5.7 SASUPPLIER

                                   

                                  /code

                                   

                                  The F (Field) numbers across the top are column headers - I did not bother to name them at that point.

                                   

                                  So, in the second model you can now clearly identify the constant lines from the occasional lines. Creating a template to trap the constant lines only should be quite straightforward if you use a trap for one or more of the fields that appear off to the right of the line.

                                   

                                  For the Occasional lines create an Append template as previously suggested and use exactly the same traps as for the detail template. This will force the Append to have a 1:1 relationship with its detail line and no other line.

                                   

                                  For the data field all you need is one (or 2 if you want to separate the CR code from the text that goes with it) field 'painted' where the information will appear in columns F4/F5. Right click on the field to access the advanced properties, set the 'Start field on' to be a 'Preceding string of ' message: 'on the current line' and save the template. That should be all you need to extract just what you want to a table ready for whatever you want to do with it next.

                                   

                                  If you send me a Private Message with an email address that will accept attachments I will forward a suitable model for this part of the solution with the sample file used to produce it. In the meantime have a go at reading the file as a database for the first part of the solution.

                                   

                                  HTH.

                                   

                                   

                                   

                                  Grant