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

    Multi-line trap w/optional records

    scottnelson _

      I have a data file that has account records split across multiple lines.  E.g., for account # ABC123456, I might have an A record on one line, a B record on the next, and sometimes there will be a C record on the next line.

       

      Another account, say, XYZ456777, might have only A and B records, no C record.

       

      E.g.,

       

      AABC123456Some other fields

      BABC123456Some other fields

      CABC123456Some other fields

      AXYZ456777Some other fields

      BXYZ456777Some other fields

       

       

      If I try to create a detail template to trap all three records (A, B, C), Monarch v9 Pro will only trap the ABC123456 account.  It will skip XYZ456777 because it lacks a C record.

       

      If I try to trap only A and B with one template, I can create an append template for the C record, but then Monarch appends the C record for ABC123456 onto EVERY subsequent record until it finds some other account that has a C record.

       

      In the past, the only way I've been able to deal with this is to create two distinct models, one that extracts A and B records, and a second that extracts only the C record.  Then I have to load both into a database and join the two of them based on account number.

       

      Surely there must be a better way....

        • Multi-line trap w/optional records
          Bruce _

          Scott are you picking up everything on the line as one detail template or are you picking up "some other data" as the detail and the customer number as an append?

           

          If you set it up vie the 1st way you might have to change your model around.

           

          Normally the lowest level of data is the detail record, then anything above would be an append, and any thing after is a footer. The page header is for anything you need that shows up at the top of each page.

           

          If you already use the second method there is something else wrong.

           

          Post back & let us know how you make out.

            • Multi-line trap w/optional records
              Grant Perkins

              Hi Scott and welcome to the forum!

               

              Are these line really all part of one single row long record but have been wrapped for display?

               

              If so you might find the Monarch Utility useful to pre-process the file (I'm guessing the the EOL markers will be different where a record ends) and if you have the Pro Monarch 9 you could them read the resulting line as a database.

               

              There are other possible options but more than likely we would need to be assessing a good sample of what you are dealing with to be able to propose a solid solution - they can become very dependent on layout and content for this sort of file in my experience.

               

              HTH.

               

              Grant

              • Multi-line trap w/optional records
                scottnelson _

                I'm trying to combine all 3 records, A, B, and C, into one detail line.

                 

                I don't care whether I use "Detail" and/or "Append" templates, I'm just trying to find a clean way to make it work.

                  • Multi-line trap w/optional records
                    scottnelson _

                    Grant,

                     

                    no, each account has multiple physical lines (not wrapped) that constitute one logical record.

                     

                    The way you see them on the Monarch forum web site is exactly how they appear in the data file.

                      • Multi-line trap w/optional records
                        Grant Perkins

                        OK, interesting.

                         

                        I'll summarise what I think I understand of it so far then suggest a couple of ideas for you to consider.

                         

                        Each record has at least 2 lines, some may have a third line, they are all part of a single record and no two lines share overlapping data fields. i.e. if there are three lines the data on each line are unique and lines B and C are not both possible complete detail lines hanging off line A.

                         

                        I assume you have a way of trapping line A in a two line template that would not also potentially trap line B or line C. That is important. Your trap for DETAIL must either successfully trap LINE As ONLY  or possibly line Bs only. It must never trap Line Cs.

                         

                        Even if you detail trap on B Lines, A lines as APPENDS will still require a unique trap as fas as I can tell, so we may as well seek a solution based on trapping on A for the detail for all 3 possible lines.

                         

                        Making that assumption, you would need a 2 line sample for the template. Trap the first line and define the fields.

                         

                        For the second line grab the entire line (except perhaps the repeated first Account Number) and define a character memo field that grabs the whole line. Right click on the field and go to the advanced options tab to set an 'end on' value that that will encourage the field to stop reading lines when it encounters another detail template - the next record's A line.

                         

                        You will now have one large field that will include C lines when they occur. The large field will need slicing and dicing to extract the individual field you require using Calculated fields in the table window.

                         

                        Absent a representative sample it's a little difficult to suggest exactly what to use for that but as you have V9 a starter function would be TextLine to identify the B and C lines separately and then functions like the LSPLIT and RSPLIT tools will likely be very useful. In general the String functions group and probably the Conversion functions are your tools of choice for slicing and dicing.

                         

                        If you have done it before you will likely need no help. If not, once you see what you have got from the initial extraction post some representative samples and people will be able to assist with a way to move forward based on your report's data structure post extraction.

                         

                        HTH.

                         

                         

                        Grant