10 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Trap Problem

    gar _

      Hi All,

      I'm a long time viewer, first time writer.

      I'm having a problem trapping all the lines required by our accounting staff.  I can trap the detail lines easily, but occassionally they are preceeded by a comment which can be identified by the ** in the left column.  this line only applies to following line.

       

      Any ideas would be appreciated.

       

             CHK#  ISSUED         AMOUNT        PAY-TO                TLR  STATUS   PAID AMT      DATE  SOURCE             MESSAGE

       

            123456 11-22-95        71.00   AAAAAAAAAAAAAAAAAAAAAAAA     0  STOP         0.00             1111111       148

       

      •    234567 12-12-95     1,509.19   BBBBBBBBBBBBBBBBBBBB         0  STOP         0.00             2222222       149

       

      •    345678 01-18-96    26,000.00   CCCCCCCCCCCCCCCCCCCCCCC    431  STOP         0.00             3333333

       

      •    456789 01-23-96         1.40   DDDDDDDDDDDDDDDDD          718  STOP         0.00             4444444

       

      •    135790 12-05-95       200.00   EEEEEEEEEEEEEEEEEEE        220  STOP         0.00             5555555

       

      •    246801 10-20-95       181.72   FFFFFFFFFFFFFFFFFFFFF      128  STOP         0.00             6666666

       

      •    468024 11-20-95         0.29   GGGGGGGGGGGGGGGGGGG        157  STOP         0.00             9999999

       

      •    680246 10-03-95       357.00   HHHHHHHHHHHHHHHHHHHHHH     106  STOP         0.00             7777777       171

       

        • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

       

      •    987654 10-25-95        15.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

       

      •    876543 11-17-95         0.99   JJJJJJJJJJJJJ             6001  STOP         0.00             8888888         5

       

      •    765432 12-07-95        11.68   KKKKKKKKKKKKKKKKKKKK       112  STOP         0.00             9999999[/CODE]

       

        • Trap Problem
          Data Kruncher

          Hi gar. You certainly came up with a good one for your first post!

           

          Fought with it for a bit, then came up with the following solution:

           

          1) As you say, the detail line is pretty standard stuff, with a one line detail. I trapped on the first decimal point.

           

          2) The append... I created a two line template with the comment in the first line. Set the trap to be the same decimal point. Set the trap line to be 2 of 2. Now paint your comment field, and on the advanced field settings, set the Preceding string: ** in current line (two asterisks).

           

          That'll do it. The table shows the comment on the $15.00 amount record, and nowhere else.

           

          HTH,

          Kruncher

            • Trap Problem
              gar _

              Kruncher,

              thanks for the quick reply, I've been pulled off to another problem but hope to be back to this shortly.  When i do, I'll let you know how it goes.

              Thanks again.

                • Trap Problem
                  gar _

                  kruncher,

                  I finally tried your solution and on the small sample it worked fine.  After increasing the size of the sample I began to see a problem where Monarch appeared to randomly append the comment to lines where no comment should be.

                   

                  To increase the size of the sample I simple duplicated some of the line in the sample.

                   

                  Now it may be my implementation of what you said or Friday the 13th, I'm not sure but if you could try with the increase sample size that would give me a better idea.

                   

                         CHK#  ISSUED         AMOUNT        PAY-TO                TLR  STATUS   PAID AMT      DATE  SOURCE             MESSAGE

                        123456 11-22-95        71.00   AAAAAAAAAAAAAAAAAAAAAAAA     0  STOP         0.00             1111111       148

                   

                  •    234567 12-12-95     1,509.19   BBBBBBBBBBBBBBBBBBBB         0  STOP         0.00             2222222       149

                   

                  •    345678 01-18-96    26,000.00   CCCCCCCCCCCCCCCCCCCCCCC    431  STOP         0.00             3333333

                   

                  •    456789 01-23-96         1.40   DDDDDDDDDDDDDDDDD          718  STOP         0.00             4444444

                   

                  •    135790 12-05-95       200.00   EEEEEEEEEEEEEEEEEEE        220  STOP         0.00             5555555

                   

                  •    246801 10-20-95       181.72   FFFFFFFFFFFFFFFFFFFFF      128  STOP         0.00             6666666

                   

                  •    468024 11-20-95         0.29   GGGGGGGGGGGGGGGGGGG        157  STOP         0.00             9999999

                   

                  •    680246 10-03-95       357.00   HHHHHHHHHHHHHHHHHHHHHH     106  STOP         0.00             7777777       171

                   

                    • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

                   

                  •    987654 10-25-95        15.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

                   

                  •    876543 11-17-95         0.99   JJJJJJJJJJJJJ             6001  STOP         0.00             8888888         5

                   

                  •    765432 12-07-95        11.68   KKKKKKKKKKKKKKKKKKKK       112  STOP         0.00             9999999

                   

                  •    246801 10-20-95       181.72   FFFFFFFFFFFFFFFFFFFFF      128  STOP         0.00             6666666

                   

                  •    468024 11-20-95         0.29   GGGGGGGGGGGGGGGGGGG        157  STOP         0.00             9999999

                   

                  •    680246 10-03-95       357.00   HHHHHHHHHHHHHHHHHHHHHH     106  STOP         0.00             7777777       171

                   

                    • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

                   

                  •    987654 10-25-95        15.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

                   

                  •    876543 11-17-95         1.99   JJJJJJJJJJJJJ             6001  STOP         0.00             8888888         5

                   

                  •    765432 12-07-95        12.68   KKKKKKKKKKKKKKKKKKKK       112  STOP         0.00             9999999

                   

                    • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

                   

                  •    987654 10-25-95        16.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

                   

                    • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

                   

                  •    987654 10-25-95        17.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

                   

                    • EXPENSE CHECK ERROR: CLEAR AMT NOT EQUAL TO ISSUE AMT **

                   

                  •    987654 10-25-95        18.00   IIIIIIIIIIIIIIIIIIIIIIII   125  STOP         0.00             10-25-95  VCHR#    355

                   

                  /CODE

                   

                  Thanks again..

                    • Trap Problem
                      Data Kruncher

                      Hmmm... now isn't that just a particular breed of "special"?? :eek:

                       

                      I'll give it another go...

                        • Trap Problem
                          Data Kruncher

                          OK gar, got it. Good thing that you've got the Pro version of Monarch. Now let's see if I can explain the solution I developed...

                           

                          I pretty much gave up on conventional modeling techniques within a few minute of attempting your revised sample. That led to a three part solution. I had to change your sample a little to ensure that there are no duplicates of the check number in the report, as I'm sure that's how the real model is anyway. If there really are duplicates, then we'll fight with that somehow, but I really doubt it.

                           

                          1) Build the first model, capturing the comments as the detail template. Append the remaining field values with a 2 line template, with a double asterisk in the trap line to match the comment. Build a project export, with the file option set to overwrite, creating an Excel file (assuming that you don't have too many records in the real data). Save the model and the project.

                           

                          2) Close your existing model, and start again. Build a second model and project. Capture all of your details, ignoring the comments altogether. Name all of the fields properly, making Source a character field. Since you don't have a Comments field in this model, create one with a calculated character field, with the expression set as "" (an empty string). Build a project export, sending the output to append to the Excel file you just created, appending to the same table name. Be sure that the data structure and field types is identical to that of the first model. Save the model and the project.

                           

                          3) Close All, and Open Database. Select your fresh new Excel file. Now you'll have some duplication initially, so create a new filter. Ignore the filter expression and go right to the Advanced tab. Select rows according to their uniqueness, and check both the Unique rows and Duplicated rows. Set one row only. Check the CheckNumber in the Specify keys. OK the filter. You might want to create a sort based on the check number. Save the model.

                           

                          That's it, you're done. I think I've described it all.

                           

                          Finally, with any multiple pass solution such as this, you may want to put together a [URL="http://********************/tips/monarch-batch-file-generator"]batch file[/URL] to handle all of that for you.

                           

                          HTH,

                          Kruncher

                            • Trap Problem
                              gar _

                              Kruncher,

                              The users of the report were very happy to see the data in the excel format, just like they wanted.  During the review I did notice a problem.  In a few cases where the ** line is separated from the following line by a page break all the field values become null.

                               

                              I haven't come up with a method to solve that situation.  Any suggestions.

                               

                              The users also  want to trap some of the values from the headings, but I don't see that as been a problem.

                               

                              the headings look similar to this:

                               

                              -


                              AAAAAAAAAAAAAAAAAAAAAAAAAA                         AAAAAAAAAAAAAAAAAAAAA                                                   06-02-08

                              BBBB     05-31-08 22:05:05                  BBBBBBBBBBBBBBBBBBBB          9999999999                                       PAGE   1

                              -


                              CHECK STATUS OPTION ALL   CHECKS                              CHECK DATE RANGE:10-01-95   TO 05-31-08

                                     CHK#  ISSUED         AMOUNT        PAY-TO                TLR  STATUS   PAID AMT      DATE  SOURCE             MESSAGE

                              /CODE

                                • Trap Problem
                                  Data Kruncher

                                  Hi gar,

                                   

                                  Great, we've made some progress! Thanks for the update.

                                   

                                  Usually those "lost" append problems go away when you define a page header template, even if you don't need to capture any values from within the header (though I haven't tried that yet on your sample). But since there's now a need to do that anyway...

                                   

                                  When you create the page header template, don't use the first page as the sample; go a few pages into the document if you can.

                                   

                                  Where does that get you?

                                    • Trap Problem
                                      gar _

                                      Unfortunately defining the header didn't solve the problem...:(

                                       

                                      there doesn't appear to be anything in the following row that would positively identify it, other it shouldn't be blank, and the source column I believe has a date value in the format mm-dd-yy.  but the source column can contain many other values, so its defined as a character column.

                                        • Trap Problem
                                          gar _

                                          Thanks Kruncher for your help.  I solved the problem by trapping the header and the last two footer lines from the previous page.  the footer lines are always blank.  The only problem that causes is the first page header which doesn't have two blank lines before it.  So I need to edit the file, and append two blank lines before the first header.

                                           

                                          If you know of a better way to solve this let me know.

                                            • Trap Problem
                                              Data Kruncher

                                              Excellent! :cool:

                                               

                                              I may have avoided a problem with my model just by the coincidental manner in which I built up the sample file with some extra lines. That would largely explain why it was that my model didn't show the same problems that you described.

                                               

                                              Glad to have helped.