2 Replies Latest reply on Oct 19, 2014 6:27 AM by philmodjunk

    How to calculate items that have same number as group

    Rockito

      Title

      How to calculate items that have same number as group

      Post

      Hi File Maker

      I have a table with the following details

      ID       Carton No       Of Cartons      Units         Style number        _fkShippingID

      1            1                          3                40            11111                      1

      2            2                          3                40            11112                      1

      3            3                          3                40            11113                      1

      4            1                          2                40            14455                      1

      5            2                          2                40            14467                      1

      6            1                          1                40            56678                      1

      7            1                          2                40            55555                      2

      8            2                          2                40            55555                      2

      9            1                          1                40            55555                      2

      10          1                          1                40            55555                      4

      11          1                          1                40            122443                     3

      12          1                          1                40            88700                       7

      now as you see some of styles are pack in one carton some of them have individual carton, it is very flexible packing

      so my question is how to calculate the total number of cartons and total of units in one carton, per shipping(shipping ID)

      Thanks for the help

        • 1. Re: How to calculate items that have same number as group
          Rockito

          I will try to explain more.

          the shipping ID means that it shipped in different date.

          sometimes same style can be split by different shipping

          also sometimes same style can be split to different cartons (with other styles together or to individual packing)

          I am not sure how to accomplish it.

          I was thinking that maybe i need to add a Group A, B.....Z.....ZA.......ZZ as unique symbol for each carton, than to count them by function, but i hope there is much easier way to do that, and not manual input it.

          • 2. Re: How to calculate items that have same number as group
            philmodjunk

            Set up a new layout based on this table.

            Remove the Body layout part.

            Add a sub summary layout part, "When sorted by Carton No" Put the fields shown in your example in the sub summary layout part except replace the Units field with a summary field that computes the total of Units. Sort your records by the Carton No field and you should get one row of data for each carton No value with the total units in that Carton included in that row.

            Counting the number of cartons per shipping ID will be tougher as you want the count of unique values in carton No for each Shipping ID.

            For that, you can:

            Some The Reciprocal: How to count the number of unique occurences in field.

            or Use Execute SQL: A new way to count unique values in FileMaker 12

            Or use a self join that matches records by Shipping ID to set up a conditional value list and then use ValueCount ( ValueListITems .... to count the number of unique values in the value list.