Thank you for your post.
I can think of a couple of different ways to do this.
First, it sounds like a many-to-many relationship. That is, there can be several purchase orders per month and several sales per month, so the trick is to get them displaying together. Therefore, your conclusion of needing another table to link is correct. The linking to each of these tables would be by year and month. If you just did month, then how would you distinguish between January 2008, January 2009 and January 2010? This is a rhetorical question, and it may not apply, but thought I should explain it.
This third table needs a Year and Month fields. The Purchase and Sales tables need two calculation fields that extracts the Year and Month from the Date field. Then, link both of these fields in the third table to the Year and Month calculation fields in the Purchase Orders and Sales tables. Then, you can put a portal into Purchases on left side of your layout, and a portal into Sales on the right side.
In the third table, you can then create two calculation fields that use the Sum() function to add up the totals in the portals, and a Summary field to add up totals for each month.
This should point you in the right direction.
If you need clarification for any of the above steps, please let me know.
Hi and thanks. Why does it make so much more sense when someone just writes it out!
I'll have a play and update tomorrow.
Just a tip to share, for all of my financial data, I create one of two extra fields (sometimes both).
I do this especially on sales numbers for running sub-summaries by these two fields. Even if I don't need to run reports like this initially, I know I will when i have more data available. It also addresses year over year and month over month data.
I actually picked up another method which is even more flexIble (thanks to Comment). Firstly, I wouldn't use week of year because it varies from year to year but let's move to the calculation for grouping by month/year:
yourDate - Day ( yourDate ) + 1 (calculation result of date )
... this will turn all your dates into the first day of the month. Why is this powerful? Because, by keeping the calculation as a true date field, you can utilise the power of FileMaker dates and then 1) you can use pop-ups for selecting the month/year for display in a portal and grap 'month-chunks' of data as you wish, 2) You can group your reports by this date and they will automatically sort true by year and then month, 3) you can use that same field and place it on a layout and (from field date formatting), select to display as September, 2007 or 9-2007 or anything you like.
Once you've created this generic 'first day of each month' you will find you use it all the time and you will never go again without it. Guaranteed.
I remember reading that new power in date fields. Your enhancement is great.
Regarding weeks, I usually designate the start of the week. Days may move from one week to another, but when comparing sales it works correctly.