Very Computer

Why don't you create an index on the invoice number then
run the report perhaps limiting it with a FOR clause.

Larry

I need to recreate old invoices from time to time and my M.O. has been
to write it to the screen [set alternate on] and then formatting the
text in WordPerfect and then printing it. [Yes, I am a rank amateur
programmer, but it works.]

Is there a way to program the FoxPro report form to grab all the
entries in my lineitem database that match a particular invoice number
and loop on that until it hits the next number?

Or, perhaps I should post this message to a WordPerfect group and ask
how to create a table from a DBF file and get the macro to keep adding
to the table for a particular invoice number.
Any advice greatly appreciated.
Please reply to the NG.

databases used:
maillist.dbf  [customer with link to invoice by custno]
invoice.dbf [terms/date/etc, with link to lineitem by invoice no.]
lineitem.dbf

Thanks,
Marcel  

[ Courtesy cc'ed through e-mail to the quoted author ]

Quote:>I need to recreate old invoices from time to time and my M.O. has been
>to write it to the screen [set alternate on] and then formatting the
>text in WordPerfect and then printing it. [Yes, I am a rank amateur
>programmer, but it works.]

>Is there a way to program the FoxPro report form to grab all the
>entries in my lineitem database that match a particular invoice number
>and loop on that until it hits the next number?

>Or, perhaps I should post this message to a WordPerfect group and ask
>how to create a table from a DBF file and get the macro to keep adding
>to the table for a particular invoice number.
>Any advice greatly appreciated.
>Please reply to the NG.

>databases used:
>maillist.dbf  [customer with link to invoice by custno]
>invoice.dbf [terms/date/etc, with link to lineitem by invoice no.]
>lineitem.dbf

SELECT invoice
SET RELATION TO custno INTO maillist
SET RELATION TO inv_no INTO lineitem ADDITIVE
SET SKIP TO lineitem
REPORT NEXT 1 FORM <yourreport> TO PRINT

In the report form <yourreport> put a group on inv_no, put all invoice and
customer info into group header and/or footer, and put line item information
into detail line.

Or, if you only want this report for one invoice _always_, don't bother with
groups; put all invoice and customer information into page header/footer, line
item information into detail line.  Don't bother with relations, too; just set
up proper records in both maillist and invoice tables, then
SELECT lineitem
REPORT FOR lineitem.inv_no = invoice.inv_no FORM <yourreport> TO PRINT

(And this by no means is the whole list of available methods).

[ When replying, remove *'s from address ]
Alexandre Pechtchanski, Systems Manager, RUH, NY

REPORT FORM invoice TO PRINT FOR invoiceno=m.invoiceno

The above would only print records whose invoicno field matches the value
store in the memvar m.invoicno.

The report will run much faster if you have an index tag on the invoicno
field.

Quote:>Is there a way to program the FoxPro report form to grab all the
>entries in my lineitem database that match a particular invoice number
>and loop on that until it hits the next number?

>databases used:
>maillist.dbf  [customer with link to invoice by custno]
>invoice.dbf [terms/date/etc, with link to lineitem by invoice no.]
>lineitem.dbf

Notice how you show your structures above::

Quote:>maillist.dbf  [customer with link to invoice by custno]
>invoice.dbf [terms/date/etc, with link to lineitem by invoice no.]
>lineitem.dbf

Quote:>lineitem.dbf indexed on invoice no + line item no with link to invoice.dbf
>invoice.dbf with link to to customer
>maillist.dbf

Forget Foxpro for a second.  Think of what you are billing for.  You
are billing for the line item, or for several of them.  So in the
physical world a charge is for an item sold.  The invoice info, like
customer, is really a lookup value.  The real transactions are the
line items sold and their quantites and costs.

For Foxpro this means the LineItem.DBF is your "main" dbf for an
invoice form.  I usually do this when setting up this type of form.

I use LineItem order InvoiceLineItem, assuming you have a CDX on the
LineItem DBF order by Invoice No. and LineItem No.

I them create a report formatting the line item as a detail for this
report.  I get the line for this down, such as quantity, description,
unit cost, extended cost.  I format this line first.  Next I set a
grouping for the detail, ie the line item, at the LineItem.Invoice_No
field, assuming this is the Invoice No field in the Line Item data
base.  Set the grouping options so that every new group, ie every new
LineIem.Inv_No, causes a page eject.  And then I set my totals, ie
costs.  I will print the report at this point to see if my line items
are formatting correctly, grouped by Invoice No, and then totalled
correctly.

Now notice I have not used the Invoice Database at any point yet.
When I have a good form laid out for the Line Items, I then use the
Invoice DBF order by Invoice, and I set the relation of the Line Item
DBF into the  Invoice, like this (for speed I am not worrying about
correct typing, commands, punctuation,etc.)

use LineItem order InvNoLineItem

use Invoice order InvNo in 0

use MailList order Custno in 0

select Invoice
set RELATION to Invoice.CustNo into MailList

select LineItem
set RELATION to LineItem.InvNo into Invoice

report form INVOICEFORM for LineItem.InvNo = "what ever invoice number
you want" to printer

Now you can go back to the report with a modify report INVOICEFORM.
Set up some header lines ahead of the detail and group lines.  Put
your Invoice file data here, such as Cust Name, Address etc.  Be sure
to put this in a header line in the report write so the Name etc will
repeat for multiple pages.  If you put the Invoice level data in the
invoice group lines, the fields will only print at the beginning of
every new invoice, not every page.

That should get you started.  If you have anymore questions, just
email me.

Good Luck,
Al Frick