13 Replies Latest reply on Oct 14, 2009 12:55 PM by user14360

    calculation field



      calculation field


      Hi.  Is there a way you can have a table that references another table and adds up the values included in all the records in one particular field?


      For example, in a sales table, the first field is the company name, and the 2nd field is "week 1", and in that field goes the sales figures for each company.  (Each company is an individual record.)  What calculation do I need to do to add up all of the figures in that "week 1" field?




        • 1. Re: calculation field
             You can create a calculation using the Sum() function on the related field. However I notice that you have Week1. This is an alert that your structure is probably off.
          • 2. Re: calculation field

            Thanks for replying.  I'll give the Sum function a go.  


            Regarding the structure, I want to track the sales figures of 20 different companies on a weekly basis.  So I've got field one being the company name, with the 20 different records, and then each other field contains the commencing date of each week.  


            I've got various other tables linking into this company names field, such as contact details, invoices, purchase orders etc... 


            Does this sound right?




            • 3. Re: calculation field

              What will you do if "Acme construction" is sold to a new proprietor and they decide to change their name to "Pinnacle Builders"? Name changes happen all the time and if you base your relationships on a name field, you'll encounter trouble each time. Name changes, even of companies, are often not unique and this causes yet another problem. If you give each customer a unique number (auto-entered serial number), you'll avoid those issues.


              You should also take a look at placing your "commencing dates" in a related table instead of a series of fields all in one record.

              • 4. Re: calculation field

                Sorry, I should have been clearer.  I do have a unique serial ID for each company, and that is what all the related tables are based on.


                What is the benefit of having the commencing dates in another table?  


                I currently have about 4 tables that are the sales figures tables.  One has the actual sales figures for each week, one has the rolling sales for each week (week 1, then week 1 added with week 2, then week 1, 2 and 3 added together etc...), one has a year on year growth calculation figure (calculation based on sales data from a past date) and one will have forecasts/budgets.


                Does that seem ok?


                (I'm very new to filemaker - sorry, these issues must seem so obvious to you!  Thanks for your help.) 

                • 5. Re: calculation field

                  It's hard to tell. That's why I said "look into" not that this is a bad idea.:smileywink: I suspect that you don't need separate tables for each kind of sales figure totals.


                  You can probably compute the sales totals you need all within one table using summary fields and different sub-summary reports.

                  • 6. Re: calculation field

                    Ok - cool.  I'll have a look.


                    One more question.  Given that each field represents one week, and this is an ongoing thing, it means that there are potentially endless numbers of fields.  Is there any problem with that?  (I suppose I could break them down into different tables for different years, but that seems like it would complicate it even more. I'd prefer just to keep creating a field for each week within the same table.)


                    So is there any fundamental problem with eventually having hundreds of fields?



                    • 7. Re: calculation field
                         I would rather have one record for each company for each week instead of having 52 week fields.
                      • 8. Re: calculation field

                        That's exactly why I am suggesting a related table instead of fields. With your approach, you'll have all kinds of problems with reports and will need to continually add new fields as time progresses.


                        Instead, use a table where one related record corresponds to one such field for a given customer. THen, you can simply add more records as needed.

                        • 9. Re: calculation field

                          Ok thanks.  I'll mull over it.


                          I appreciate your time! 

                          • 10. Re: calculation field

                            Hi again,


                            I've been trying out your suggestion, and I think you're right, but I can't seem to implement it.  I've posted this message below on the general message page, but thought I'd pop it here for you just in case you didn't see the other message.  If you happen to have a suggestion, that'd be great.






                            I’d really appreciate it if anyone can help me with the structure of my database.  I’ve been trying a new structure and working on it for days now and just can’t seem to sort it out.  I’m new to Filemaker, but rapidly learning.  I’m using Filemaker pro 10 Advanced.

                            I’ll explain in detail, and thanks to anyone who is able to bear with me and provide some help!


                            Basic info:

                            I have 20 companies that sell my products (very occasionally there may be more companies added).

                            I need to be able to track all their contact details of course, and also what products of mine they stock.

                            I have set up a General info table, with a serial number for a primary key for each retailer.  This contains contact and other relevant info.  (I also have another more detailed contacts table which is related to the General info table, and this contains the contact details of the multiple 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 me what 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 to be 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 record being 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 new fields for each week, and this isn’t very practical.


                            So it’s been suggested to me that I create a new table for each individual company, and have each record represent one week.  Then I can have a sales field with the actual sales figures, and then I would also want a summary field with a running total of the sales figures, and I’d also want fields with various calculations based on those figures.


                            I need somewhere (in a separate table?) a place where the figures for each company is added up weekly to create a “Total Sales Figures” field.  Then I can have a running total of that, and I would also use this data as the basis for more calculation fields within the individual company tables (such as % of total sales etc…)


                            I want to be able to view:

                            - sales figures for a particular company in a particular quarter

                            (Currently I’ve tried creating a related table with a key for the different quarters, and when I sort the records in the sales figures tables by these keys, it returns the subsummary result.  Not sure if this is the right approach.  But I’m having trouble getting this to work in any layout other than the table layout, which has 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 structure both the sales figures tables and relationships, as well as the layouts to use to be able to view the sales figures appropriately?


                            I'm trying, but with my limited experience just can't get it right yet. 




                            • 11. Re: calculation field



                              I'm glad to see that Comment did his usual excellent job of helping you out in the new thread you started. If you'd like, you can thank him by opening that thread back up and clicking a Kudo button.


                              Just clarifying that I did not suggest putting each company in a separate table. I was suggesting that you put all the records for all the companies in a single table--just as Comment has had you do.

                              • 12. Re: calculation field

                                Ah - sorry for my misunderstanding!  Indeed, your and Comment's help has been brilliant.


                                Thanks again. 

                                • 13. Re: calculation field
                                     Oops.  Put the 'solved' thing next to the wrong entry in relation to this field!  It was a solution to my other problem.  :  )