asked on

Complex Summary Calculation in Filemaker Pro 11 Advanced

I am trying to include a summary calculation for costs on a report by fiscal year and building for a construction project. The database stores the information in four tables: Fiscal Year, Buildings, Projects, and Tasks. The Fiscal Year table is linked to the Projects table by FY and includes calculated summaries for each project year. The Building table is linked to the Tasks table by building. The Tasks table is linked to the Projects table by project number.

My problem occurs due to the costs being separated into design costs and construction costs in the Projects table with each having a different fiscal year. Thus to summarize a single fiscal year by building requires first separately summarizing the design and construction costs by fiscal year and then totaling them. I can easily accomplish this in MS Access by creating intermediate queries but I don't know how to do it in Filemaker Pro which I only use infrequently.

North2Alaska

I think the issue may be how you are calculating the numbers. If I understand what you are saying, you have tasks that are rolled up to totals in buildings that rollup to projects. Now you want to link FY to projects. So, if projects span multiple years and you want the totals for a given year, you will need to push the FY down to the task level so that the rollup is by year as well. You may even need to create two TOs for the totals, one for a Total Project and one for Total Project by year.

kwheil

ASKER

The Fiscal Year for a Task is determined in the Projects table. However, each task may have a design phase and a construction phase which are assigned in the Projects table. So FY is detemined by the Projects table but there are two different years for each record. The building is determined by the Task table. The way I solved this in MS Access was to create two queries: one with FY and design cost by building and the other with FY and construction cost by building. I then combined these totals in a query by building and FY. I don't know how to perform a similar operation in Filemaker. I have tried adding a variety of different tables but nothing has worked.

North2Alaska

Create two Global calculation fields in the building table; one named g_design = "Design" and the other g_Construction = "Construction". Create two new TOs of Task; Task_Design and Task_Construction. These will be joined to the building table by FY and the new global. The results is what you would have done in Access. Now in building you can create two new calculations to capture the intermediate results and then roll them up to the project.

kwheil

ASKER

Thanks, I will try this when I get to work in the morning.

kwheil

ASKER

The Bulding table does not have a field for FY because a single building may have multiple projects that each have different Design and Construction FY/Costs. Since the Building table has a single record for each building there would need to be separate fields for each FY for design and construction. What I need in the end is a table/query that contain a record for each FY with fields for FY, Building, Design Cost, Construction Cost, and Total Cost. There is potential for 10 buildings and 10 FY.

If you have MS Access you can view the attached MS Access database I created for testing purposes by importing tables from the Filemaker database. The query "qryFYCostByBldg" shows the desired final totals I am trying to get in Filemaker.

Thanks for your assistance.
NYC.zip

North2Alaska

While I don't have Access (I use a Mac), if you would send me a sample of your FM database, I can help.

kwheil

ASKER

The database is actually comprised of 22 linked databases but the fields I am concerned with are only in the main one. It is also shared on a network. I will try to extract the pertinent parts to make it practical to send. The main file is over 16MB but I don't need all the data for this calculation.

Thansk again for your assistance.

kwheil

ASKER

The stripped down Filemaker Pro database is attached. It includes tables and relationships that do not work as intended. I removed password protection and all linked databases so hopefully it will be accessible.
FACILITIES.zip

ASKER CERTIFIED SOLUTION

North2Alaska

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwheil

ASKER

Thank you for your help. I do believe you are corret that the database structure does not support the desired result. Unfortunately, I did not create the database and do not have primary responsibility for it. I am just trying to assist the owners. I will pass on your suggestions.