Help with the structure of my table to track sales figures
<!-- StartFragment -->
I’d really appreciate it if anyone can help me with thestructure of my database. I’vebeen trying a new structure and working on it for days now and just can’t seemto sort it out. I’m new toFilemaker, but rapidly learning. I’musing Filemaker pro 10 Advanced.
I’ll explain in detail, and thanks to anyone who is able tobear with me and provide some help!
I have 20 companies that sell my products (very occasionallythere may be more companies added).
I need to be able to track all their contact details ofcourse, and also what products of mine they stock.
I have set up a General info table, with a serial number fora primary key for each retailer. This contains contact and other relevant info. (I also have another more detailed contacts table which isrelated to the General info table, and this contains the contact details of themultiple reps that are attached to each company.)
I also have an Inventory table, which contains our products(also with their own primary key each). These are related tables, and this works at the moment in showing mewhat each company are selling.
This is all working well so far.
What I need to be able to do:
The big thing that I need tobe able to do is to track each company’s sales figures on a weekly basis.
So far I have had a related “sales” table, with each recordbeing an individual company (related to the company in the General info table),and then each field would be one week. The problem with this is that I will have to continually create newfields for each week, and this isn’t very practical.
So it’s been suggested to me that I create a new table for eachindividual company, and have each record represent one week. Then I can have a sales field with theactual sales figures, and then I would also want a summary field with a runningtotal of the sales figures, and I’d also want fields with various calculationsbased on those figures.
I need somewhere (in a separate table?) a place where thefigures for each company is added up weekly to create a “Total Sales Figures”field. Then I can have a runningtotal of that, and I would also use this data as the basis for more calculationfields within the individual company tables (such as % of total sales etc…)
I want to be able toview:
- sales figures for a particular company in a particularquarter
(Currently I’ve tried creating arelated table with a key for the different quarters, and when I sort therecords in the sales figures tables by these keys, it returns the subsummaryresult. Not sure if this is theright approach. But I’m havingtrouble getting this to work in any layout other than the table layout, whichhas limited layout editing features.)
- sales figures for ALL companies in a particular quarter
- total sales figures for a particular quarter
Does anyone have a suggestion for a good way to structureboth the sales figures tables and relationships, as well as the layouts to use to be able toview the sales figures appropriately?
I'm trying, but with my limited experience just can't get it right yet.
<!-- EndFragment -->