4 Replies Latest reply on Sep 19, 2012 9:52 AM by philmodjunk

    Sub-Summary Calculations

    BrianSmith_1

      Title

      Sub-Summary Calculations

      Post

           Hello All-

           New to the forum and Filemaker Pro but I'm hoping you all can help me out. I'm wondering if I can calculate across sub-summary parts. Or if that is even what I should be doing in the first place. Here's what I'm trying to do:

           I have a table that is a full inventory of audio equipment - there is a record for every piece of equipment we own. I then have another table that is essentially a wish-list of what equipment is needed for a certain project. This table contains a field for quantity, such as "2 Meyer 700 Speakers". Now, in the Wish-List I want to distinguish which speakers are for what, so maybe I have 2 left and 2 right. I have figured out how to use Sub-Summaries to create an overview that only shows how many total (total of quantity when sorted by model number) of each item are needed. 

           Here's where I am stuck - I want to be able to generate a report/layout/whatever that will tell me how many of each item I need to buy or rent. Say I only have 3 Meyer 700 Speakers, I want it to calculate how many I want, minus how many I have, and give me a simple line-by-line listing of each, in this case telling me I need to rent 1 Meyer 700 Speaker. 

           I tried to get a calculation to do, basically, 'Total of Wish List Quantity when sorted by model number" minus " Count of model number in stock" to equal "quantity needed to rent". I'm having a problem using the sub-summary fields because it seems as if they are too variable and are using the values of full totals or full counts of model numbers, not the count as if were grouped. 

           Does this make sense? Sorry if this is long-winded. Any thoughts on how to do this more efficiently than I have been attempting? 

            

           Thanks very much,

           Brian

        • 1. Re: Sub-Summary Calculations
          philmodjunk

               Putting a summary field into a sub summary layout part produces a sub total in that sub summary part when the "sorted by" field specfied in the part setup is included in the sort order for your records.

               But if you refer to a summary field in a calculation, this happens at a lower level that does not take the design of your layout into account. (Nor should it as this calculation field might be displayed on any number of layouts and the sub summary layout part might not even be on the other layouts.) Instead, references to a summary field return the grand total instead of the sub total.

               But there's a function for handling this situation: GetSummary ( SummaryField ; BreakField )

               The "break" field is the "sorted by" field you specify for your sub summary part. If you use this funciton in your calculation, and if your records are sorted by the break field, the funciton will return the desired subtotal so that you can use it in your calculation.

          • 2. Re: Sub-Summary Calculations
            davidanders

                 People >--< Events     two tables that would be very hard to use to calculate certain things. [many to many relationship]

                 People >-- Attendance --< Events      a join table that makes the calculations much easier.   [many to one to many relationship]


            http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
            Home > Designing and creating databases > Creating a database > About planning a database
                 A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.

                 Follow these general steps to plan a database:
            <SNIP>

            http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
            Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

            http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
            Relational Database Design 101 (part 2 of 3)

            http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
            Relational Database Design 101 (part 3 of 3)

            The White Paper for FMP Novices is useful  - 
                 http://foundationdbs.com/downloads.html

            • 3. Re: Sub-Summary Calculations
              BrianSmith_1

                   Thanks PhilModJunk, that is fairly helpful. It at least got me going in the right direction. Here is where I find myself now: 

                   I am able to use GetSummary (ProjectEquipment ; Model#) so as to get a quantity of model that I need for a project. I'd like to then subtract how many I have in stock - GetSummary (Stock::StockEquipment ; Model#) to then result in a number of how many I need to rent. It seems like if I am doing this in the Project Table, then only the first function works and if I am in the Stock table then only the second. How do I perform this across multiple tables? They are related, in that Stock ---< Project. Do I have a many to many relationship, since a project contains many types of equipment and so does my stock? 

                   If I need to modify my relationships, I understand WHY just not HOW. Any thoughts are greatly appreciated.

                   Thanks,

                   Brian

              • 4. Re: Sub-Summary Calculations
                philmodjunk

                     Please describe your relationships between the two tables, (what are the match fields?) on which table your layout is based (see layout setup...) and how your system calculates the "in stock" value for a given item.

                     To me, this looks like you need another table so that you have exactly one record in Project for each project and a related table that lists the items required for that project.