2 Replies Latest reply on Jul 24, 2011 4:12 PM by EplotEplot

    Summing and Sorting

    EplotEplot

      Title

      Summing and Sorting

      Post

      Hi there,

      We have some data/fields in a table that we want to sort and display the total quantity based on Customer and Quantity, what is the best way to do it using filemaker to make a layout displaying the data?

      We have three fields to sort Customer, Product Code & Qty

      Customer Product Code Qty
      A Apple 2
      A Oranges 1
      A Oranges 3
      A Apple 5
      B Apple 2
      B Oranges 2
      B Apple 3

      We would like to have an end result like this:-

      Customer Product Code Total Qty
      A Apple 7
      A Oranges 4
      B Apple 5
      B Oranges 2

      ----------------------------------------------------------

      Thanks

        • 1. Re: Summing and Sorting
          philmodjunk

          Hmmm, tried to do this with the "re-order based on summary field" option, but couldn't get quite the order you show here... The following alternative did work, but required and extra calculation to use for sorting your records.

          1. Start with a new layout based on this table.
          2. Replace the body layout part with a sub summary part "When sorted by Product Code".
          3. Define a summary field that computes thte total of your quantity field if you have not already done so.
          4. Define a calculation field, cSubTotal, as GetSummary ( TotalQty ; ProductCode )
          5. Put the Customer, Product code and the Total Quantity summary field inside this sub summary part.
          6. When you return to browse mode, sort your records by Customer, Product Code, cSubTotal.
          • 2. Re: Summing and Sorting
            EplotEplot

            thanks will try that and let you know how it goes, might be useful for other users in the future.