Reporting shortfall in FM?

[ABC]

I think I may have found something Filemaker just cannot do. Perhaps I need to change the way I'm approaching this to get what I want.

Have two tables: Invoices and InvoiceLines. InvoiceLines contains individual records for each line item in an invoice. It is related to Invoices by a unique InvoiceID field. Invoices contains general information about the sale: date, time, cashier, and payment method. InvoiceLines are entered through a portal in the Invoices layout.

Want a report that shows end-of-day subtotals from InvoiceLines by: ItemCategory and ItemName. For example: subtotal of cars, then subtotals of each car model; subtotal of trucks, then subtotals of each truck model. Then at the bottom, I want to display subtotals from Invoices::PaymentMethod to show the total from checks, cash, visa, etc. independently of each InvoiceLines record.

Is this possible? Do I need to restructure my database to make this work instead?

[RalphL]

The first part can be done using a list/report layout. You will need sub-summary parts to separate the cars & trucks and to separate the models. You will need summary to calculate the totals. If you don't want details remove the body from the layout. Before printing the report you will have to find records for the date and sort them.

For the second part you will need a relationship for each posible payment method. These relationships will need to include the date. Use the Sum function with the relationship.