5 Replies Latest reply on Jun 1, 2012 10:19 AM by philmodjunk

    How do you get a summary on related values

    donjuancarlos_1

      Title

      How do you get a summary on related values

      Post

      I have an inventory database with a related month-end count database.

      The count sheets use the inventory database as their layout database.

      If I change the layout to the month end count database, I can get a summary for the value of inventory.  However, the count sheet only show inventory items the have values in them and I want to show the complete inventory.

       

      Is there a way to show the summary of values in a related table?

        • 1. Re: How do you get a summary on related values
          philmodjunk

          I'm not sure if this is what you are asking for:

          If you have 4 records in a related table that link to the current record on your layout and you need to see a total, average, or other summary value computed from those 4 related records, you can either use an aggregate funciton such as: Sum ( RelatedTable::Field ) or you can define a summary field in the related table and put it on your layout or refer to it in a calculation.

          • 2. Re: How do you get a summary on related values
            donjuancarlos_1

            I did define the summary field in the related table, and it's blank. Same thing happens using an aggregate function.  I guess Filemaker doesn't know what it's supposed to do.

            • 3. Re: How do you get a summary on related values
              philmodjunk

              Filemaker works just fine. I use both of these methods frequently.

              The value returned is controlled by the relationship between the two tables. If there is no valid relationship, or the relationship does not match to any records, you'll get an empty field like you describe here.

              Feel free to describe the relationships that you have and how you need the values to be computed.

              • 4. Re: How do you get a summary on related values
                donjuancarlos_1

                Inventory DB                             MonthEnd Count DB

                Part# -----------------------------------Part#

                gMonth -------------------------------Month

                Cost                                           Count

                                                                 Extended (Cost * Count)

                                                                 Total (Summary field)

                 

                User select a month from a dropdown and the counts for that month are displayed on an Inventory count sheet. Records are created in the MonthEnd DB when a user enters a number in the count field.  Should I structure the db a different way?

                                                                  

                • 5. Re: How do you get a summary on related values
                  philmodjunk

                  Two quick tests you can do:

                  Go to a layout based on MonthEnd Count and put Total on that layout. Do you see a value in this field. (Don't care at this point what the value is as it will change with the foundset, but this will tell us that it is computing a value.

                  Put a portal to MonthEnd Count on your inventory layout and make sure that you actually get records appearing in the portal. If you don't, then either the value in gmonth isn't matching to a value in Month or a value in Part# isn't matching to Part# in the other field.

                  Example, if you have "January" in gMonth and Month records a number 1, 2 ,3 ... 12 for the month, you won't see any records match and your portal will be empty.

                  If you put MonthEnd Count::Total on your Inventory layout, you should see the total of all records shown in this portal.

                  A few questions about your design.

                  The relationship matches by part# and month as I understand your last post. Does this mean that you have several counts for the same Part # for the same month? (Perhaps one person counts all the items in location A and another counts all the items in location B....)

                  What keeps your relationship from matching to records for a given part # for a count taken on the same month of a different year?