12 Replies Latest reply on Oct 15, 2009 3:05 AM by comment_1

    Help with the structure of my table to track sales figures

    user14360

      Title

      Help with the structure of my table to track sales figures

      Post

      <!--  StartFragment  -->

      Hi,

      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!

       

      Basic info:

      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. 

       

      Thanks.

       

      <!--  EndFragment  -->

        • 1. Re: Help with the structure of my table to track sales figures
          comment_1
            

          You should have a table of Retailers (one record for each retailer) and a table of WeeklySales with fields for:

          • RetailerID
          • Week
          • Amount

          You can produce practically any type of report from the WeeklySales table, by finding the records you want to include and summarizing them by retailer and/or time period.

           

           

          BTW, I am not sure why your sales data isn't broken down by product - this would give you more control.



          • 2. Re: Help with the structure of my table to track sales figures
            user14360
              

            Thanks for your response. 

             

            There's really only one product we sell, just different types, and I only need sales for overall figures.

             

            What you described is what I had already tried.  But doesn't your suggestion mean that I would need many many fields for EACH week?  (Not just the one week.)  (This will be ongoing, tracking every week throughout the year and after.) 

             

            I think that's the setup I have now, and it's been suggested to me that it might be a better structure to have each week as each record, rather than each retailer.

             

             

            • 3. Re: Help with the structure of my table to track sales figures
              comment_1
                

              Hildy wrote:
              But doesn't your suggestion mean that I would need many many fields for EACH week?

              No - each record has only ONE amount, for ONE week, for ONE retailer. If you have 20 retailers, you will have 20 records for each week in the WeeklySales table.

              • 4. Re: Help with the structure of my table to track sales figures
                user14360
                  

                So does that mean in the WeeklySales tables, I would have 20 records for the sales figures for week 1 (1 for each company), and then another 20 records for the sales figures for week 2 (1 for each company), and so on?

                  

                • 5. Re: Help with the structure of my table to track sales figures
                  comment_1
                     Yes. That is the only way to report data aggregated by any criteria you choose: the records must be atomized.
                  • 6. Re: Help with the structure of my table to track sales figures
                    user14360
                      

                    Ok I've had a play, and I can see that this is going to be a better way to go.  Thank you.

                     

                    Another question - ideally, I'd like the "week" field in the WeeklySales table to be set up so that when someone enters a new record, it knows the date of the previous record for that Company, and automatically enters the date of the first day of the next week.

                     

                    So if Company1 has already got sales figures entered for Weeks 1, 2 and 3, which start on dates 1 Jan, 7th Jan and 15th Jan respectively, then the next time someone creates a new record for this company (most likely via a portal on the Main General Info view page), the week date field would be filled in with the 22nd of Jan.  Is this possible?  Bearing in mind that the person entering it won't necessarily be entering it during the week in question.  It could be before or after, so the calculation can't be based around the date created.  I tried some calculations stemming from a start date, and also from a number representing the week, but couldn't get anything to work.

                     

                    Thanks again if you can help me out. 

                    • 7. Re: Help with the structure of my table to track sales figures
                      comment_1
                        

                      Try defining a calculation field cMaxWeek in the Retailers* table (result is Date) =

                       

                      Max ( WeeklySales::Week )

                       

                       

                      Then set the Week field in WeeklySales to auto-enter a calculated value =

                       

                      Retailers::cMaxWeek + 7

                       

                       

                      ---

                      (*) I suggest naming tables by what they represent - you'll find it much easier to understand when you come back to it later.

                      • 8. Re: Help with the structure of my table to track sales figures
                        user14360
                          

                        Thanks heaps for your time.  I've got the tables working now, with the structure you suggested, and the portals within the Retailer table showing the sales for that particular retailer.  I ended up getting some calculations to work to put in the right date (but based on the person inputting the week of the year, so maybe not the best way, and certainly not as simple as what you suggested!).

                         

                        I'm now trying to work out how to filter the records in the portal so you just see one quarter for example.  (Each week of the year is assigned a quarter description, eg Q4_09 etc...).  So far I've got them to filter by quarter, but then it shows the sales figures for ALL the retailers for that quarter, not just the ones in the current record.

                         

                        Anyway, thanks again. 

                        • 9. Re: Help with the structure of my table to track sales figures
                          comment_1
                             Make the relationship on which the portal is based match BOTH RetailerID and the quarter.
                          • 10. Re: Help with the structure of my table to track sales figures
                            user14360
                              

                            Brilliant!  That is so cool.

                             

                            (I made it a checkbox field so you can select as many or as few of the quarters as you want.)

                             

                            Thank you!!! 

                            • 11. Re: Help with the structure of my table to track sales figures
                              user14360
                                

                              I've run into a problem with this portal filtering that I can't seem to solve.

                               

                              It works to select, for example, Quarter 1 and Quarter 2 in the "quarter filter" checkbox from the main Retailer table, and this will filter the portal records to just those records with Quarter 1 and Quarter 2 in them, but, if I leave those checkboxes selected (which is what would happen with people entering data), when you enter a new week into the portal, no matter what week it is, the "Quarter" field automatically gets filled with whatever Quarters are currently selected in the checkbox at the time of data entry.  I want the Quarter field in the portal records to still input the correct quarter, based on the year of the week that the user enters for that record.  (The "Quarter" field in the WeeklySales table is currently a calculation field, designed to input whatever quarter it is dependent on what week of the year the user inputs into the year field.)

                               

                              There is a global "quarter filter" field in the Retailer table, which draws from a value list based on the Quarter field in the WeeklySales table. 

                              The relationship is setup so that the Retailer ID key from the Retailer table equals the Retailer ID key from the WeeklySales table, and the global Quarter Filter equals the Quarter field from the WeeklySales table.

                               

                              Any ideas about how to solve this so I can still filter the portal records with the Quarter Filter checkbox, but so the correct quarter gets inputted into the Quarter field from the WeeklySales table when new records are entered (no matter what is selected in the Quarter Filter checkbox)? 

                               

                               

                              • 12. Re: Help with the structure of my table to track sales figures
                                comment_1
                                   Filtered relationships are not well suited to creating child records. You should have two relationships (using two occurrences of the WeeklySales table): one matching only on RetailerID, the other filtered by quarter. Only the first relationship should allow the creation of new records.