List of Invoices with embedded list of LineItems

[Mandu]

I'm trying to create a daily invoice report laid out like this:

Page Header

-- Invoice 1 customer information

---- Line Item 1

---- Line Item 2

---- Line Item 3

-- Invoice 2 customer information

---- Line Item 1

---- Line Item 2

-- Invoice 3 customer information

-- Invoice 4 customer information

---- Line Item 1

---- Line Item 2

Page Footer

I've tried the inside-out approach of using LineItems as the master file, sorting them by Invoice_ID, and displaying them on a layout as follows:

Header

Sub-summary by Invoice_ID (Leading) [contains Invoice cust. info]

Body [contains line item info]

After Viewing as List in Preview Mode, it looked pretty much like what I wanted.

Unfortunately, it requires that every Invoice contain at least one LineItem - which, it turns out, is not always the case (see Invoice 3 above). So this breaks my inside-out approach.

If someone could give me a clue about how to do this right, I think I could run with it from there. It can't be difficult, but I'm obviously way off track here.

Thanks for your consideration,

Chap

[-Queue-]

I think the easiest solution is to import both invoice and line item information into a third table. Create your report there. After it has been run, delete the imported items.

You could also loop through the invoices and create a dummy line item for each one that has no related records. Set the fields on your Body part to slide up and reduce enclosing part. Then run the report and delete the dummies.

[Mandu]

I think the easiest solution is to import both invoice and line item information into a third table.

Do you mean, importing the line items into the third table as repeating fields?

You could also loop through the invoices and create a dummy line item for each one that has no related records. Set the fields on your Body part to slide up and reduce enclosing part. Then run the report and delete the dummies.

Okay - I was hoping there was a simple solution, but this might be as good as it gets.

For simplicity I left out a detail - every invoice *does* have at least one line item, but I'm only supposed to list certain types of line items. I can't see how that changes anything, but I thought I should mention it.

Thanks for your suggestions.

[-Queue-]

Do you mean, importing the line items into the third table as repeating fields?

No, just into regular fields. There's no need for using repeating fields here.

Every invoice *does* have at least one line item, but I'm only supposed to list certain types of line items.

This should only effect how you test for related records in your loop. I would use a calculated field in your line items file such as Type = "criteria", substituting your criteria, of course. This will return a 1 for true and a zero for false. Then test

If [not Sum(LineItems::typecalc)]

{steps to create dummy line item with the correct type}

End If

in your loop from the Invoices file.

[Mandu]

No, just into regular fields. There's no need for using repeating fields here.

Then I'm confused - how else would I represent the one-to-many relationship of Invoices to LineItems in a single, third table?

Like so?

INV_ID, INVCustomer, INVOtherCustData, ITEMName, ITEMQty

So that there's one rec per line item, and the customer data gets duplicated and subsequently grouped by INV_ID somehow during reporting?

[-Queue-]

There will naturally be one record per line item since you're only importing one record per line item. Customer data should be pulled from the Customer file--a simple relationship based on Customer ID should suffice.

There will also be one record per invoice header, since you're importing these also. You can either create a calculation to flag which records are headers or set a flag after importing them. Then you sort by the Invoice ID, the calculation field descending (assuming the flag is set with a 1 for header records), and then Line Item ID. This will force all header records to appear before their 'related' line item records. Now just create a subsummary part by Invoice ID and put your header fields in it and your line item fields in the body.

[Walter B]

Pardon me, if this sound odd. But, if there is no line item for and invoice, then there really is no invoice. I'm saying this strictly from an accounting standpoint, but, do you submit zero detail invoices to your clients?

[-Queue-]

To clarify, Mandu wrote earlier

Every invoice *does* have at least one line item, but I'm only supposed to list certain types of line items.

[Mandu]

There will naturally be one record per line item [...] There will also be one record per invoice header [...] Now just create a subsummary part by Invoice ID and put your header fields in it and your line item fields in the body.

Okay - neat - I get it. This looks like a reasonable way to go about it.

Thanks for your patience!

Chap