Relations in Reports

[J__]

Hi,

I have an inoices summary report i'm trying to put together.

I'm very new to reports so put the kid gloves on!

I have an invoices table and a InvoiceLineItems table. (1-M) relation.

I'd like to order by date the invoices and underneath each invoice, display the lineItems which are associated with that invoice.

example:

Date: 11/01/2004

Invoice1_InvoiceNumber, Invoice1_Amount

InvoiceLI_Description Invoice LI_Amount

InvoiceLI_Description Invoice LI_Amount

Invoice2_InvoiceNumber, Invoice2_Amount

InvoiceLI_Description Invoice LI_Amount

InvoiceLI_Description Invoice LI_Amount

Date: 12/01/2004

Invoice1_InvoiceNumber, Invoice1_Amount

InvoiceLI_Description Invoice LI_Amount

InvoiceLI_Description Invoice LI_Amount

I don't understand how to get the line items to display in there.

I have other forms where I show the Invoice and the details in a Portal.

thanks for any help in advance,

Sincerely,

J__

[RalphL]

Make your report in the lineitems table. Sort by date and by invoice number. Make a columnar layout with grouped data. Your layout should have 2 sub-summaries. One by date, the other by invoice number.

Sub-Summary1 by date would show Date: 11/01/2004

Sub-Summart2 by Invoice number would show Invoice1_InvoiceNumber, Invoice1_Amount

Body whould show InvoiceLI_Description Invoice LI_Amount

InvoiceLI_Description Invoice LI_Amount

Then you will get another Sub-Summary 2 showing Invoice2_InvoiceNumber, Invoice2_Amount

and then more body

[J__]

You said:

Make your report in the lineitems table.

- Check.

Sort by date and by invoice number.

- The invoice number of the InvoiceLI or the invoice number of teh Invoices table?

Make a columnar layout with grouped data.

- ok.

Your layout should have 2 sub-summaries. One by date, the other by invoice number.

Sub-Summary1 by date would show Date: 11/01/2004

Sub-Summart2 by Invoice number would show Invoice1_InvoiceNumber, Invoice1_Amount

- again how am i using Invoice fields here? Or am I ? and how are they sorting the invoiceLI?

Or should there be an invoiceLI invoice number?

Body whould show InvoiceLI_Description Invoice LI_Amount

- check.

Then you will get another Sub-Summary 2 showing Invoice2_InvoiceNumber, Invoice2_Amount

and then more body

- are you saying this will repeat for all the invoices i have in my invoice table?

sorry, as i said i'm pretty new to reports. Please provide more specific details.

and I would appreciate this also for the other posting you responded to.

thanks,

sincerely,

J__

[RalphL]

I will answer your questions in the order you gaave them.

1. q. he invoice number of the InvoiceLI or the invoice number of teh Invoices table?

a. I would use the invoice number in the lineitems.

2. q. again how am i using Invoice fields here?

a. This data will be related data from Invoice.

3. q. Or am I ? a. Yes

4. q. and how are they sorting the invoiceLI?

a. You can sort on relaated fields.

5. q. Or should there be an invoiceLI invoice number?

a. I assume this is the foreign key in lineitems.

6. q. are you saying this will repeat for all the invoices i have in my invoice table?

a. Yes, for every line item record in the found set

Take a look a the Task Management template. The projects report layout is arranged somewhat like this. Look at the layout in the layout mode and also look at the part setup in the layout menu. This may help.