1 Reply Latest reply on Sep 22, 2011 8:52 AM by philmodjunk

    Working with a tall hierarchy and fiscal years



      Working with a tall hierarchy and fiscal years


      I have a situation with a hierarchy like this:


      Each umbrella has many orgs.
      Each org has many clients.
      Each client has many projects.

      So, starting from the bottom, I just link each project to a clientID, each client to an orgID, each org to an umbrellaID, and all the umbrellas to a reports table. A project only has a clientID match, and can view the related info all the way up to its umbrella. I want to keep it this way--projects don't have match fields for orgs or umbrellas--because that info shouldn't be input on the project level.

      Is this bad practice?

      Now: is it possible to make calculations and view portals based on a the year the project existed in?

      For example, the ORG layout has a portal to its related PROJECTS. But a year from now, I will want to view just that year's projects. Also, there are calculations that total up revenue for that level in the hierarchy (total billable for this ORG).

      The problem is that a project doesn't have an org field or umbrella field because a client may be part of the org "Unknown" when the project is made. When the client is assigned to his correct org, I want that to automatically reflect in the project. So without those fields to match project table occurrances to the upper tables, I think i'm boned.

      Am I boned?

      How is this usually solved? This would be easy if the data was always input from top to bottom (umbrella to project), but that isn't the case.


      Any advice or slaps on the back of the head are appreciated! We're sticking with FMP 10, so no portal filtering.

        • 1. Re: Working with a tall hierarchy and fiscal years

          Is this bad practice?

          This is set up exactly how I would do it. Looks like basic relational database design at work...

          You are not "boned" here. Just keep in mind that when you work with relationships in FileMaker you are not limited to the table occurences that are directly connected to the current layout's table occurrence.

          You appear to have this structure to your relationships:


          From Org, you can place a portal to Project and you will see a combined list of projects for that Org record. In FileMaker 11, you can set a filter expression on this portal to only list Projects for a specified year. (The specified year can be a value in a field where you select the year.). This can also be accomplished in older versions, but the relationships needed are more complex. A Summary field in Project that computes total values can be placed in such a filtered portal and will will report the total of those records listed in the portal. Likewise, a Sum or other aggregate function defined in Org can compute aggregate values for all project records linked via records in client to a given Org record.

          For data entry operations, this shouldn't be a major problem. When you assign a new client to an Org, their projects will automatically be included in that Org record's portal.

          The nice part about your setup here is that there are a lot of options for summary reports that will work very smoothly for you here. You'd base such reports on the Project table, but can include all related data as needed from the other three tables, using sub summary and other layout parts as needed. Such reports can be limited to just the projects for a specified Client, Org or Umbrella if you perform a find that limits the Project records to that specification.