I have a straight forward Order Entry application. I want to summarize products sold by Code Size and Type, Customer data is entered into a Invoice header with child Line Items. I established a relationship to the global table called Dash with the Products table. I can summarize the product Code, Size and Type (in the product table) in 3 different portals, by quantity and amount (in Line Items) just fine . The number of records in the Code, Size and Type table will vary.

But I cannot figure out how to filter the data for a preselected yearly interval, the selected year field is a global field in the Dash global table, but I can't figure out how to get to the Invoice header to set up the relationship for the date interval, do I store the invoice date in the order lines or create separate tables to sum the data?

     Dash----<Products---<Lines>----Invoice

I tried going the opposite direction but then couldn't sum the product info. Not sure which way is correct.

     Dash----<Invoice---<Lines>----Products

I think you want to use Both of your orders  Under Dash, You have two layouts, one based on products and one based on invoices.  The product Layout will show all product information and summaries.  The invoice layout does the same for invoices (e.g., number of parts sold for year).

Also, I'm wondering why year is a global variable.  Invoices have dates, and you'll want to filter by a date range (e.g., after july 4, 2012 and before July 4, 2013)

 

Hope this might help