Weekly / daily sales tables
Hi. Am having a crisis in confidence over this issue. (Filemaker Pro 10 Advanced, Mac OS X.)
A client wants to switch from only tracking their sales data for different retailers on a weekly basis (one figure per retailer per week), to being able to also do it on a daily basis, if the data is available.
I have two tables: Weekly Sales and Daily Sales. They are related by the Week of Quarter field (so in the Weekly sales table, one record is Week 1 of Q3, and in the daily sales table, all the records that are in week 1 of Q3 will be related.)
There is a field in the Weekly Sales table that allows you to manually input a weekly sales figure, but then there is a calculation field (and this is the one that everyone sees) that says "if there is something related in the daily sales table to this particular week, then use the sum of that data here in this field. Otherwise, use the data from the manual field in the Weekly Sales table."
This is working fine so far.
My crisis in confidence lies in the question of multiple records for sales of different products in the daily table. So for example, on the 26/12/09, perhaps $500 of Product 1 was sold and $700 of Product 2 were sold.
My instinct says they should be two separate records, each with the date 26/12/09, but the client is wondering why they shouldn't have just one record for 26/12/09 with multiple fields for each product.
I think they should be separate records to allow for new products, changes in products, probably better reporting etc...
But, I want to be able to drill down into the detail, from the topline Week figure, to the total Day sales figure and then into the details of exactly how much was sold of each product.
So my two main questions are:
1) Am I right in thinking each record in the Daily sales table should be for just 1 date and 1 product?
2) Do I actually need 3 tables? Weekly sales (with a field summing the daily sales), Daily Sales (with a field summing the product sales), and Product Sales?
Or, can I just have the weekly sales and then the Daily Sales, and have a self-join relationship in the Daily Sales that allows me to sum up the total of the product sales for that day? This is what I've done now, but am not sure how to view in a portal ONLY 1 instance of each daily date, so that I can click on that and bring up the details of the sales of each different product in a different table.
So I thought I'd post and see if I'm on the right track in my thinking. Basically, it's going to be a pain for the client (and will cost them some money too) to change over their sales reporting structure to what I'm suggesting, and before they go ahead and do that, I wanted to make sure I'm right in my thinking!
Sorry about the long message and thanks for any input.