1 Reply Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Difficult trap - need some help

    Melissa2 _

      I have a daily file the I need help on.  It is probably pretty simple, but I am having trouble with this so bear with me.  The detail for every record is located in the first line, but some records have 2 or even three message lines after the detail line that I need captured on just those records.  A sample of the file is below:

       

                                                            

          • Testtest V10.05 ***                                            Page      7                                                                               

      Yakima Valley Credit Union...ACH Posting Report for 01SEP05 - Credits Only                 As of: 01SEP05   Run: 01SEP05    7:19 AM                                                                               

      Beginning  Trn  Db/                   Ending  Source  Dist Trace                Disc  File     

        Account No. Sf  Name          JI       Balance  Cde  Cr      Amount       Balance  Code    Src  Number               Data  ID                                                                               

      11111-9  NAME           P        9000.00   22  C       100.00       9100.00  SOURCE1  *   021000013792263            0830D    

               1111-9  NAME           P        4742.46   32  C       150.00       4892.46  XXX      *   081036303086392            0826B    

              11111-9  NAME           P        2656.55   22  C       698.96       3355.51  XXXX     *   041036000177454     08     0829A    

              11111-9  NAME           P        7117.92   22  C      2263.75       9381.67  XXX      *   041036000177391     04     0829A    

              11111-9  NAME           P          78.46   32  C       635.05        713.51  XXX      *   041036000177450     08     0829A    

              11111-9  NAME           P        7296.99   32  C       693.13       7990.12  XXX      *   121736153326650            0826C    

              11111-9  NAME           P        2030.02   32  C       977.33       3007.35  XXX      *   041036000177377     02     0829A    

              11111-9  NAME           P        6400.89   32  C       129.72       6530.61  XXX      *   071000157917165     9      0830A    

              11111-9  NAME           P        4563.55   22  C       300.00       4863.55  XXX      *   042000014260866     ZZ     0830A    

                        Addendum Record: LAST NAME                FIRST NAME

              11111-9  NAME           P        4563.55   22  C        40.41       4603.96  XXXX     *   021001036153579            0831A    

              11111-9  NAME           P        2420.23   22  C       716.89       3137.12  XXXX     *   042000014260496     ZZ     0830A    

                        Addendum Record: LAST NAME                FIRST NAME                                                                      

              11111-9  NAME           P        2771.75   22  C      1021.03       3792.78  XXXX     *   042000014260602     ZZ     0830A    

                        Addendum Record: LAST NAME                FIRST NAME                                                           

              11111-9  NAME           P        1250.04   22  C       713.50       1963.54  XXXX     *   021000024008618     1      0830D    

              11111-9  NAME           P        1981.70   22  C       612.00       2593.70  XXXX     *   021000024008623     1      0830D    

              11111-9  NAME           P       20150.00   22  C        50.00      20200.00  XXXX     *   043301603297781            0830D    

              11111-9  NAME           P        6717.02   22  C       300.00       7017.02  XXXX     *   021000024008630     1      0830D    

              11111-9  NAME           P        6717.02   22  C      1481.92       8198.94  XXXX     *   021000024008639     1      0830D    

              11111-9  NAME           P        6717.02   22  C        27.50       6744.52  XXXXX    *   042000012552703            0830D    

               1111-9  NAME           P        2836.58   27  D        40.12       2796.46  XXXXX        021200025234293            0831A    

               1111-9  NAME           P           0.00   27  D       131.96       -131.96  XXXXXX       104000012645023            0831A    

                               Messages: INSUFFICIENT FUNDS ..OD amt -131.96 ..COVERED BY XFER(S)                                           

              11111-9  NAME           P         470.63   27  D       186.73        283.90  XXXXX        091000011766551            0831A    

              11111-9  NAME           P         283.90                20.00        263.90                                                   

                               Messages:  ..TOTAL FEE(S)-Returns

        • Difficult trap - need some help
          Grant Perkins

          Originally posted by Melissa:

          [quote][font="courier"]                                                     *** Testtest V10.05 ***                                            Page      7                                                                               

          Yakima Valley Credit Union...ACH Posting Report for 01SEP05 - Credits Only                 As of: 01SEP05   Run: 01SEP05    7:19 AM                                                                               

          Beginning  Trn  Db/                   Ending  Source  Dist Trace                Disc  File     

            Account No. Sf  Name          JI       Balance  Cde  Cr      Amount       Balance  Code    Src  Number               Data  ID                                                                               

          11111-9  NAME           P        9000.00   22  C       100.00       9100.00  SOURCE1  *   021000013792263            0830D    

                   1111-9  NAME           P        4742.46   32  C       150.00       4892.46  XXX      *   081036303086392            0826B    

                  11111-9  NAME           P        2656.55   22  C       698.96       3355.51  XXXX     *   041036000177454     08     0829A    

                  11111-9  NAME           P        7117.92   22  C      2263.75       9381.67  XXX      *   041036000177391     04     0829A    

                  11111-9  NAME           P          78.46   32  C       635.05        713.51  XXX      *   041036000177450     08     0829A    

                  11111-9  NAME           P        7296.99   32  C       693.13       7990.12  XXX      *   121736153326650            0826C    

                  11111-9  NAME           P        2030.02   32  C       977.33       3007.35  XXX      *   041036000177377     02     0829A    

                  11111-9  NAME           P        6400.89   32  C       129.72       6530.61  XXX      *   071000157917165     9      0830A    

                  11111-9  NAME           P        4563.55   22  C       300.00       4863.55  XXX      *   042000014260866     ZZ     0830A    

                            Addendum Record: LAST NAME                FIRST NAME

                  11111-9  NAME           P        4563.55   22  C        40.41       4603.96  XXXX     *   021001036153579            0831A    

                  11111-9  NAME           P        2420.23   22  C       716.89       3137.12  XXXX     *   042000014260496     ZZ     0830A    

                            Addendum Record: LAST NAME                FIRST NAME                                                                      

                  11111-9  NAME           P        2771.75   22  C      1021.03       3792.78  XXXX     *   042000014260602     ZZ     0830A    

                            Addendum Record: LAST NAME                FIRST NAME                                                           

                  11111-9  NAME           P        1250.04   22  C       713.50       1963.54  XXXX     *   021000024008618     1      0830D    

                  11111-9  NAME           P        1981.70   22  C       612.00       2593.70  XXXX     *   021000024008623     1      0830D    

                  11111-9  NAME           P       20150.00   22  C        50.00      20200.00  XXXX     *   043301603297781            0830D    

                  11111-9  NAME           P        6717.02   22  C       300.00       7017.02  XXXX     *   021000024008630     1      0830D    

                  11111-9  NAME           P        6717.02   22  C      1481.92       8198.94  XXXX     *   021000024008639     1      0830D    

                  11111-9  NAME           P        6717.02   22  C        27.50       6744.52  XXXXX    *   042000012552703            0830D    

                   1111-9  NAME           P        2836.58   27  D        40.12       2796.46  XXXXX        021200025234293            0831A    

                   1111-9  NAME           P           0.00   27  D       131.96       -131.96  XXXXXX       104000012645023            0831A    

                                   Messages: INSUFFICIENT FUNDS ..OD amt -131.96 ..COVERED BY XFER(S)                                           

                  11111-9  NAME           P         470.63   27  D       186.73        283.90  XXXXX        091000011766551            0831A    

                  11111-9  NAME           P         283.90                20.00        263.90                                                   

                                   Messages:  ..TOTAL FEE(S)-Returns /font[/quote][/b][/quote]Melissa,

           

          A couple of approaches come to mind. Both require at least one extra step in the process.

           

          Method 1.

           

          Pre-process the file to introduce a space line between each record (you will see why in a minute.)  I did this using MS Word since the sample is a small file and Word is the quickest way to get a result for testing. In a full Monarch soluiton I would expect to use the MSRP utility for ease of automation and speed of processing if the report is normally a large file.

           

          What you need to do is change the control character at the end of each line from a single line feed to a double line feed.

           

          In Word this means using the REPLACE feature and replacing ^p  with pp . (MSRP will require a different command to achieve the same result.)

           

          The effect will be to double the number of lins in the report by adding a blank after every existing line.

           

          Now select a 2 line sample for you detail template, the first line being one of the existing lines of detail and the second line a blank line.

           

          Paint the fields you need from the first line and in the second line paint a field that will be wide enough to accomodate the information in any of the mesages which may appear.

           

          From the sample it looks like it should start under the "A" in "NAME" and be about 80 characters wide but that is something you can check for the full report.

           

          Right mouse button click on the long field and set the Advanced properties for "End field on" to "None of the Above" ( I assume you have V7 or V8. If not the text may be slightly different but the effect will be the same.) That will give you a large text field that will pick up any messages that appear anywhere from the second line of the record until the start of the next detail record. The text will all be in one field. If you need to split it out again there are ways but the approach will depend on which version of Monarch you have. Version 8 would make it really easy.

           

          Method 2

           

          Requires a Pro version of Monarch and use of the lookup facility.

           

          Step one would be to create a model that extracts ONLY the additional message lines (They would be the detail lines for this purpose) and information from the 'real' detail lines with which they are associated that will provided a unique key to connect them back to in the second part of the process. So the 'Real detail' lines wil, for this stage of the process, be part of an Append template.

           

          Export the resulting table as a 2 column report to a text file or a table for Excel or Access or similar. It should be sorted in Key field order.

           

          Create a secong model for the report to select the Detail lines again, map the fields as normal. Add any appends required form the header type information at the top of the report.

           

          From the resulting Table screen create a calculated field for an External Lookup and link it to the fiel you created with the previous extraction fo the additional text. Taht will bring the text in for just the records which exist in the external lookup file.

           

          I could provide more detailed instructions about the second method but I would really need to get some info from you first regarding the data and which version of Monarch you have

           

          I hope these suggestion help you. I think the first method is probably the better one for you, the MSRP part can be built into a batch file and that file can be activated off a button on the user's screen if that would help. It could be set uo to run the MSRP process and than startup Monarch and do whatever is required to present the users with the screen they need to work on.

           

          I hope this description makes some sense. Just tell me if you need more clarification.

           

          Have fun.

           

           

          Grant