Help needed with using subsummary fields for further processing

[kirvis]

Hi all,

I am quite a novice when it comes to working with Filemaker 10, but I am in the process of learning quite a lot. There is however one problem I have not been able to solve up till now.

What I would like to do is use subsummary values from one table, and display that in a field in another table, accompanied by its break field.

What it is:

• three indicators: GHG output, energy use, and waste output
  
• one recurring entity: (substituted) end products
  
• two subsequent supply chain steps: components per end product, and processes per end product
  
• two connect tables where calculations take place (currently only in connect table components) and where end product is coupled to components or processes
  
• one output table for every indicator (3 in total), with supply chain steps separated in different columns
  
• one final output table with total outputs per end product
  

I have posted a few screenshots on Flickr, for clarification.

I also added the file as attachment to this post.

As you can see, I have created a central entity with end products.

End products are made of different components and each component has its specific outputs during production which are depicted in the components entity. This is the first step in the supply chain where output is generated.

During production there are different processes needed for every end product, with also specific outputs per indicator. This is the second step in the supply chain where output is generated.

I have defined two connect tables to prevent many-to-many relationships between end products and components, and between end products and processes. (One component can be used in more than one end product, and one end product can have more than one components..)

Calculation takes place in the connect table for components, where the number of components needed to make the end product is specified. It then calculated a total output for the number of identical components needed for one end product. Furthermore, I have specified summary fields in the connect table components, to be able to summarize the total outputs per end product. So far so good.

The problem is that I want to be able to create a bunch of overview tables, depicted in purple, where on a per end product basis, the summary fields from the connect tables is calculated automatically. So in essence, the in the green connect tables calculated sub summary values must be used by the purple output table, which then have to be able to use them further for calculations. (Adding up as you can see in the last overview picture I added).

To illustrate: subsummary values from both components and processes for GHG output need to be used in one overview table for GHG output. In this table, these two values will be added (depicted in the two lower tables for clarity). These added values will then be used in the final overview table where on a per end product basis, all output indicators are depicted.

I hope I have made myself a little clearer on the problem. In my view, the essence of the problem is that I am currently not able to use subsummary values for further processing.

Please help!

relational_database.fp7.zip

Edited November 18, 2009 by Guest
added FM file

[bcooney]

Hello again,

As I'm reviewing your file, I've noticed:

1. You've used auto-enter calcs rather than calc fields for GHG, etc. in the join table. Why?

2. I think this is a case of separating entities that actually could all be in one table. It made me think of Expenses and Income in a ledger. They are put into one table, but a "type" field is added to distinguish them.

And so, I think that you only need one join table and it would have the foreign keys for both componentID and processID. Then you can report off this join table, subsummary by End Product Name, Type (component or process), then the detail. Your summary fields would still work, and you'd have the Grand totals, in a trailing Grand Summary part, that you are looking to achieve. (no purple TOs needed).

[bcooney]

..and after more thinking, I believe that components and processes should be in the same table, with a type field.