12 Replies Latest reply on Apr 25, 2014 9:27 AM by philmodjunk

    Summarize Related Records in Calculation Field

    Atkins

      Title

      Summarize Related Records in Calculation Field

      Post

           Hello,

           Here's the setup:  I have a table full of equipment records and another table full of room records.  The equipment records are each assigned to a location which ties them to a particular room record.

           I'd like to have a field in my room record table that calculates the total cost of all the equipment assigned to that room.  This summary needs to update whenever equipment is reassigned or cost values change.  I'm not sure if this can be done purely using a calculation field?  I don't know how to "look up" multiple records from a related table and then summarize them using a single calculation.

           I believe the proper table relationships are already established, essentially a one to many relationship based on the Room ID, so that one room record can have many related equipment records.  Thanks for any input!

        • 1. Re: Summarize Related Records in Calculation Field
          philmodjunk

               There is a special group called aggregate functions that compute values based on all the related records.

               Sum ( RelatedChildTable::FIeld ) will compute the total of Field over the set of all related records when defined to evaluate from the context of the parent record.

               A summary field defined in the related table, but referenced from the parent record or layout will show the same total.

          • 2. Re: Summarize Related Records in Calculation Field
            Atkins

                 Well the Sum ( RelatedChildTable::Field ) function is exactly what I tried using but it's not working.  I'm guessing that means my relationship is incorrect somehow?  It's showing the same value across all room records instead of a unique value for each room record.  The value is also incorrect...it's higher than it should be, and I have no idea what data it's actually computing to get that total.

                 Do I need anything else in my table relationship other than Room ID x Room ID?  I am using a duplicate table because I already had a different relationship defined with the original table.  Would that have any effect?  On my layout I selected the duplicate table::field, so it should be grabbing data from the proper place, right?

                  

                 As for a summary field in the equipment table, I don't know how to specify that it only summarize equipment with a particular Room ID.  Essentially there would be one summary for every 15 records (that's about how many items are typically in a room).  If I was doing this as a one-time calculation through a script I could figure that out, but this is something I want to be available across all room records at all times.  Hence the desire to use a calculation field rather than a script to achieve this summary.

            • 3. Re: Summarize Related Records in Calculation Field
              Atkins

                   Quick Update -- It appears to be grouping by the Building but not the Room.  So all the rooms in a building wind up with the same equipment cost total.  The Room ID field is a calculation field that consists of ( Building Name & " " & Room Number ).  Any reason why it wouldn't be matching the entire field, just the first word?  I haven't come across this in any of my other relationships...

                   I tried changing my table relationship to Building = Building AND Room = Room but it give the same result.  All the rooms in a single building get the same equipment cost total calculation.

              • 4. Re: Summarize Related Records in Calculation Field
                Atkins

                     Another update --

                     First, the grouping by building that I wrote about previously occurs when I change the relationship to Room ID = Room ID.  The Room ID x Room ID still returns the same value for ALL room records.

                      

                     Also, I've been trying to find other ways to do this.  I came across this function:

                     

                GetSummary(Total Sales;Country) returns a summary of all records pertaining to the value in the Country field.

                     Wouldn't this be exactly what I'm trying to do?  The Total Sales would be my total equipment cost and the Country would be my Room ID.  I've tried implementing this but with no success.  Any thoughts on this approach?

                • 5. Re: Summarize Related Records in Calculation Field
                  philmodjunk

                       Yes, it would appear that you have relationship problems. Given what I see and don't see in your preceding posts, you may need to carefully document the relationship between Rooms and Equipment that you are using and explain how and why you are using that specific relationship. I'm guessing that you are using a filtered portal to see the records from Rooms and a sum calculation does not reference your portal filter, only the relationship, in order to compute an aggregate value.

                       Room ID x Room ID

                       Matches any record in one table to ALL records in the other. So sum would be expected to return the sum of all records in the table as that is what you specified it to do given that relationship.

                       But if you have this relationship:

                       Rooms::__pkRoomID = Equipment::_fkRoomID

                       then you can define a calculation field in Rooms to be:

                       Sum ( Equipment::AmountFieldHere )

                       and you should get the total of "AmountFieldHere" for all records in Equipment that have the same RoomID as the current record in Rooms.

                       A summary field defined in Equipment to compute the total of AmountFIeldHere can use the same exact relationship to show the same total if you set it up to be evaluated from the context of a record in Rooms. You can do that by adding the summary field from Equipment to a layout based on Rooms. A calculation field defined in Rooms that refers to the summary field from Equipment will also evaluate as the total of all related records.

                       But please keep in mind that these methods rely on the same relationship and produce the same total.

                       It's also possible, however, to put this summary field inside a one row filtered portal to Equipment and it will show a total that reflects any filtering specified for that portal. But this is a "display only" total, you cannot refer to this total in any kind of calculation or script.

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  • 6. Re: Summarize Related Records in Calculation Field
                    Atkins

                         Ok, so it turns out I was doing it right all along, it's just that the relationship defined in the original table was screwing up the relationship I was trying to define in this duplicate table.  I thought those could co-exist completely independently, but I guess not?  I went back and changed the original table to the Room ID = Room ID and it worked immediately.  Hopefully this doesn't mess up anything that I already had working in my file (which is now vast and extremely hard to track).

                         In any case, thanks for the input!

                    • 7. Re: Summarize Related Records in Calculation Field
                      philmodjunk
                           

                                I thought those could co-exist completely independently, but I guess not?

                           What do you mean by that?

                           If you need two different relationship between the same pair of tables, you need to duplicate at least one Tutorial: What are Table Occurrences? so that you can set up different groups of match fields for each relationship.

                      • 8. Re: Summarize Related Records in Calculation Field
                        Atkins

                             Yes, I was under the same impression that you are.  I thought that if I had a relationship defined between two tables and I wanted to create a different relationship between the same two tables that I would duplicate one of the tables and create a different relationship.  On my layout when I selected the source for the field I would use the duplicated table::field name to take advantage of the new relationship.

                             Well, in this problem that I posted about I tried every which way to get it to work using that duplicate table and it just never did work.  I then created a test file with the same setup but without a duplicate table and it worked, so I knew the problem was in the relationship.  I went back to my original file and changed the relationship of the primary table and boom, it worked.  So something was happening in that relationship that was preventing the duplicate table from working...that's the best I can figure.  It doesn't make sense, but the duplicate table's relationship was not independent of the primary table.

                             I'm sure there's some room for user error in my experience, but it's such a simple relationship, Room ID = Room ID, that I don't see how I could've messed it up.

                        • 9. Re: Summarize Related Records in Calculation Field
                          philmodjunk
                               

                                    Yes, I was under the same impression that you are.

                               Sorry, but that is not an "impression". It is how FileMaker relationships works. Multiple large complex databases that I have designed and which are in daily use would fail catastrophically if this were not the case. If you were getting different results it wasn't due to setting up your relationships in this fashion.

                               I suspect that your original attempt failed due to some other detail being wrong such as duplicating the wrong table occurrence, or specifying the wrong table occurrence in your calculation--either inside the sum function or in the "context" drop down found at the top of the specify calculation dialog.

                               This calculation also will not function correctly in an auto-enter calculation.

                               As a test, take your test file and add in a second relationship via an added table occurrence. I predict that it will not affect your calculation. If it does, I want to see it and you can upload such a file to a share site such as drop box and post the download link for it here in this thread.

                          • 10. Re: Summarize Related Records in Calculation Field
                            Atkins

                                 Before I do that, let me ask this:  how do you decide which table occurrence to duplicate?  I hadn't really thought this question over yet.  I have been duplicating the Rooms table in my file, but perhaps I should've duplicated the Equipment table?  What would the resulting differences be?

                                 And I didn't mean to cause a "flap" when I used the term "impression".  I realize that FileMaker is designed to work a certain way and will only work that way.  I was just trying to express that I understood how it should work, I just wasn't getting matching results.

                            • 11. Re: Summarize Related Records in Calculation Field
                              Atkins

                                   Apparently I was on to something with that last question.  In my test file:

                                   I duplicated my Room table and changed the primary instance to a relationship of Building = Building, as it was in my real file.  I created a new relationship with the duplicate table, Room ID = Room ID.  This "broke" the working calculation, just as experienced in my real file, where all of a sudden it was calculating by building instead of by each room.  It seems obvious when stated as such, with Building = Building on the primary table, but doesn't that mean the relationship of the 2nd occurrence is not completely independent of the primary relationship?

                                   To further test the functionality, I then duplicated my Equipment table, leaving the primary instance as Building = Building.  I created a new relationship to the original Room table of Room ID = Room ID.  I then went into my Room Equipment Cost calculation field in the Room Table and changed the source of that Sum ( Equipment::Cost ) function to the 2nd occurrence of the Equipment table and it worked!

                                   Essentially, I duplicated the wrong table originally.  I still don't 100% understand what's happening in the "background" that prevents it from working with the duplicated Room table, but it does make sense how I have it now.  I guess I'm still not totally comfortable with duplicate tables and relationships and the philosophy behind how to use them, but this is definitely a step in the right direction.

                                   Thanks for your help PhilModJunk!  You continue to be a reliable source of detailed information and have helped me tremendously in learning the more complex aspects of FileMaker.

                              • 12. Re: Summarize Related Records in Calculation Field
                                philmodjunk

                                     Think of if this way. If you have this relationship:

                                     Rooms-----<Equipment

                                     If you were to place an unfiltered portal to Equipment on the Rooms layout, A sum function, defined in Rooms and with "Rooms" selected in the context drop down list at the top of the specify calculation dialog, will compute a total based on all the records shown in the portal. But this assumes that "Rooms" and not some other table occurrence of Rooms was selected in Layout Setup | Show Records From for that layout. If you base the layout on a different table occurrence of Rooms, the records in your portal will change, but the total you defined for the calculation field will not. That's because your layout now "starts from" a different occurrence of Rooms while your calculation continues to evaluate from the starting point of Rooms because it's starting point is specified in that "context" drop down in specify calculation.

                                     For more on table occurrences and how they affect the function of your database, see: Tutorial: What are Table Occurrences?