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

[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

[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

[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 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