4 Replies Latest reply on Aug 27, 2012 8:19 AM by DavidBradow

    Calculations from multiple fields in multiple tables

    DavidBradow

      Title

      Calculations from multiple fields in multiple tables

      Post

      Hello,

      I'm fairly new to Filemaker (using v. Pro 12), so please forgive my lack of knowledge and perhaps incorrect lingo.  I have been able to teach myself a great number of things about Filemaker and I've made a fairly decent database in which to conduct my business, but I still need some help.

      I have a database with multiple tables.  Tables are "Table", "Molding Entry", and "Stamping Entry".  The latter 2 tables are designed to input the completion of manufactured parts as they are completed. "Table" is the database that stores all of the data for each order.

      What I need help with is setting something up so that when I enter an "order number" and a qty into "Molding Entry" or "Stamping Entry", it will automatically find the same order number in "Table" and then subtract the qty entered from the "Qty" field listed in "Table", but then put the calculation into a new fixed field.  I already have the fields, "M Qty Comp" and "S Qty Comp" created.

      It's bad enough that I can't figure out how to do that part, but I need it to be mildly more complex.  Since not every order is manufactured in 1 day, I need "Table" to find multiple entries in either of the entry databases and continuously subtract until the final qty reaches "0".

      If anyone can help, that would be greatly appreciated.  If you need any further information to assist, I will be more than happy to provide it for you.

      Thank you,

      David

        • 1. Re: Calculations from multiple fields in multiple tables
          davidanders

          If you have not created Summary Layout Parts, I suggest starting there.

          http://www.filemaker.com/help/html/create_layout.9.14.html

          Home > Designing and creating databases > Creating and managing layouts and reports > Working with layout parts > About layout part types

          Summary layout parts
          To include summary data on a layout (for example, subtotals, grand totals, averages, counts, and so on), you place summary fields in summary layout parts. Summary parts include grand summary and subsummary parts.

           


          http://www.filemaker.com/help/html/create_db.8.16.html#1029235

          Home > Designing and creating databases > Creating a database > Defining database fields > Defining summary fields

          Defining summary fields
          Use summary fields to calculate values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May in a report.


          • 2. Re: Calculations from multiple fields in multiple tables
            DavidBradow

            Hi David,

             

            Thank you for your comments.  I have already setup Summary Layout Parts to summarize data for the individual reports that I am running for the "Molding Entry" and "Stamping Entry" tables.  From what little experience I have with these reports, I'm not sure they'd be useful for the function I'm attempting.  Allow me to present a more detailed scenario.

            Here is an example of how my data is set up in the tables.

            TABLE

            Date Order Number Qty M Qty Left S Qty Left
            08/20/2012 100050001 20,000    
            08/25/2012 100050002 100,000    

            MOLDING DATA

            Date Order Number Qty Comp
            08/21/2012 100050001 5,000
            08/21/2012 100050001 5,000
            08/21/2012 100050002 5,000

            As you can see in Molding Data, there are two entries for order number 100050001.  What I would like to find a way to do is to have the two numbers in "Qty Comp" for order number 100050001 added together, then subtracted from the "Qty" field in "Table", and then the resulting calculation inserted into the "M Qty Left" field next to the appropriate "Order Number."  I would be adding data for each order into "Molding Data" until the resulting calculation equals "0".

            I'm sure that the reporting functions including subtotals and grand totals would be effective when running a report, however, I will be doing a large amount of lookups in "Table" to check on the status of individual orders, so it would be beneficial to have this calculation done directly in the "Molding Data" table.

            Can the function I am searching for be done?  Or should I simply set up a new layout that will search for an order number and then set up a script to perform the calculation?  I assume that's a possibility??  If so, I may need some assistance with that.  I've set up a few scripts, but i

            Thank you again for all of your help.  Once I'm done with this urgent project, I'll be sure to take more time to review all of the appropriate training materials so that I don't have to ask what are probably simple, mindless questions like this. :)

             

            Thank you!

            • 3. Re: Calculations from multiple fields in multiple tables
              philmodjunk

              Assuming that you have this relationship:

                   Table::Order Number = Molding Data::OrderNumber

              Then

              Quantity left can be defined as:

              Qty - Sum ( Molding Data::Qty )

              or you can use

              Qty - Molding Data::sTotalQty

              where sTotalQty is a summary field defined in molding data.

              Note, it looks from here like you have one table for Molding Data ordered items and one for Stamping Entry ordered items. Reporting would be easier if you had a combined table for all order line items where each record has a fields for Order Number, Qty, ItemID,Description, UnitPrice and extended cost. (Imagine a report listing all ordered items for a specified dat range with subtotals for each item and type of item.)

              • 4. Re: Calculations from multiple fields in multiple tables
                DavidBradow

                Hi Everyone!

                 

                Thank you for your help!   It turns out, for THIS particular issue I'm having, setting up the proper relationship and using the "Qty - Sum ( Molding Data::Qty )" function works perfectly.

                As I do a little more programming, I'm sure I'll come up with something new that I need to work on.

                But, again, thank you both for your help!  It is truly appreciated!!!!

                 

                Regards,

                David Bradow