rdbms to flat file report

[StanKorn]

I am trying to create a report that mimics a service bureaus data input form. I am using VFP V7 DBF tables FRMAST and FRTRAN. the tables are related via INVNO field.

The FRMAST file looks like this:
INVNO invoice number
CUSTNO customer number
TRANSDATE transaction date
TOTAMT total amount
REVCODE revenue code

The FRTRAN file is like this:
INVNO invoice number
PRODUCT product
REVCODE revenue code
TOTAMT total amount
TRASDATE transaction date

The FRMAST is the parent and the FRTRAN is the child.
The service bureau wants a single record per invoice with the line items attached at the end of the record. My problem is I have no way of placing the line items next to the invoice, I have them beneath the invoice. The problem is when the bureau reads the record it rejects the batch becoz of duplicate invoices.
I thought I left flat files behind but alas!
Can anyone give me a hand. As usual I am working against the clock.
thanx,
stan

 

[synapsevampire]

Please share what the file is supposed to look like, and your version of Crystal (older versions are limited).

-k

 

[StanKorn]

Using CR 8.5

File outputis 30 fields of fixed definition and then 6 fields for each line item.

fields are:

Invoice #
Invoice # ext
Invoice Date
Invoice Type
Shipper Name
Shipper Address
Shipper city

and so on till field 31 then the fun begins:

# Pieces
Hazerdous Material
Description
Weight
Rate
Charges

I have to segregate the line item fields from the invoice header data table.
I have the report as the following for 1 invoice in their format:
"73422"," ",7/22/03," ",""," ","BRONX","NY"," ",""," ","SHELDON SPRINGS","VT"," "," ","ROCKSHELVT","PO BOX 465 , 21 MILL ST","SHELDON SPRINGS","VT","05485",""," ","COL",651.31," "," "," "," "," "," ",0.00,"","",0.00,583.00,583.00
"73422"," ",7/22/03," ",""," ","BRONX","NY"," ",""," ","SHELDON SPRINGS","VT"," "," ","ROCKSHELVT","PO BOX 465 , 21 MILL ST","SHELDON SPRINGS","VT","05485",""," ","COL",651.31," "," "," "," "," "," ",0.00,"X","",0.00,27.50,27.50
"73422"," ",7/22/03," ",""," ","BRONX","NY"," ",""," ","SHELDON SPRINGS","VT"," "," ","ROCKSHELVT","PO BOX 465 , 21 MILL ST","SHELDON SPRINGS","VT","05485",""," ","COL",651.31," "," "," "," "," "," ",0.00,"X","",0.00,0.00,40.81

What I am thinking is to set up a formula that will create an id for each line item per invoice so thwe above would have one invoice # and three sub numbers. Then save that as a DBF and run another report on the results bu doing it all on one line so the previous report example would be a single record long but wider by the required 12 fields (the last 6 fields of each record).
Hope this helps,
thanx,
stan

 

[StanKorn]

Vamp,
here is the ASCII def that the bureau sent to me:

ASCII-delimited Invoice File - Transportation
Field # Description Type Max. Length Req/Opt Comments
A 1 Invoice Number Numeric 12 Required
B 2 Invoice Number Ext. Alpha-Numeric 3 Optional
C 3 Invoice Date Numeric 6 Required I.e. MMDDYY
D 4 Invoice/Schedule Type Alpha 1 Required Space or 1 character alpha (per list)
E 5 Shipper Name Alpha-Numeric 30 Required
F 6 Shipper Address Alpha-Numeric 34 Optional
G 7 Shipper City Alpha-Numeric 20 Required Unless Shipper is VARIOUS
H 8 Shipper State Alpha-Numeric 2 Required Unless Shipper is VARIOUS
I 9 Shipper Zip Code Alpha-Numeric 9 Optional 5, 6 or 9 digits accepted, no space or dash
J 10 Consignee Name Alpha-Numeric 30 Required
K 11 Consignee Address Alpha-Numeric 34 Optional
L 12 Consignee City Alpha-Numeric 20 Required Unless Consignee is VARIOUS
M 13 Consignee State Alpha-Numeric 2 Required Unless Consignee is VARIOUS
N 14 Consignee Zip Code Alpha-Numeric 9 Optional 5, 6 or 9 digits accepted, no space or dash
O 15 Bill-To ID Code Alpha-Numeric 17 Optional ID assigned in your system, REQUIRED if available
P 16 Bill-To Name Alpha-Numeric 30 Required
Q 17 Bill-To Address Alpha-Numeric 34 Required
R 18 Bill-To City Alpha-Numeric 20 Required
S 19 Bill-To State Alpha-Numeric 2 Required
T 20 Bill-To Zip Code Alpha-Numeric 9 Required 5, 6 or 9 digits accepted, no space or dash
U 21 B/L or SID Number Alpha-Numeric 12 Required
V 22 Purchase Order Number Alpha-Numeric 15 Optional PO# issued by the Shipper
W 23 PPD, COL, OTH Alpha-Numeric 3 Required Prepaid, Collect, Other
X 24 Total Amount Due Numeric 11 Required No punctuation EXCEPT decimal
Y 25 Pick Up Date Numeric 6 Optional I.e. MMDDYY
Z 26 Shipped Date Numeric 6 Optional I.e. MMDDYY
AA 27 Equipment Number Alpha-Numeric 9 Optional
AB 28 Driver/Carrier Name Alpha-Numeric 30 Optional
AC 29 Special Inst. Line 1 Alpha-Numeric 30 Optional
AD 30 Special Inst. Line 2 Alpha-Numeric 30 Optional
AE 31 # Pieces Numeric 10 Required A minimum of 1 line-item is required.
AF 32 Hazardous Material X if Hazardous 1 Required Fields 31-36 are repeated until the
AG 33 Description Alpha-Numeric 50 Required invoice is completed.
AH 34 Weight Numeric 8 Required
AI 35 Rate Numeric 11 Required
AJ 36 Charges Numeric 10 Required No punctuation EXCEPT decimal

This is an ASCII-delimited file. Fields are separated by commas (,), alpha-numeric fields
are surrounded by double quotes (&quot and records are separated by a Carriage Return and New
Line character.

The last record in the file needs to be a control record denoting the total number of invoices
in the file, the total dollar amount of those invoices and the invoice (schedule) type. The
schedule type is generally a space, denoting ‘normal’.

They tell me that all fields must be present but data is optional. I also have data dictionary for my source data if that will help.
Thanks,
stan

 

[synapsevampire]

OK, it's a comma delimited file.

I wouldn't even use Crystal, I'd do this all from within VFP as it might get ugly from Crystal.

At least create a query within VFP to construct the data to match the requirements better.

I'm still at a loss for wher "placing the line items next to the invoice" means, your example data doesn't appear to demonstrate any line items...

Perhaps a very simple model of how the data looks in the tables, and what that data looks like as output would clarify for me.

-k

 

[StanKorn]

These dbf files are too cumbersome. I'm going to post the dictionary of the two tables fields and descriptions but I'm not sure this board can handle the size. You frightened me with your last post as I have very little practical experience with VFP although I must learn it quickly. I appreciate your assistance.

well here goes:
FRDATA\FRMAST.DBF
Current/history Invoices
Invoice # INVNO
Shipment Number LOADNUMBER
Customer # CUSTNO
Customer Name CUSTNAME
Transaction Date TRANSDATE
Tractor TRACTOR
Trailer TRAILER
Shipper SHIPPER
Sh Name SHNAME
Origin City OCITY
Origin State OST
Origin Zip Code OZIP
Consignee CONSIGNEE
Co Name CONAME
Co Address 1 COADDR1
Co Address 2 COADDR2
Destination City DCITY
Destination State DST
Destination Zip Code DZIP
Billto BILLTO
B/L No BLNO
Miles MILES
Weight WEIGHT
Tot Weight TOTWGT
Truckload/LTL TL_LTL
Pmt Code PMTCODE
So SO
Fr FR
Total Charges TOTCHGS
Pmts PMTS
Adjustment ADJ
Balance Due BALDUE
Prncd PRNCD
Pd Date PDDATE
Checkcall No CKNO
Status STATUS
Misc MISC
E_n E_N
Rt Code RTCODE
Pay Code PAYCODE
Quote QUOTE
Transstat TRANSSTAT
Signature SIGNATURE
Driver 1 DRIV1
Driver 2 DRIV2
Ltr Code LTRCODE
As AS
T Type TTYPE
Min MIN
Driver 1 DRIVER1
Driver2 DRIVER2
Manifest MANIFEST
Rate RATE
Nopieces NOPIECES
Drops DROPS
Product PRODUCT
Oldconsignee OLDCONS
Delivery Date DELDATE
Load Date LOADDATE
Billed BILLED
Inbound/Outbound IB_OB
Rev Code REVCODE
Rfile RFILE
Hhg HHG
Slsmn SLSMN
Amount DRAMT
Trip TRIP
Stmtno STMTNO
Stmt Date STMTDATE
Cmsncustomer CMSNCUST
Custpct CUSTPCT
Customer Amount CUSTAMT
Slsmpct SLSMPCT
Slsm Amount SLSMAMT
Over OVER
Dspt DSPT
Custpdref CUSTPDREF
Custpddt CUSTPDDT
Slsmpdref SLSMPDREF
Slsmpddt SLSMPDDT
Ref REF
Seal1 SEAL1
Seal2 SEAL2
Shiploc SHIPLOC
Consignee Loc CONSLOC
Hazardous HAZARDOUS
Review REVIEW
AR Type ARTYPE
COD COD
Terminal TERMINAL
Invoice Type INVTYPE
Stseqno STSEQNO
Booking Customer # BOOKCUST
Checkcall Date CKDATE
Checkcall Time CKTIME
Pu Date PUDATE
Pu Time PUTIME
Fact Code FACTCODE
Factor FACTOR
Poid POID
Invoice Review
Invoice # INVNO
Shipment Number LOADNUMBER
Customer # CUSTNO
Customer Name CUSTNAME
Transaction Date TRANSDATE
Tractor TRACTOR
Trailer TRAILER
Shipper SHIPPER
Sh Name SHNAME
Origin City OCITY
Origin State OST
Origin Zip Code OZIP
Consignee CONSIGNEE
Co Name CONAME
Co Address 1 COADDR1
Co Address 2 COADDR2
Destination City DCITY
Destination State DST
Destination Zip Code DZIP
Billto BILLTO
B/L No BLNO
Miles MILES
Weight WEIGHT
Tot Weight TOTWGT
Truckload/LTL TL_LTL
Pmt Code PMTCODE
So SO
Fr FR
Total Charges TOTCHGS
Pmts PMTS
Adjustment ADJ
Balance Due BALDUE
Prncd PRNCD
Pd Date PDDATE
Checkcall No CKNO
Status STATUS
Misc MISC
E_n E_N
Rt Code RTCODE
Pay Code PAYCODE
Quote QUOTE
Transstat TRANSSTAT
Signature SIGNATURE
Driver 1 DRIV1
Driver 2 DRIV2
Ltr Code LTRCODE
As AS
T Type TTYPE
Min MIN
Driver 1 DRIVER1
Driver 2 DRIVER2
Manifest MANIFEST
Rate RATE
Nopieces NOPIECES
Drops DROPS
Product PRODUCT
Oldconsignee OLDCONS
Delivery Date DELDATE
Load Date LOADDATE
Billed BILLED
Inbound/Outbound IB_OB
Rev Code REVCODE
Rfile RFILE
Hhg HHG
Slsmn SLSMN
Amount DRAMT
Trip TRIP
Stmtno STMTNO
Stmt Date STMTDATE
Cmsncustomer CMSNCUST
Custpct CUSTPCT
Customer Amount CUSTAMT
Slsmpct SLSMPCT
Slsm Amount SLSMAMT
Over OVER
Dspt DSPT
Custpdref CUSTPDREF
Custpddt CUSTPDDT
Slsmpdref SLSMPDREF
Slsmpddt SLSMPDDT
Ref REF
Seal1 SEAL1
Seal2 SEAL2
Shiploc SHIPLOC
Consignee Loc CONSLOC
Hazardous HAZARDOUS
Review REVIEW
AR Type ARTYPE
COD COD
Terminal TERMINAL
Invoice Type INVTYPE
Stseqno STSEQNO
Booking Customer # BOOKCUST
Checkcall Date CKDATE
Checkcall Time CKTIME
Pu Date PUDATE
Pu Time PUTIME
Fact Code FACTCODE
Factor FACTOR
Poid POID
Invoices
Invoice # INVNO
Shipment Number LOADNUMBER
Customer # CUSTNO
Customer Name CUSTNAME
Transaction Date TRANSDATE
Tractor TRACTOR
Trailer TRAILER
Shipper SHIPPER
Sh Name SHNAME
Origin City OCITY
Origin State OST
Origin Zip Code OZIP
Consignee CONSIGNEE
Co Name CONAME
Co Address 1 COADDR1
Co Address 2 COADDR2
Destination City DCITY
Destination State DST
Destination Zip Code DZIP
Billto BILLTO
B/L No BLNO
Miles MILES
Weight WEIGHT
Tot Weight TOTWGT
Truckload/LTL TL_LTL
Pmt Code PMTCODE
So SO
Fr FR
Totchgs TOTCHGS
Pmts PMTS
Adjustment ADJ
Balance Due BALDUE
Prncd PRNCD
Pd Date PDDATE
Checkcall No CKNO
Status STATUS
Misc MISC
E_n E_N
Rt Code RTCODE
Pay Code PAYCODE
Quote QUOTE
Transstat TRANSSTAT
Signature SIGNATURE
Driver 1 DRIV1
Driver 2 DRIV2
Ltr Code LTRCODE
As AS
T Type TTYPE
Min MIN
Driver 1 DRIVER1
Driver 2 DRIVER2
Manifest MANIFEST
Rate RATE
Nopieces NOPIECES
Drops DROPS
Product PRODUCT
Oldconsignee OLDCONS
Delivery Date DELDATE
Load Date LOADDATE
Billed BILLED
Inbound/Outbound IB_OB
Rev Code REVCODE
Rfile RFILE
Hhg HHG
Slsmn SLSMN
Amount DRAMT
Trip TRIP
Stmtno STMTNO
Stmt Date STMTDATE
Cmsncustomer CMSNCUST
Custpct CUSTPCT
Customer Amount CUSTAMT
Slsmpct SLSMPCT
Slsm Amount SLSMAMT
Over OVER
Dspt DSPT
Custpdref CUSTPDREF
Custpddt CUSTPDDT
Slsmpdref SLSMPDREF
Slsmpddt SLSMPDDT
Ref REF
Seal1 SEAL1
Seal2 SEAL2
Shiploc SHIPLOC
Consignee Loc CONSLOC
Hazardous HAZARDOUS
Review REVIEW
AR Type ARTYPE
COD COD
Terminal TERMINAL
Invoice Type INVTYPE
Stseqno STSEQNO
Booking Customer # BOOKCUST
Checkcall Date CKDATE
Checkcall Time CKTIME
Pu Date PUDATE
Pu Time PUTIME
Fact Code FACTCODE
Factor FACTOR
Poid POID

FRDATA\FRTRAN.DBF
Invoice Line Item Details
Invoice # INVNO
Period PERIOD
Customer # CUSTNO
Customer Name CUSTNAME
Transaction Date TRANSDATE
Nopieces NOPIECES
Description DESC
Code CODE
Weight WEIGHT
As Weight ASWGT
Miles MILES
Chgs CHGS
Rchgs RCHGS
Aschgs ASCHGS
Rate RATE
R Rate RRATE
As Rate ASRATE
Comm COMM
% PCT
Tot Amount TOTAMT
Ltr Code LTRCODE
Destination DEST
Class CLASS
Rbr RBR
Rev Code REVCODE
Units UNITS
Product PRODUCT
Rfile RFILE
Hazardous HAZARDOUS
AR Type ARTYPE
Ref REF
Pay Code PAYCODE
Poid POID
Stopcustomer STOPCUST

thanx,
stan