Your first model is definitely preferable. Avoid keeping 'same sort of things' at different places. This is potential source of future complications.
OK That's what I thought. Given that structure, since I'm used to generating queries, how do create a layout to compare month by month of the current year compared to the previous year?
You can grab records with dates of a specific month via a relationhip combining calculation fields with the pattern 2009-01
year(date)&"-"& right( "0" & month(date) ; 2 ).
To collect values of the month one year ago you modify this calculation
year(date)-1 & ....
If this relatationship tends to be too slow because the number of records is too big you have to create tables and fields to store the monthly values.
Thanks for taking the time to explain; as I mentioned I am more familiar with queries and event-driven programming and this is brand new to me.
If you don't mind, would you please share your thoughts on using a Numeric "Month" and "Year" field vs. a Date field?
In order to help me better understand your response, would you approach this through a portal to the productsales table or to create a special table to hold the variance data?
If the former, I understand how to constrain the detail table through setting a global variable in the master table. Would you create a calculated field that grabbed data from the year-1 record?
If the latter, it appears that the solution is to scan the product sale table, get a field value and then put that value into a special table designed to display the variance.
Since I have not written a script yet, can you please assist me with the get and put functions? Is there a good resource for learning functions and scripts?
> If you don't mind, would you please share your thoughts
> on using a Numeric "Month" and "Year" field vs. a Date field?
The calculation fields are used as break fields. This means that they allow a grouping of dates by month:
date1: 2009-01-01 -> calc field: 2009-01
date1: 2009-01-02 -> calc field: 2009-01
date1: 2009-01-03 -> calc field: 2009-01
date1: 2009-02-01 -> calc field: 2009-02 (!)
> In order to help me better understand your response, would you
> approach this through a portal to the productsales table or to
> create a special table to hold the variance data?
I thought of creating a list with subsummaries per month.
Lets do it together: Lesson 1 My first list
1. Create a new db with name myFirstList.fp7
2. Create a number field called 'amount'. Create a summary field 'SumOfAmount'.
3. Create a date field 'date'.
4. Create a calc field: see my calculation from above (the first one). Close all dialogs.
5. Fill the records with some data, different amounts and dates.
6. Change to List View (View -> ... ). List looks ugly because the body part is too large vertically.
7. Change to Layout Mode (Cmd/Apple-L) and reduce the height of body part by dragging the bottom line.
8. Layouts -> Part Setup
9. Add a subsummary when sorted by calc field from (4). Close dialog
10. Move summary field (2) into the new subsummary part.
11. Browse mode.
12. Sort (records ....) by calc field (4).
13. Cmd/Apple - U
What do you see???
Excellent! It had never occurred to me to use list view. I was hung up in form view. Thank you.
Now let's move on to lesson 2. Let's assume we add one field, product. Can you nest subsummary fields and view monthly totals with a nested subsummary of product? I tried and was unsuccessful.
Very good idea.
1. Add a field 'Product'.
2. Add in Layout Mode a subsummary part 'when sorted by Product'.
3. Place the product field in the new part.
4. Duplicate the summary field also into the new subsummary part.
5. For better debugging this choose a background color for both subsummary parts.
Click Label of part to activate ...
6. Sort by product OR by product AND month calc field
7. Preview Mode