Your proposed design is very typical, just the names are different. Line detail table is actually "Products" table and client is "Customers". Part 1 and part 2 are actually the Categories. So better you adopt the solution database "Invoices" which comes with every version of FMP. It is very identical to your design and well organized and programmed to learn.
Thank you. so i need to create 2 layouts. one for data entry and the other is for reporting? but the data should be specific to that customer, right?
Thank you. so i need to create 2 layouts. one for data entry and the other is for reporting?
Adding to akhlaq38's explanation, here is a "graphical" display of your tables and their relationships:
Customer --< Invoice --< LineItem >-- Product
Typically, an Invoice is
• managed: from an Invoice layout in Form view (selection of customer, a portal that displays the line items added)
• printed: from a LineItems layout in List view (including sub-summary part(s), a grand total etc.
As to fruit, meat etc.: each line item is based on a record from the Product table; and that is where you would add that as an attribute. Once you've done that, you can use that attribute in the LineItems table to base a sub-summary part on.
but the data should be specific to that customer, right?
I think the single most important report in any Order database is the invoice layout … and that is based (indirectly) on a specific customer anyway.
If beyond that you want to see overviews / statistics for a customer, use a layout that makes the most sense for the task at hand.
To see the totals ordered by a customer, you could simply use a related summary field from Invoice on a Customer layout. To break orders down by period, you'd create a report based on the invoice layout. To break orders by produce category, you'd base your report on the LineItems layout.