3 Replies Latest reply on Aug 6, 2012 12:01 PM by philmodjunk

    How to calculate sum of a field with same value

    duhongfei626@gmail.com

      Summary

      How to calculate sum of a field with same value

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      Win7

      Description of the issue

      Hi
      The problem to be solved is like this, I have a table with:
      Table: Input
      SKU       Quantity
      A                 1
      B                 1
      A+B               1
      Table: Output
      A outcome table is to calculate all quantity with A or B like:
      SKU       Quantity
      A                 2
      B                 2

      Is there a simple function that can be written for Table Output's Quantity= ????
      Thanks

      Steps to reproduce the problem

      Set a table
      SKU       Quantity
      A           1
      B           1
      A+B         1

      Expected result

      another table resulting in:

      SKU       Quantity
      A           2
      B           2

      Actual result

      I tried a lot of ways but it only results in:

      SKU       Quantity
      A           1
      B           1

        • 1. Re: How to calculate sum of a field with same value
          philmodjunk

          This portion of the forum is intended for reporting possible bugs with the software. If you post questions of this type in the FM Pro Forum it be seen by more users than here.

          I don't follow how

          A 1
          B 1
          A+B 1

          becomes

          A 2
          B 2

          How did the 1's become 2's? What does A+B mean?

          I'd guess that you want the total count of all records where a field has a SKU of A and a count of all Records that have a SKU of B in the same field. If so, a Summary report can be constructed such that you get this result, but I am making a lot of guesses at to what you want.

          • 2. Re: How to calculate sum of a field with same value
            duhongfei626@gmail.com

            Hi PhiModJunk,thanks a lot for your reply. the key problem is I want Table 2 be able to recognize "A+B" as separate "A" "B" and calculate the quantity corresponds to A+B. Think Table 1 as a sales records, Lisa ordered item A, Linda ordered item B, and Clair ordered items A and items B:

            Table 1: Today's Sales Records

            Record#      Item       Quantity
            #0001              A                1
            #0002              B                1
            #0003              A+B            1
            #0004              C                2
            #0005              B+C            3

             

            Table 2 intends to calculate the sum of item A and item B totally ordered respectively.

            Table 2: Stock Dispatched

            Item       Quantity
            A                2           //to sum(1+1)
            B                5           //to sum(1+1+3)
            C                5           //to sum(2+3)

            I guess the key is how to solve the "+" as in fact a separate record, as some customer does buy multiple items in a single record, I hope this makes it better understood the issue. Cheers.

             

            • 3. Re: How to calculate sum of a field with same value
              philmodjunk

              ome customer does buy multiple items in a single record

              This is the issue creating the problem.

              Invoicing system generally have this set of tables and relatinships:

              Customers----<Invoices-----<LineItems>-------Products/Services

              A single invoices record will document a given sales transaction, but the individual items purchased are listed as individual records in the lineItems table. And you can then produce your report from the LineItems table where each item purchased is on a different record.

              See this simple demo file: http://fmforums.com/forum/showpost.php?post/309136/