29 Replies Latest reply: May 15, 2014 9:52 AM by Grant Perkins RSS

    Multiple Line Wrap

    blankel _

      I'm working as a consultant for a mortgage company.  I've written an application that automates the process of grabbing files from an ftp folder and then loading them into an Access database for various users to work with.

       

      I've had good luck with Monarch so far, but I'm having difficulty with the following records.  I've read everything I can find on multiple line wrapping, but I'm just not getting it.  Hopefully someone here can help.

       

      I've put sample data below.  The NNNNNNNN strings represent the loan number.

       

      The problem is with the last column, which wraps to the next line.  In sample 1, you can see where there is a value in the top line and then more detail in the next line:

       

      SAMPLE 1[/b]

      [font="courier"] 

         NNNNNNN       NNNNNNN    07-10-06  50    118.37     123.59   621.45           621.45    745.04      99,308.91           99.30-REP 

                            123.59 DI REPAID                    .00  ASSET INV REPAID                                                      

                  745.04   .0750000   .0000000   05-01-06                                                                                /font[/quote]In sample two, there is a second wrapped line, but there is nothing in the first line.

       

      SAMPLE 2[/b]

      [font="courier"]

         NNNNNNN       NNNNNNN    07-10-06  37    193.41     114.61   755.67           755.67    870.28     120,792.60                       

                            114.61 DI REPAID                    .00  ASSET INV REPAID                                                        

                  870.28   .0750000   .0000000   06-01-05                                               /font[/quote]In sample 3, there is no data in the field at all.

       

      SAMPLE 3[/b]

      [font="courier"]   NNNNNNN       NNNNNNN    07-10-06  37    193.41-                                                   120,792.60                       

                  870.28   .0750000   .0000000   06-01-05                                                                               

      /font[/quote]I've tried creating a multiple line field, but it doesn't appear to wrap on the second line, just starts at the same point as the first line, which won't work, as you can see from the samples above.

       

      Help!

        • Multiple Line Wrap
          blankel _

          I'm working as a consultant for a mortgage company.  I've written an application that automates the process of grabbing files from an ftp folder and then loading them into an Access database for various users to work with.

           

          I've had good luck with Monarch so far, but I'm having difficulty with the following records.  I've read everything I can find on multiple line wrapping, but I'm just not getting it.  Hopefully someone here can help.

           

          I've put sample data below.  The NNNNNNNN strings represent the loan number.

           

          The problem is with the last column, which wraps to the next line.  In sample 1, you can see where there is a value in the top line and then more detail in the next line:

           

          SAMPLE 1[/b]

          [font="courier"] 

             NNNNNNN       NNNNNNN    07-10-06  50    118.37     123.59   621.45           621.45    745.04      99,308.91           99.30-REP 

                                123.59 DI REPAID                    .00  ASSET INV REPAID                                                      

                      745.04   .0750000   .0000000   05-01-06                                                                                /font[/quote]In sample two, there is a second wrapped line, but there is nothing in the first line.

           

          SAMPLE 2[/b]

          [font="courier"]

             NNNNNNN       NNNNNNN    07-10-06  37    193.41     114.61   755.67           755.67    870.28     120,792.60                       

                                114.61 DI REPAID                    .00  ASSET INV REPAID                                                        

                      870.28   .0750000   .0000000   06-01-05                                               /font[/quote]In sample 3, there is no data in the field at all.

           

          SAMPLE 3[/b]

          [font="courier"]   NNNNNNN       NNNNNNN    07-10-06  37    193.41-                                                   120,792.60                       

                      870.28   .0750000   .0000000   06-01-05                                                                               

          /font[/quote]I've tried creating a multiple line field, but it doesn't appear to wrap on the second line, just starts at the same point as the first line, which won't work, as you can see from the samples above.

           

          Help!

          • Multiple Line Wrap
            Data Kruncher

            I'd be tempted to break this up into pieces that should, from the look of the samples, be easier to deal with.

             

            If the samples are consistent with the rest of the report, you'll always have the fields in the last row. I'd make the last line the detail template, and each line above it an individual append template.

             

            Will that resolve this problem?

             

            Kruncher

            • Multiple Line Wrap
              Data Kruncher

              I'd be tempted to break this up into pieces that should, from the look of the samples, be easier to deal with.

               

              If the samples are consistent with the rest of the report, you'll always have the fields in the last row. I'd make the last line the detail template, and each line above it an individual append template.

               

              Will that resolve this problem?

               

              Kruncher

              • Multiple Line Wrap
                blankel _

                It would, except I have a summary line on some pages which contains similar text to Sample 2.

                 

                What I need to be able to do is set a trap that says, this is the other trust field only when there is a loan number in the first 10 positions of the top line.

                • Multiple Line Wrap
                  blankel _

                  It would, except I have a summary line on some pages which contains similar text to Sample 2.

                   

                  What I need to be able to do is set a trap that says, this is the other trust field only when there is a loan number in the first 10 positions of the top line.

                  • Multiple Line Wrap
                    blankel _

                    Here's where I start having problems:  There is a footer on some records that looks like this:

                     

                    [font="courier"]

                    CATEGORY TOTAL        82   LOANS                     8,973.37         1,800.43          32,855.16                475.28                

                                                             17,242.41           25,682.22        23,881.79             6,713,939.75                       

                                        1,076.74 DI REPAID                    .00  ASSET INV REPAID                 .00  NON-ASSET REPAID                  

                    /font[/quote]What I need to be able to do is pick the second line up when the top line contains a loan number, but not when it doesn't contain a loan number

                    • Multiple Line Wrap
                      blankel _

                      Here's where I start having problems:  There is a footer on some records that looks like this:

                       

                      [font="courier"]

                      CATEGORY TOTAL        82   LOANS                     8,973.37         1,800.43          32,855.16                475.28                

                                                               17,242.41           25,682.22        23,881.79             6,713,939.75                       

                                          1,076.74 DI REPAID                    .00  ASSET INV REPAID                 .00  NON-ASSET REPAID                  

                      /font[/quote]What I need to be able to do is pick the second line up when the top line contains a loan number, but not when it doesn't contain a loan number

                      • Multiple Line Wrap
                        Data Kruncher

                        Aha, as suspected...

                         

                        You'll need to define a filter to exclude those summary records. If you capture the loan number as a numeric, the summary records should be null. Set the filter to [font="courier"].Not. IsNull(LoanNumber)  /font[/quote]and you'll exclude those pesky summary items.

                         

                        Kruncher

                        • Multiple Line Wrap
                          Data Kruncher

                          Aha, as suspected...

                           

                          You'll need to define a filter to exclude those summary records. If you capture the loan number as a numeric, the summary records should be null. Set the filter to [font="courier"].Not. IsNull(LoanNumber)  /font[/quote]and you'll exclude those pesky summary items.

                           

                          Kruncher

                          • Multiple Line Wrap
                            blankel _

                            That doesn't appear to solve the problem, as I have the wrapped Other Trust field populated for records in the table view that it isn't associated with in the report.  I did get rid of the summary rows, but now the summary data is getting tacked on to all of the records following the occurrance of the summary row.

                             

                            [size="1"][ July 13, 2006, 02:15 PM: Message edited by: blankel ][/size]

                            • Multiple Line Wrap
                              blankel _

                              That doesn't appear to solve the problem, as I have the wrapped Other Trust field populated for records in the table view that it isn't associated with in the report.  I did get rid of the summary rows, but now the summary data is getting tacked on to all of the records following the occurrance of the summary row.

                               

                              [size="1"][ July 13, 2006, 02:15 PM: Message edited by: blankel ][/size]

                              • Multiple Line Wrap
                                Data Kruncher

                                Sorry, but for now I'm out of both ideas and time. "Real" work and all. Hope you understand.

                                 

                                Can someone please jump in?

                                 

                                Kruncher

                                • Multiple Line Wrap
                                  Data Kruncher

                                  Sorry, but for now I'm out of both ideas and time. "Real" work and all. Hope you understand.

                                   

                                  Can someone please jump in?

                                   

                                  Kruncher

                                  • Multiple Line Wrap
                                    blankel _

                                    I appreciate the help you've given so far.  I'm learning Monarch on the fly, so this forum has been a huge help.

                                     

                                    What would be very useful is being able to define multi line traps.

                                    • Multiple Line Wrap
                                      blankel _

                                      I appreciate the help you've given so far.  I'm learning Monarch on the fly, so this forum has been a huge help.

                                       

                                      What would be very useful is being able to define multi line traps.

                                      • Multiple Line Wrap
                                        Grant Perkins

                                        Hmm. Nasty looking report even if one was just reading it!

                                         

                                        Taking you second problem first.

                                         

                                        Is it reasonable to assume that the summary lines start with "CATEGORY TOTAL" but the main report detail lines start with something else. (It would be really good if "something else" was a numerice field!)

                                         

                                        If so you should be able to come up with a trap that would exclude the summaries. Or teats them as footers with no fields. Or if not just treat them as detail and filter them out. Just as Kruncher suggested really so maybe I have missed some subtle problems with that.

                                         

                                        For your first problem, I think the report is really one very long (potentially) detail row, wrapped onto 2 lines for output. Lets deal with it like that.

                                         

                                        Trap the Loan Number rows AS A SINGLE FULL WIDTH FIELD and make it a multi line field.

                                         

                                        Rows like

                                         

                                        [font="courier"]745.04   .0750000   .0000000   05-01-06 /font[/quote]always seem to appear so make them them footers.   

                                         

                                        You will now have a single field which contains the info from the second line if there is any.

                                         

                                        Define a set of calculated fields that give the fields you really want and use formulae utilising the LSPLIT or RSPLIT functions and similar. You may also need to lok at the TRIM/RTRIM/LTRIM and INTRIM functions, thoguh I doubt you will need all of them.

                                         

                                        Since you have V8 Pro you could also check out the function TEXTLINE and particularly its ability to, in this case, identify whether or not a second line is involved.

                                         

                                        Or, more interestingly, use the SEARCH option of TEXTLINE to identify if the text  "DI REPAID" or "ASSET INV REPAID " occur in which case you can pick out their related values with some smart parsing of the data related to that line.

                                         

                                        Slicing and dicing the entire line sound clumsy at first BUT, once you see what is possible and how the calculated fields can be created very quickly, it becomes almost a natural way to do things!

                                         

                                        Honestly!

                                         

                                        I could work an example using your sample lines if they are truly representative. Let me know if that wouold help and PM me with an email address to send a model to when done.

                                         

                                        HTH

                                         

                                        Grant

                                        • Multiple Line Wrap
                                          Grant Perkins

                                          Hmm. Nasty looking report even if one was just reading it!

                                           

                                          Taking you second problem first.

                                           

                                          Is it reasonable to assume that the summary lines start with "CATEGORY TOTAL" but the main report detail lines start with something else. (It would be really good if "something else" was a numerice field!)

                                           

                                          If so you should be able to come up with a trap that would exclude the summaries. Or teats them as footers with no fields. Or if not just treat them as detail and filter them out. Just as Kruncher suggested really so maybe I have missed some subtle problems with that.

                                           

                                          For your first problem, I think the report is really one very long (potentially) detail row, wrapped onto 2 lines for output. Lets deal with it like that.

                                           

                                          Trap the Loan Number rows AS A SINGLE FULL WIDTH FIELD and make it a multi line field.

                                           

                                          Rows like

                                           

                                          [font="courier"]745.04   .0750000   .0000000   05-01-06 /font[/quote]always seem to appear so make them them footers.   

                                           

                                          You will now have a single field which contains the info from the second line if there is any.

                                           

                                          Define a set of calculated fields that give the fields you really want and use formulae utilising the LSPLIT or RSPLIT functions and similar. You may also need to lok at the TRIM/RTRIM/LTRIM and INTRIM functions, thoguh I doubt you will need all of them.

                                           

                                          Since you have V8 Pro you could also check out the function TEXTLINE and particularly its ability to, in this case, identify whether or not a second line is involved.

                                           

                                          Or, more interestingly, use the SEARCH option of TEXTLINE to identify if the text  "DI REPAID" or "ASSET INV REPAID " occur in which case you can pick out their related values with some smart parsing of the data related to that line.

                                           

                                          Slicing and dicing the entire line sound clumsy at first BUT, once you see what is possible and how the calculated fields can be created very quickly, it becomes almost a natural way to do things!

                                           

                                          Honestly!

                                           

                                          I could work an example using your sample lines if they are truly representative. Let me know if that wouold help and PM me with an email address to send a model to when done.

                                           

                                          HTH

                                           

                                          Grant

                                          • Multiple Line Wrap
                                            blankel _

                                            Okay..I'm back. 5 months later.  I have everything working, except I now notice that on some records, I lose the last three or four fields (P&I Constant, Ann I/R, SF RATE, Date Due) when the last field of the first line is wrapped.  In otherwords, the total length of the field appears to be longer than Monarch can handle.  Any other ideas?

                                            • Multiple Line Wrap
                                              blankel _

                                              Okay..I'm back. 5 months later.  I have everything working, except I now notice that on some records, I lose the last three or four fields (P&I Constant, Ann I/R, SF RATE, Date Due) when the last field of the first line is wrapped.  In otherwords, the total length of the field appears to be longer than Monarch can handle.  Any other ideas?

                                              • Multiple Line Wrap
                                                Grant Perkins

                                                Hi blankel,

                                                 

                                                I'll guess that you have the multiline field set as a CHARACTER field which is where the size restriction comes in.

                                                 

                                                If so  change it to a MEMO field and you should see the other characters currently missing. Make you of the "Show Field Contents" from the WINDOW menu to view the entire current field.

                                                 

                                                Here are the specs for the max data length of the two field types.

                                                 

                                                Character field length     254 characters

                                                Memo field length     65536 characters

                                                 

                                                HTH.

                                                 

                                                If this is not a good answer let me know what fials the test and I will see if I can come up with something else.

                                                 

                                                Grant

                                                • Multiple Line Wrap
                                                  Grant Perkins

                                                  Hi blankel,

                                                   

                                                  I'll guess that you have the multiline field set as a CHARACTER field which is where the size restriction comes in.

                                                   

                                                  If so  change it to a MEMO field and you should see the other characters currently missing. Make you of the "Show Field Contents" from the WINDOW menu to view the entire current field.

                                                   

                                                  Here are the specs for the max data length of the two field types.

                                                   

                                                  Character field length     254 characters

                                                  Memo field length     65536 characters

                                                   

                                                  HTH.

                                                   

                                                  If this is not a good answer let me know what fials the test and I will see if I can come up with something else.

                                                   

                                                  Grant

                                                  • Multiple Line Wrap
                                                    blankel _

                                                    Actually, I figured it out after I wrote the message.  You had mentioned using the third line as a footer, which I hadn't done yet. 

                                                     

                                                    I'm reviewing now to see if the second line worked right.  Sorry to cry wolf, will let you know how it came out.

                                                    • Multiple Line Wrap
                                                      blankel _

                                                      Actually, I figured it out after I wrote the message.  You had mentioned using the third line as a footer, which I hadn't done yet. 

                                                       

                                                      I'm reviewing now to see if the second line worked right.  Sorry to cry wolf, will let you know how it came out.

                                                      • Multiple Line Wrap
                                                        Grant Perkins

                                                        Ah! That should do it as well.

                                                         

                                                        However the change to MEMO type is still worth considering or at least keeping somewhere close at hand in your personal Monarch toolkit!     

                                                         

                                                         

                                                        Grant

                                                        • Multiple Line Wrap
                                                          Grant Perkins

                                                          Ah! That should do it as well.

                                                           

                                                          However the change to MEMO type is still worth considering or at least keeping somewhere close at hand in your personal Monarch toolkit!     

                                                           

                                                           

                                                          Grant

                                                          • Multiple Line Wrap
                                                            blankel _

                                                            Curiouser and Curiouser!

                                                             

                                                            Is there any reason why a record would match the trap but not be captured in the results?  I have a situation where one record fails to be trapped.  Everything else is working fine.

                                                             

                                                            Here's some obfuscated sample data

                                                             

                                                            [font="courier"]

                                                            1123456789      9999999999 11-29-06  23    294.65     252.71   838.89   59.92   778.97   1031.68     191,492.82     <<<<<This traps                                                                               

                                                            1091.60   .0525000   .0037500   12-01-06                                                                               

                                                            1061-210                                        M O R T G A G E  S E R V I C E S                                           11/29/06                                                                               

                                                            REMITTANCE REPORT                                                  PAGE  68                                                       

                                                            XY HOUSING FINANCE CORP                                                                               

                                                            PO BOX 101020                               INTEREST RATE .00000000        SERVICE FEE .00000000       STATE                                                                               

                                                            BLANCMANGE XY 99510         INVESTOR W99      CATEGORY 111                                                                               

                                                            -


                                                                                                                     

                                                                             INVESTOR    DATE   PMT                                SERVICE   NET    DEPOSITED   PRINCIPAL     LATE   OTHER                                                            

                                                            OUR LOAN NO       LOAN NO    PAID   NO     ESCROW  PRINCIPAL INTEREST    FEE  INTEREST  /REMITTED    BALANCE     CHARGE  TRUST                                                            

                                                            +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ <<<<<<This is the page break

                                                                   P&I CONSTANT  ANN I/R    SF RATE   DATE DUE                                                                               

                                                            -


                                                                                                                     

                                                            1123456789      9999999999 11-29-06  23                13.75                               13.75     191,479.07   <<<<<<This does not  :confused:                                                                               

                                                            1091.60   .0525000   .0037500   12-01-06                                                                               

                                                            2345678901      1234567890 11-29-06  21    310.68     227.30   782.89   54.62   728.27    955.57     174,558.48    <<<<<<This traps 

                                                            /font[/quote]The line occurs just after a page break, but the page break occurs in the middle of detail, and the   I had this problem in another model, as well, so I suspect this is something that I just haven't learned about yet.  Any help you can give would be appreciated.

                                                            • Multiple Line Wrap
                                                              blankel _

                                                              Curiouser and Curiouser!

                                                               

                                                              Is there any reason why a record would match the trap but not be captured in the results?  I have a situation where one record fails to be trapped.  Everything else is working fine.

                                                               

                                                              Here's some obfuscated sample data

                                                               

                                                              [font="courier"]

                                                              1123456789      9999999999 11-29-06  23    294.65     252.71   838.89   59.92   778.97   1031.68     191,492.82     <<<<<This traps                                                                               

                                                              1091.60   .0525000   .0037500   12-01-06                                                                               

                                                              1061-210                                        M O R T G A G E  S E R V I C E S                                           11/29/06                                                                               

                                                              REMITTANCE REPORT                                                  PAGE  68                                                       

                                                              XY HOUSING FINANCE CORP                                                                               

                                                              PO BOX 101020                               INTEREST RATE .00000000        SERVICE FEE .00000000       STATE                                                                               

                                                              BLANCMANGE XY 99510         INVESTOR W99      CATEGORY 111                                                                               

                                                              -


                                                                                                                       

                                                                               INVESTOR    DATE   PMT                                SERVICE   NET    DEPOSITED   PRINCIPAL     LATE   OTHER                                                            

                                                              OUR LOAN NO       LOAN NO    PAID   NO     ESCROW  PRINCIPAL INTEREST    FEE  INTEREST  /REMITTED    BALANCE     CHARGE  TRUST                                                            

                                                              +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ <<<<<<This is the page break

                                                                     P&I CONSTANT  ANN I/R    SF RATE   DATE DUE                                                                               

                                                              -


                                                                                                                       

                                                              1123456789      9999999999 11-29-06  23                13.75                               13.75     191,479.07   <<<<<<This does not  :confused:                                                                               

                                                              1091.60   .0525000   .0037500   12-01-06                                                                               

                                                              2345678901      1234567890 11-29-06  21    310.68     227.30   782.89   54.62   728.27    955.57     174,558.48    <<<<<<This traps 

                                                              /font[/quote]The line occurs just after a page break, but the page break occurs in the middle of detail, and the   I had this problem in another model, as well, so I suspect this is something that I just haven't learned about yet.  Any help you can give would be appreciated.

                                                              • Multiple Line Wrap
                                                                Grant Perkins

                                                                Hi blankel,

                                                                 

                                                                Have you got a page header template defined?

                                                                 

                                                                If so might there be an anomaly with the number of lines defined for the header as it applies to this particualr page break?

                                                                 

                                                                I'm not sure which record you are missing - the one right under the page header or the one below it with no subsequent line shown?

                                                                 

                                                                If you are using the first line as a potential MULTILINE field, which I think is the case here, then the END ON definition may be a problem, althogh if you have implemented footers this should not be the case as far as I know.

                                                                 

                                                                If you got back to the REPORT page for that page break where does Monarch think its fields should be? Any in the middle of the page header area (which are not part of a page header template!)?

                                                                 

                                                                Grant

                                                                Grant

                                                                • Multiple Line Wrap
                                                                  Grant Perkins

                                                                  Hi blankel,

                                                                   

                                                                  Have you got a page header template defined?

                                                                   

                                                                  If so might there be an anomaly with the number of lines defined for the header as it applies to this particualr page break?

                                                                   

                                                                  I'm not sure which record you are missing - the one right under the page header or the one below it with no subsequent line shown?

                                                                   

                                                                  If you are using the first line as a potential MULTILINE field, which I think is the case here, then the END ON definition may be a problem, althogh if you have implemented footers this should not be the case as far as I know.

                                                                   

                                                                  If you got back to the REPORT page for that page break where does Monarch think its fields should be? Any in the middle of the page header area (which are not part of a page header template!)?

                                                                   

                                                                  Grant

                                                                  Grant