6 Replies Latest reply: May 15, 2014 9:52 AM by Dee Moore RSS

    Capturing multiple lines?

    Neal _

      Hi all,

      I'm new to Monarch and can't quite seem to get the second (and sometimes more) line of a field - the Misc Appl and Miscellaneous GL in the report below.  I have a detail template that captures the Check Amount, Post Date, Invoice #, Disc Appl, Misc Appl, Misc G/L, Cash Appl, and Chrg Back.  Also have an append template for Description, Batch, and Check Num.  If there is nothing in the invoice column but still a Misc Appl and Misc GL I can't get those.  I've tried the options on the advanced field properties but I can't get the next lines in Misc Appl and GL.  In the report below I don't get anything on the -47603.06 line.

       

      Any thoughts?

       

      [font="courier"]Description  Batch  Check Num       Check Amt Pst-Date Invoice#   Cash Appl   Disc Appl  Chrg Back  Misc Appl Miscellaneous G/L

      00000M       018101 ACH              79288.85 01/03/06 MISC         79288.85       0.00             -31685.79 !!00!100!!1350                                                                               

      -47603.06 !!00!100!!1350  

        /font[/quote]Cheers,

       

      Neal

        • Capturing multiple lines?
          Grant Perkins

          Hi Neal and welcome to the forum.

           

          I'm going to guess that both of the amounts (and I suppose there could be many more ...) relate to the fields to the left of the Misc Appl value.

           

          If so your detail template needs to trap the lines for Misc Appl and Miscellaneous G/L giving a 2 field detail record and everything to left (presumably) would be an append for each record. (Or maybe more than one append depending on what else appears in the report in longer transaction records.)

           

          The other option MIGHT be to look at capturing the last 2 columns as a block of text (Multi Line field) and then breaking it out into its component fields but that doesn't look to be quite the right approach for this example.

           

          HTH.

           

           

          Grant

          • Capturing multiple lines?
            joey

            Could you just use the option to copy previous values when blank for all columns other than the rightmost two? Of course this wouldn't work if these fields could have blanks in them normally.

            • Capturing multiple lines?
              Neal _

              Hi Grant thanks for the welcome,

               

              That's correct, the amount(s) in the Misc Appl relate to the Cash Appl column - in the example, the two Misc Appl equal the Cash Appl. The difficulty is that there is not always a Misc Appl amount - so if the detail traps only Misc Appl and Misc GL, it misses all the lines where there is no Misc Appl?

               

              I probably should have included this example originally, but here's the entire first page of the report...

               

              [font="courier"] Description  Batch  Check Num       Check Amt Pst-Date Invoice#   Cash Appl   Disc Appl  Chrg Back  Misc Appl Miscellaneous G/L     

              00000M       018101 ACH              79288.85 01/03/06 MISC         79288.85       0.00             -31685.79 !!00!100!!1350                                                                               

              -47603.06 !!00!100!!1350        

                                                                              -


              -


              -


              -


                                       

                Totals For                                                        79288.85       0.00       0.00  -79288.85                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              539706       018115 33665             9686.46 01/03/06 03081102       319.50                                                        

              CERAMI CONST CO INC                                    05072560      2041.84                                                                               

              01145906       680.00                                                                               

              01144757       382.03                                                                               

              01145909        40.85                                                                               

              01145911      2841.87                                                                               

              01145910      1183.96                                                                               

              01145905       439.86                                                                               

              01145908       193.50                                                                               

              01145907        16.13                                                                               

              05073302       247.24                                                                               

              01146950      1299.68                                                                               

              -


              -


              -


              -


                                       

                Totals For CERAMI CONST CO INC                                     9686.46       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              690544       018115 6828                15.48 01/03/06 01147319        15.48       0.00                                             

              C & C SHENUK CONST                                                                               

              -


              -


              -


              -


                                       

                Totals For C & C SHENUK CONST                                        15.48       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              723290       018115 21543               42.17 01/03/06 07047155        42.17       0.00                                             

              J J ALESSIO COMPANY                                                                               

              -


              -


              -


              -


                                       

                Totals For J J ALESSIO COMPANY                                       42.17       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              478277       018115 102363             135.20 01/03/06 05073966       135.20       0.00                                             

              NOVAK CONSTRUCTION CO                                                                               

              -


              -


              -


              -


                                       

                Totals For NOVAK CONSTRUCTION CO                                    135.20       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              522190       018115 38149             2359.17 01/03/06 05073320      2359.17                                                        

              LANCE CONST SUPPLY                                                                               

              -


              -


              -


              -


                                       

                Totals For LANCE CONST SUPPLY                                      2359.17       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              725551       018115 35668              385.97 01/03/06 07047258       278.55       0.00                                             

              ALESSIO & SONS COMPANY                                 07047257        26.54       0.00                                                                               

              07047666        80.88       0.00                                                                               

              -


              -


              -


              -


                                       

                Totals For ALESSIO & SONS COMPANY                                   385.97       0.00       0.00       0.00                       

                  Comments THERMAL REM 31685.79 DL UNDERGROUND 47603.06                                                                               

              858509       018115 1646               205.41 01/03/06 01146158       205.41                                                        

              DAWES CONST & ASSOC INC                                                                               

              -


              -


              -


              -


                                       

                Totals For DAWES CONST & ASSOC INC                                  205.41       0.00       0.00       0.00                       

                          

              /font[/quote]As you can see, most lines do not have a Misc Appl/Misc GL.  Would it make any sense to run two models - one to capture everything but Misc Appl and one to get Misc Appl then just combine them? 

               

              Cheers,

               

              Neal

              • Capturing multiple lines?
                Grant Perkins

                I had a feeling there might be more to it ...

                 

                OK. Briefly as I am short of time just at the moment;

                 

                You could extract the allocations using a suitable key to then use the extraction as an External Lookup to a process reading the rest of the record but you would need to treat all allocations as a block. (Or perhaps all allocations to a single Cash Appl as I guess each Cash Appl may have multiple allocation lines associated with it as well as there being multiple Cash Appl. entries for each account.

                 

                This assumes that the Cash Appl is the Ultimate focus of the Detail Record.

                 

                If it was the allocation the lookup link would have to be the other way around ...

                 

                Since you have a variable number of possible allocation lines (at least one in effect even if the field positions are empty, but there can be two or more) the only simply way to extract the detail would be to paint the field (or even both fields - amount and GL account) and extract as a multiline block using an advanced "End field on" setting in the field properties.

                 

                If all you need is some sort of memorandum record that would work OK I reckon. It may even be possible to do some slicing and dicing to cut the values out into calculated fields BUT unless you could guarantee that the number of allocations lines could not exceed a know value (preferably small!) that could get messy.

                 

                If you need to be able to take the allocations  to further analysis my amount and account things will get trickier.

                 

                I think the simple thing for that purposes (since any records without allocations would not be very relevant for such analysis?) would be to treat the allocations as, in effect, a separate report and therefore make them the Detail and append any other information required.

                 

                The final idea would be to treat each line as a complete content line (I can already see some problems with that over to the left though ....) and just take everything out field by field even if blank. Then filter unwanted lines and take it from there. Joey's suggestion about filling the field from the previous record could come into play at that point but I can see some problems and it would be a fairly desparate solution.

                 

                What do you need to see as the output? Maybe we can work backwards from there?

                 

                Or maybe someone already has the wheel that we are trying to invent for this format of report?

                 

                Anyone?

                 

                Grant

                • Capturing multiple lines?
                  Neal _

                  Thanks for the suggestions Grant.

                   

                  I ended up treating the allocations as a separate report - captured the detail of those separately from the detail of the lines without allocations. This worked well for easily seeing where the misc allocations went...

                   

                  Cheers,

                   

                  Neal

                  • Capturing multiple lines?
                    Dee Moore

                    Hi Neal,

                     

                    It's true that there are many different ways to extract data using Monarch.

                     

                    I would advise getting familiar with the basics. Know what the Traps can do for you and make use of the Templates available.

                     

                    I created this Fixed Width Text Export:

                     

                    [font="courier"]COMPANY                    DESC    BATCH  CHECKNO     CKAMT PSTDATE    INVNO       CASH APPL   DISCAPPL CHRGBK       MISCAPPL MISCGL         COMMENTS                                         

                                               00000M  018101 ACH      79288.85 1/3/2006   MISC         79288.85       0.00             -31685.79 !!00!100!!1350 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                                               00000M  018101 ACH                                                                       -47603.06 !!00!100!!1350 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665     9686.46 1/3/2006   03081102       319.50                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        05072560      2041.84                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145906       680.00                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01144757       382.03                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145909        40.85                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145911      2841.87                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145910      1183.96                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145905       439.86                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145908       193.50                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01145907        16.13                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        05073302       247.24                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    CERAMI CONST CO INC        539706  018115 33665                        01146950      1299.68                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    C & C SHENUK CONST         690544  018115 6828        15.48 1/3/2006   01147319        15.48       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    J J ALESSIO COMPANY        723290  018115 21543       42.17 1/3/2006   07047155        42.17       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    NOVAK CONSTRUCTION CO      478277  018115 102363     135.20 1/3/2006   05073966       135.20       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    LANCE CONST SUPPLY         522190  018115 38149     2359.17 1/3/2006   05073320      2359.17                                                 THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    ALESSIO & SONS COMPANY     725551  018115 35668      385.97 1/3/2006   07047258       278.55       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    ALESSIO & SONS COMPANY     725551  018115 35668                        07047257        26.54       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    ALESSIO & SONS COMPANY     725551  018115 35668                        07047666        80.88       0.00                                      THERMAL REM 31685.79 DL UNDERGROUND 47603.06     

                    DAWES CONST & ASSOC INC    858509  018115 1646       205.41 1/3/2006   01146158       205.41                                                                               

                    /font[/quote]By building only a Detail, 1 Append, and 1 Footer Template. No Calculated Fields and 1 Filter to omit the Totals from the Table display.

                    The Sub-Totals can be displayed in a Summary later if you like.

                     

                    Using the Numeric Or (|) trap can be very useful for a file like this. Placed above one of the Cash Appl and Misc Appl values will return all lines that hold numeric values in either position.

                     

                    I extracted the Check Amt, Pst-Date, Invoice#, Cash Appl, Disc Appl, Chrg Back, Misc Appl, and Miscellaneous G/L in the Detail. You have the choice to Fill from the Previous Record in the Properties, if you like. I also extracted the Description and called it FilterOn. This field can be used to Filter out the "Totals" and can be hidden.

                     

                    Create an Append using the Description as the  Sample line, Trapping the slashes above the Date works well. Extract the Description, Batch, and Check Num. These values will repeat. If you do not want them to repeat, they may be extracted in the Detail. However, you will need to do a little clean up using Calculated Fields because the Detail is picking up additional lines of data, not just the Description lines.

                     

                    Lastly, create a Footer using a two line Sample of the Totals and Comments. Trap "totals" and extract the Company Name and Comments. These will repeat attaching to the Detail lines above.

                     

                    I don't put my two cents in very often, but this seemed like a pretty good spot to do a little advertising....

                     

                    If you have a tough Report and a time constraint, check out our  [url="http://www.datawatch.com/services/model-building.asp"]Model Building Service[/url]

                     

                    and....

                     

                    To learn more Advanced Techniques consider a  [url="http://www.datawatch.com/services/training.asp"]Training Class[/url]

                     

                    I hope this is helpful.

                     

                    Dee Moore

                    Datawatch Tech Support