6 Replies Latest reply on Aug 4, 2011 9:45 AM by philmodjunk

    Summing problem with a complex relationship

    JasonRossitto

      Title

      Summing problem with a complex relationship

      Post

      I've got a space database and I'm trying to sum floor areas for each of five use types in each building in the database. The types are Administrative, clinical, research, etc. I want a portal with an entry for each building and a field that shows the total area in that building for each type.

      Right now I'm only trying to total the Administrative spaces just to get it working. Here is how it's set up:

      There are three tables. Buildings, Spaces, and Space_Class

      Buildings has four fields:

      • Name (unique, text)
      • Area (calc) = Sum(Spaces::Net_Area)  <-- WORKING
      • Admin_Use_Type (global calc) = "Administrative"
      • Admin_Area (calc) = Sum(Admin_Spaces::Net_Area)  <-- NOT WORKING

      Spaces has a record for every room. There are two instances, Spaces and Admin_Spaces. The relevant fields are:

      • Building (text) - name of a building in the above table
      • Use_Code (text) - a code for the use, used to look up the use type
      • Net_Area (number) - area of the room
      • Use_Type (calc) = Lookup(Space_Class::UseType;"")

      Space_Class has the type for each use code. There are many use codes, and five possible types

      • Use_Code (unique, text)
      • Use_Type (text)

      I attached the relationship graph which will hopefully clarify things a little. Spaces and Admin_Spaces are the same table. Space_Class is there so that Admin_Spaces can look up its Use_Type.

      The reasoning here is I want Buildings::Area to calculate the total area for the building, and Buildings::Admin_Area to have the total of only the spaces with Administrative use types. So the total area is calculated from Spaces, and the Admin area is calculated from Admin_Spaces, which has the extra condition of containing only records where Use_Type is "Administrative."

      Here's the problem. In the portal for Buildings I have fields for Building name, total area, and admin area. The building and total area show up correctly, but the admin area is blank for every building.

      (Sorry the formatting seems wierd, it looks fine in the editor then it seems to strip the <li> tags out when it posts)

      relationship-graph.PNG

        • 1. Re: Summing problem with a complex relationship
          philmodjunk

          I'd use a summary report created on a layout based on the spaces table with fields drawn from Buildings and Space_Class as needed to complete the report. That can produce a report looking like this:

          Building: Main Street Pavilion
          Administrative: 250 sq feet
          Next space type: 300 sq feet
          Next space type: 400 sq feet

          You can use different find requests to pull up this report for just one building or more than one, for all the use types or just specified use types.

          Does this do what you need or do you have something else in mind?

          • 2. Re: Summing problem with a complex relationship
            JasonRossitto

            My goal is to create a dashboard with different occupancy statistics for our space planners. Eventually I want to have a small pie chart next to each building so that the use types and efficiency of each building can be compaired at a glance. I'm having difficulty doing it with portals though, maybe it needs to be a series of reports.

            I haven't dug into reports yet, but that is exactly the way I want the data to look. I've been able to learn how to do relationships, portals, scripting, calculation fields, value lists, etc pretty quickly (I touched FMP for the first time about four weeks ago), which I think is a testament to the design of Filemaker. I've even done some dynamically filtered portals and used scripting and tab controls to dynamically show/hide parts of the layout. I've been putting off learning reports to focus on the core elements, but it seems like now is the time.

            • 3. Re: Summing problem with a complex relationship
              philmodjunk

              The report I describe won't fit on your dashboard, but as a separate document, you define a summary field to compute the total of your areas and place the space class field and this summary field inside a sub summary part "When sorted by Area Type". Then you remove the body layout part and put the same summary field in a footer or trailing grand summary if you want to see the grand total area and then you must always sort the records by area type when viewing/printing this report in order to get the sub summary part with it's sub totals to appear.

              From a portal on your dashboard will take a different approach here. Is your dashboard layout based on Space_Database? And do you use filtered portals on this layout to display the relevant data? That's what your relationship graph would imply to me anyway.

              You'll need a different structure to get your sub-totals by space class. You need a table where, for each building you have one record for each space class used in it.

              Building::BuildingID = Space_Areas::BuildingID

              Space_Class::Use_Code = Space_Areas::Use_Code

              Spaces::BuildingID = Space_Areas::BuildingID AND
              Spaces::Use_Code = Space_Areas::Use_Code

              To sum up the areas in Spaces, you can define either a summary field in the spaces table or a calculation field in Space_Areas defined as: Sum ( Spaces::Area )

              Either way you can add a portal to Space_Areas on your dashboard (Filter it by building if you are using filtered portals here) and put either the calculation or summary field into this portal to show the areas for each space class. A Pie Chart can be based on this portal once you can see that you are getting the correct totals for each space class to appear in this portal.

              Note: This requires creating records in the new Space_Areas table for each building and for each space class used in that building. You'll need to figure out whether it works better to manually add these records or to use a script to create them from the individual spaces records.

              Note also that I am using BuildingID instead of the building's name in these relationships. Using the name can create problems for you should you need to change the name of the building--either because the client changed the building's name or on your part to correct a data entry error. Either way, changing the name in your current system can break the links to other related tables--something neatly avoided when you use an auto-entered serial number field defined in the buildings table in your relationships in place of this name field.

              • 4. Re: Summing problem with a complex relationship
                JasonRossitto

                Thanks! After watching some videos it looks like the report is pretty simple to create.

                I think I see what you mean with the portal method, but only if by Use_Code you mean Use_Type. I know this is probably confusing when you don't know the data set. I'll give it a shot.

                I know exactly what you mean re: buildingID and I agree. Unfortunately the Spaces and Space_Class tables are linked via ODBC to the SQL server that hosts our space database. I don't control how building is stored there so I'm stuck with it. In any case they are an institutional standard so they are unlikely to change. I suppose I could create a calculation field in the Spaces table to look up the buildingID from another table. That would limit the damage.

                • 5. Re: Summing problem with a complex relationship
                  JasonRossitto

                  I got the portal sums working, but it turns out you can't place a chart in a portal!

                  Looks like I'll have to create a separate list view layout.

                  Thanks for your help.

                  • 6. Re: Summing problem with a complex relationship
                    philmodjunk

                    You place the chart next to the portal instead of in it and then set it up to chart the related records in that portal.