Sounds like you need a third table. Most invoicing systems have this table structure:
Invoices(orders in your case)----<LineItems>----Products
With the above setup, you place a portal to LineItems on the invoices layout and each item purchased on that invoice is entered as a record in the LineItems portal. This structure will make a summary report that groups and gives sub totals for each product purchased over a specified date range fairly easy to set up as you can base this report on the LineItems table.
Here's a demo file created by Comment that illustrates this structure:
Don't know the purpose of your "titles" but it would seem those can be handled with this modification of the above structure:
where you would select a Title purchased in each row of the lineitem portal.
I do have the LineItems type of join table -- I have orderID productID table, and a productID titleID table. So they are all related in one to many relationships.
So we have a product (PRODUCT) that has four dvds in it (TITLES).
I can look at an order, see all the products in that order, then see all the titles in those products. I have a layout setup for the orders table that shows a portal to the products, and another portal to the titles.
I am trying to see how many of a specific title was sold in 2011.
I appreciate your help!!
Now that we've established the tables and relationships, we can take a crack at getting your Title counts.
The order date should be in Orders and relationships do link Titles to orders. Looks like we need a count of the Order_Product records for products that contain a specified title and which were placed on an Order for a specific date or range of dates.
With FileMaker 11, you might be able to define a "count of" summary field in Order_Product, then display it on a Titles layout inside a one row filtered portal to Order_Product where your filter expression filters out all records that don't have an order date of the specified range of dates. You can place a field or two on your Titles layout where you can select a specific date or range of dates to control the filter expression.
I haven't tested that, but that's the first approach I'd try here.
Thanks for the help.
So I started down this path (thanks for opening my eyes to a different approach). I created a "count of" summary field in Order_Products table and created a new layout based on the Order_Product table. I added an order date in the header to select a date range, then a subsummary part with the count field in it when sorted by title name.
This is a step in the right direction, but for the mult-packs of DVDs it is only counting the first title in the list. So if MULTIPACK PRODUCT A contains TITLE 1, TITLE 2, TITLE 3, and TITLE 4, only TITLE 1 is being counted, not the other titles.
Does that make sense? It doesn't to me!
Hey phil, thanks for your help. I created the count of summary field in order_product, created a layout of titles, and put the summary count in a portal, filtered to only show Orders::OrderDate > "1/1/2011"
What's happening is I'm getting total counts for each title over all years, not just 2011.
I also have the order date as a field on the layout, and i can't do a find based on that either.
FileMaker does not recognize "1/1/2011" as a date.
Try Orders::OrderDate > Date ( 1 ; 1 ; 2011 )
You might also use a global number field so that you can select different dates to see totals for each year.
Year ( Orders::OrderDate ) = YourTable::GlobalYearField
With such an approach, you can use a script trigger (OnObjectExit) to run this script step: Refresh Window [Flush cached join results] to force the portal to update each time you select a different year in the global field.
Thank you! You've shown me the light.
I am, however, a little confused on the global variable and the script trigger part. I set up a gDate1 and gDate2 so I can create a range, but I'm having trouble getting those values into my calculation for the portal filter.
Orders::OrderDate ≥ Date ( gDate1 ) and Orders::OrderDate ≤ Date ( gDate2 )
doesn't work because the parameters of gDate are incorrect.
So I'm jsut trying to work through that now. But thanks so much for the help, Phil.
If gDate1 and gDate2 are of type date like they should be, just use the fields without the date function, you don't need them anyway.
Orders::OrderDate ≥ gDate1 and Orders::OrderDate ≤ gDate2