3 Replies Latest reply: May 15, 2014 10:06 AM by Grant Perkins RSS

    Trapping a variable row ?

    Guy Chowning

      A sample page from the report is shown below. The first 2 rows of information is consistent through out the report and I have no trouble trapping this information. In the sample report below the first row begins with 11/11/09, the second row begins with MACHLO.

       

      The trouble arises when there are varying numbers of accrued freight entries, there may be as many as 9.

       

      In the sample provided below an example of this which has 2 additional accrued freight entries is shown with the row beginning with 11/09/09 and the second row beginning with TABOCN. (SHIPMENT 503493)

       

      I offer my apology in advance because I think the answer lays somewhere in the "guru trap" pioneered by Grant Perkins, BUT try as I might I have had no success with trapping these varying rows of info.

       

      If someone out there would be so kind as too provide me with some guidance it will be greatly appreciated.

       

       

      SHIPDATE   VEHICLE ID  SHIPMENT    Y INVENTRY P.CONTRACT LN FOB SHIPPER       PURCHASE     ACCRUED     ACCRUED     ACCRUED   ACCRUAL   

      TICKET NO  FRT CARRIER SHIPMENT BR D   CODE   S.CONTRACT LN FOB CUSTOMER      - COST -   -FREIGHT-   -INVCOST-   - SALES -  -CODES-                                                                               

      11/11/09  INO  96029   504426  NP F 100      RS09574FNP 01  M1                    .00                     .00         .00             

      MACHLO    INGR90 01    504426  HQ            RS09574FNP 01  S1 GALGHR            0 LBS    8500.00           0            0            

      11/18/09  INO  71056   508265  NP F 100      RS09574FNP 01  M1                    .00                     .00   336732.15             

      MADTYK    INGR90 01    508265  HQ            RS09574FNP 01  S1 GALGHR            0 LBS    8500.00           0      3017120            

                                               G/L ACCOUNT     SRC D/C                                                                       

           PRODUCT CLASS TOTALS F100 NP        420-024-5701.00 f/a D    17000.00         .00    17000.00         .00   336732.15             

                                               400-002-1040.00 f/a C    17000.00           0    LBS                0     3017120             

                                               400-001-2020.00 s/a D   336732.15         .00 TNS                 .00     1346.93             

                                               420-024-5111.00 s/a C   336732.15                                                                               

      11/06/09  TKNP TABDAD  502150  NP F 110      NP-028837Z 01  M1                    .00                     .00    24847.20             

      TABDAD    CLAR70 01    502150  HQ            RMR028837Z 01  S1 ALLCHM            0 LBS     726.00           0        42840            

      11/10/09  TKNP MABWPK  503454  NP F 110      NP-9110632 01  M3                    .00                     .00    23446.08             

      MABWPK                 503454  HQ            RMR9110632 01  S3 AUDHEM            0 LBS        .00           0        41868            

      11/09/09  TKNP TABOCN  503493  NP F 110      NP-028870Z 01  M1                    .00                     .00    26232.80             

      TABOCN    STMSHP 02    503493  HQ            RMR028870Z 01  S1 CHOCHM            0 LBS    2100.00           0        43360            

                 CCIC50 03    503493                                                              150.00                                     

                 BOHA50 04    503493                                                              150.00                                                                               

      11/10/09  TKNP TABWMF  503693  NP F 110      NP-028870Z 01  M1                    .00                     .00    26196.50             

      TABWMF    STMSHP 02    503693  HQ            RMR028870Z 01  S1 CHOCHM            0 LBS    2100.00           0        43300            

                 CCIC50 03    503693                                                              150.00                                     

                 BOHA50 04    503693                                                              150.00                                                                               

      11/13/09  TKNP TACUPL  505911  NP F 110      NP-0029562 01  M1                    .00                     .00    24194.88             

      TACUPL    CLAR70 01    505911  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        39024            

      11/17/09  TKNP TADNYV  507619  NP F 110      NP-0029562 01  M1                    .00                     .00    25121.16             

      TADNYV    CLAR70 01    507619  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        40518            

      11/20/09  TKNP TAEDHC  509663  NP F 110      NP-0029562 01  M1                    .00                     .00    24920.28             

      TAEDHC    CLAR70 01    509663  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        40194            

      11/23/09  TKNP TAENDR  510558  NP F 110      NP-0029562 01  M1                    .00                     .00    24161.40             

      TAENDR    CLAR70 01    510558  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        38970            

      11/23/09  TKNP TAESLN  510930  NP F 110      NP-0029562 01  M1                    .00                     .00    25701.48             

      TAESLN    CLAR70 01    510930  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        41454            

      11/23/09  TKNP TAESWP  510975  NP F 110      NP-0029562 01  M1                    .00                     .00    25065.36             

      TAESWP    CLAR70 01    510975  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        40428            

      11/24/09  TKNP TAEUZY  511325  NP F 110      NP-0029562 01  M1                    .00                     .00    25489.44             

      TAEUZY    CLAR70 01    511325  HQ            RMR0029562 01  S1 OMNISOURC         0 LBS     565.00           0        41112            

      11/25/09  TKNP TAFFXG  512808  NP F 110      NP-9110632 01  M3                    .00                     .00    23284.80             

       

      /CODE

        • Trapping a variable row ?
          Data Kruncher

          Hi Guy,

           

          Assuming that you've got Monarch Pro, you should be able to resolve this challenge fairly quickly.

           

          First, create a model and a project that captures and exports the first two rows (your MACHLO record). Build a project export to send the captured data to a file (maybe an Excel sheet?), and run the export. Save and close the model and project files.

           

          Now re-open the report and build a new model to pick up the freight data lines. Include the Ticket No in this capture. In the Table window, build an external lookup to connect this data to that which you exported using the previous model. Use TicketNo as the common field, and elect to import all of the other fields in the external file. Save this model.

           

          In and of itself, that ought to do it, but you might want to build a short batch file to automate this two-step process if it will be used regularly.

           

          HTH,

          Kruncher

            • Trapping a variable row ?
              Joe Berry

              I was able to build a model using the 2nd row as the detail and the first row as the append.  Then define the TABOCN field to copy the value from the previous record. 

               

              HTH,

              Joe

                • Trapping a variable row ?
                  Grant Perkins

                  Guy,

                   

                  It's kind of 'guru' like, maybe, but how you approach it depends more on what youwant to get out of the report and how that information is to be grouped.

                   

                  Kruncher and Joe offer 2 good approaches. A third approach might be to make the fields on the second line (or at least the ones that may sometimes have additional lines) into multi-line fields to capture them as text blocks and then split them out aftger capture. This would work well if the number of possible additional lines had a known, not too large, limit. Less well if the number of extra lines could be anywhere between one and one hundred!

                   

                  The 'guru' concept works especially well if the random additional fields (when they exist) have unique names[/B] to tag them with. But those names must be to the left of the data strings you need to capture. In this case, as far as I can tell, you have nothing to fulfil that role. (Well, you have a string of spaces which would work for one field but would leave you stranded for the rest as far as I understand the report.)

                   

                  On the other hand if the only useful value form the other lines was the Accrued -Freight-   column then the "FRT CARRIER " column values might be the unique 'tag' that would enable the 'guru' concept to function as previously describen in the forum. That said you could still have problems with presenting the results if there are a lot of possible "FRT CARRIER " codes.

                   

                  HTH.

                   

                   

                  Grant